AutoQuery insight needed

Question

So, I'm working with ServiceStack and love what it offers. We've come to a point where I'm needing to implement a queryable data API... prior to my coming to this project, a half backed OData implementation was done. I'd rather not try and weed through that to make it work.

Which brings me to AutoQuery. I'd like to try it with our SQL Server database. I'm looking at the examples at http://docs.servicestack.net/autoquery-rdbms - but I cannot for the life of me get this to work. Is there something I'm missing here?

I'm using ORMLite to query SQL, and my integration tests I've written show it to be working as I would expect. I have registered the OrmLiteConnectionFactory in the container, as well as my repository which uses it by way of dependency injection.

Specific to code so far, I have a type, and a message that is based on QueryDb:

public class Detail
{
    public string Div { get; set; }
    public string Reg { get; set; }
}

[Route("/report/detail")]
public class DetailQuery : QueryDb<Detail>
{
    public string[] Div { get; set; }
    public string[] Reg { get; set; }
}

The message, DetailQuery, is used by my service:

public class ReportService : Service
{
    public object Get(DetailQuery dq)
    {
        // not sure what to put here?
    }
}

With all of that, I am able to see the AutoQuery service instance in the admin interface. When I play with the query interface, I hit my service endpoint, and I see the data I expect - filter values in the 'Div' and 'Reg' collections. What am I missing for this to 'just work' here? I have done plenty in ServiceStack accessing my repositories from the Service itself, but I'm trying to gain some insight into what AutoQuery brings to the table here. I have yet to see a 'straight forward' example of how this works... or am I looking for a pot of gold that just isn't there?

Answer 1

AutoQuery works with just the Request DTO i.e. it doesn't need any Service implementation, so your query:

[Route("/report/detail")]
public class DetailQuery : QueryDb<Detail>
{
    public string[] Div { get; set; }
    public string[] Reg { get; set; }
}

When called from /report/detail will query the Detail RDBMS Table. But your properties here either need to match a column on the Detail table (e.g. Div or Reg) in order to have an exact match (default), however exact matches aren't normally done with arrays they're done with scalar values like a string, e.g:

public string Div { get; set; }
public string Reg { get; set; }

If you're querying a collection you'd be instead making an IN Query where the values would contain list of values, in which case they're normally pluralized:

public string[] Divs { get; set; }
public string[] Regs { get; set; }

and can be called with:

/report/detail?Divs=A,B&Regs=C,D

Which will perform a query similar to:

SELECT * FROM Detail WHERE Div IN ('A','B') AND Rev IN ('C','D')

If that's not the behavior you want it needs to match an implicit convention, e.g:

public string[] DivBetween { get; set; }

Which will then query:

SELECT * FROM Detail WHERE Div BETWEEN 'A' AND 'B'

If you wanted to you could override the AutoQuery service with a custom implementation, e.g:

public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    //Override with custom implementation
    public object Any(DetailQuery query)
    {
        var q = AutoQuery.CreateQuery(query, base.Request);
        return AutoQuery.Execute(request, q);
    }
}

But you'd only need to do that when you want to customize the default behavior, e.g. add an extra filter to the populated SqlExpression.