Queryable APIs

AutoQuery RDBMS enables the rapid development of high-performance, fully-queryable typed RDBMS data-driven services with just a POCO Request DTO class definition that supports most major RDBMS.

AutoQuery Services are ServiceStack Services

An important point to highlight is that AutoQuery APIs are just normal ServiceStack APIs, which can be mapped to any user-defined route, is available in all registered formats and can be consumed from Service Clients.

Getting Started

All Templates are pre-configured with AutoQuery enabled in Configure.AutoQuery.cs:

services.AddPlugin(new AutoQueryFeature { MaxLimit = 100 });

The MaxLimit option ensures each query returns a maximum limit of 100 rows.

TIP

It's recommended MaxLimit is set based as not setting a MaxLimit will return all rows for a query

Which uses your App's registered RDBMS in Configure.Db.cs which by default uses Sqlite:

services.AddOrmLite(options => options.UseSqlite(connString));

Upgrading to Enterprise Database

To switch from SQLite to PostgreSQL/SQL Server/MySQL:

Install preferred RDBMS (ef-postgres, ef-mysql, ef-sqlserver), e.g:

npx add-in ef-postgres

Optionally install db-identity to use this RBMDS in background jobs and Request Logs:

npx add-in db-identity

Your first AutoQuery API

Now that everything's configured we can create our first AutoQuery API. To implement the ideal API for OData's movie ratings we just need to define it with this Request DTO:

[Route("/movies")]
public class FindMovies : QueryDb<Movie>
{
    public string[] Ratings { get; set; }
}

That's all the code needed! Which we can now call using the ideal route:

https://localhost:5001/movies?ratings=G,PG-13

Clients continue to benefit from a typed API

and because it's a just regular Request DTO, we also get an end-to-end typed API for free with:

// C# Example
var api = client.Api(new FindMovies { 
    Ratings = ["G","PG-13"]
})

C#/.NET APIs will be able to re-use the Server DTOs as-is, whilst every other supported language just needs to re-generate the DTOs with the npm script included in each template:

npm run dtos

Which in all React Templates, generates TypeScript DTOs to enable its end-to-end typed APIs:

// TypeScript Example
var api = await client.api(new FindMovies({ 
    ratings: ["G","PG-13"]
}))

Whilst this gives us the ideal API we want, the minimum code required is to declare an empty Request DTO with the table it should query:

public class FindMovies : QueryDb<Movie> {}

Which just like other Request DTO's in ServiceStack falls back to using ServiceStack's pre-defined routes

Implicit Request DTO Properties

This is possible as a hidden gem in AutoQuery's approach is that everything still works whether using explicit or no properties (implicit). This is possible as the JsonServiceClient generates the same HTTP Request that matches the implicit conventions whether the property exist on the Server Request DTO or not.

Explcit Request DTO Properties Recommended

In order for ServiceStack to generate typed Properties for your AutoQuery API in its TypeScript DTOs they would need to defined explitly on your Request DTO, this also gives AI Models necessary context for determining what properties are available for each .NET API.

Implicit Conventions

The built-in conventions allow using convention-based naming to query fields with expected behavior using self-describing properties. To explore this further lets look at what built-in conventions:

GreaterThanOrEqual = "{Field} >= {Value}";
GreaterThan =        "{Field} >  {Value}";
LessThan =           "{Field} <  {Value}";
LessThanOrEqual =    "{Field} <= {Value}";
NotEqual =           "{Field} <> {Value}";
IsNull =             "{Field} IS NULL";
IsNotNull =          "{Field} IS NOT NULL";

ImplicitConventions = new()
{
    {"%Above%",         GreaterThan},
    {"Begin%",          GreaterThan},
    {"%Beyond%",        GreaterThan},
    {"%Over%",          GreaterThan},
    {"%OlderThan",      GreaterThan},
    {"%After%",         GreaterThan},
    {"OnOrAfter%",      GreaterThanOrEqual},
    {"%From%",          GreaterThanOrEqual},
    {"Since%",          GreaterThanOrEqual},
    {"Start%",          GreaterThanOrEqual},
    {"%Higher%",        GreaterThanOrEqual},
    {"Min%",            GreaterThanOrEqual},
    {"Minimum%",        GreaterThanOrEqual},
    {">%",              GreaterThanOrEqual},
    {"%>",              GreaterThan},
    {"%!",              NotEqual},
    {"<>%",             NotEqual},

    {"Behind%",         LessThan},
    {"%Below%",         LessThan},
    {"%Under%",         LessThan},
    {"%Lower%",         LessThan},
    {"%Before%",        LessThan},
    {"%YoungerThan",    LessThan},
    {"OnOrBefore%",     LessThanOrEqual},
    {"End%",            LessThanOrEqual},
    {"Stop%",           LessThanOrEqual},
    {"To%",             LessThanOrEqual},
    {"Until%",          LessThanOrEqual},
    {"Max%",            LessThanOrEqual},
    {"Maximum%",        LessThanOrEqual},
    {"%<",              LessThanOrEqual},
    {"<%",              LessThan},

    {"%GreaterThanOrEqualTo%", GreaterThanOrEqual},
    {"%GreaterThan%",          GreaterThan},
    {"%LessThan%",             LessThan},
    {"%LessThanOrEqualTo%",    LessThanOrEqual},
    {"%NotEqualTo",            NotEqual},

    {"Like%",           "UPPER({Field}) LIKE UPPER({Value})"},
    {"%In",             "{Field} IN ({Values})"},
    {"%Ids",            "{Field} IN ({Values})"},
    {"%Between%",       "{Field} BETWEEN {Value1} AND {Value2}"},

    {"%HasAll",         "{Value} & {Field} = {Value}"},
    {"%HasAny",         "{Value} & {Field} > 0"},
            
    {"%IsNull",         IsNull},
    {"%IsNotNull",      IsNotNull},
};

EndsWithConventions = new()
{
    { "StartsWith", new QueryDbFieldAttribute { 
          Template= "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat= "{0}%" }},
    { "Contains", new QueryDbFieldAttribute { 
          Template= "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat= "%{0}%" }},
    { "EndsWith", new QueryDbFieldAttribute { 
          Template= "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat= "%{0}" }},
};

These built-in conventions can be extended (or replaced) with your own in the AutoQueryFeature plugin.

We'll run through a few examples to see how the implicit conventions work. The request below works as you would expect in returning all Rockstars older than 42 years of age.

/rockstars?AgeOlderThan=42

It works by matching on the rule:

{"%OlderThan", "{Field} > {Value}"},

Since AgeOlderThan does indeed ends with OlderThan.

The % wildcard is a placeholder for the field name which resolves to Age. Now that it has a match it creates a query with the defined {Field} > {Value} template to achieve the desired behavior.

If you instead wanted to use the inclusive >= operand, we can just use a rule with the >= template:

/rockstars?AgeGreaterThanOrEqualTo=42

Which matches the rule:

{"%GreaterThanOrEqualTo%", "{Field} >= {Value}"},

And when the wildcard is on both ends of the pattern:

{"%GreaterThan%", "{Field} > {Value}"},

So to can the field name, which matches both these rules:

/rockstars?AgeGreaterThan=42
/rockstars?GreaterThanAge=42

An alternative to using wordy suffixes are the built-in short-hand syntax:

Param	Behavior
`>Age`	`Age >= {Value}`
`Age>`	`Age > {Value}`
`<Age`	`Age < {Value}`
`Age<`	`Age <= {Value}`

Which uses the appropriate operand based on whether the < > operators come before or after the field name:

/rockstars?>Age=42
/rockstars?Age>=42
/rockstars?<Age=42
/rockstars?Age<=42

Although as these use illegal characters in C# property names, they can't be explicitly defined.

The use of {Values} or the Value{N} formats specifies the query should be treated as a collection, e.g:

{"%Ids",       "{Field} IN ({Values})"},
{"%In",        "{Field} IN ({Values})"},
{"%Between%",  "{Field} BETWEEN {Value1} AND {Value2}"},

Which allows multiple values to be specified on the QueryString:

/rockstars?Ids=1,2,3
/rockstars?FirstNamesIn=Jim,Kurt
/rockstars?FirstNameBetween=A,F

Advanced Conventions

More advanced conventions can be specified directly on the StartsWithConventions and EndsWithConventions dictionaries which allow customizations using the full [QueryDbField] attribute, e.g:

EndsWithConventions = new()
{
    { "StartsWith", new QueryDbFieldAttribute { 
          Template = "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat = "{0}%" }},
    { "Contains", new QueryDbFieldAttribute { 
          Template = "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat = "%{0}%" }},
    { "EndsWith", new QueryDbFieldAttribute { 
          Template = "UPPER({Field}) LIKE UPPER({Value})", 
          ValueFormat = "%{0}" }},
};

that can be called as normal:

/rockstars?FirstNameStartsWith=Jim
/rockstars?LastNameEndsWith=son
/rockstars?RockstarAlbumNameContains=e

This also shows that Implicit Conventions can also apply to joined table fields like RockstarAlbumNameContains using the fully qualified {Table}{Field} reference convention.

Advantages of well-defined Service Contracts

Whilst Implicit conventions can be called on an empty "contract-less" DTO, it's recommended to explicitly define them, e.g:

public class QueryRockstars : QueryDb<Rockstar>
{
    public int? AgeOlderThan { get; set; }
    public int? AgeGreaterThanOrEqualTo { get; set; }
    public int? AgeGreaterThan { get; set; }
    public int? GreaterThanAge { get; set; }
    public string FirstNameStartsWith { get; set; }
    public string LastNameEndsWith { get; set; }
    public string LastNameContains { get; set; }
    public string RockstarAlbumNameContains { get; set; }
    public int? RockstarIdAfter { get; set; }
    public int? RockstarIdOnOrAfter { get; set; }
}

Advantages of well-defined Service Contracts

The advantages of formalizing the conventions you end up using is that they can be consumed from any of ServiceStack's Typed Service Clients, e.g:

const api = await client.api(new QueryRockstars({ AgeOlderThan: 42 }))

Making your API self-describing and gives it access to all of ServiceStack's metadata features including:

When publishing your API, you can also assert that only explicit conventions are ever used by disabling untyped implicit conventions support with:

services.AddPlugin(new AutoQueryFeature { EnableUntypedQueries = false });

Custom AutoQuery Implementations

The behavior of AutoQuery APIs can also be completely customized by providing your own implementation, e.g:

// Overridden implementations of FindMovies and QueryRockstars AutoQuery APIs
public class MyQueryServices(IAutoQueryDb autoQuery) : Service
{
    // Sync
    public object Any(FindMovies query)
    {
        using var db = autoQuery.GetDb(query, base.Request);
        var q = autoQuery.CreateQuery(query, base.Request, db);
        return autoQuery.Execute(query, q, base.Request, db);
    }

    // Async
    public async Task<object> Any(QueryRockstars query)
    {
        using var db = autoQuery.GetDb(query, base.Request);
        var q = autoQuery.CreateQuery(query, base.Request, db);
        return await autoQuery.ExecuteAsync(query, q, base.Request, db);
    }
}

This is essentially what AutoQuery generates for each IQuery Request DTO unless a Service for the Request DTO already exists, in which case it uses the existing implementation.

We can inspect each line to understand how AutoQuery works:

Resolve the DB Connection for this AutoQuery request:

using var db = AutoQuery.GetDb(query, base.Request);

By default this follows the Multitenancy conventions for resolving a DB connection.

Creating a populated typed SqlExpression:

var q = AutoQuery.CreateQuery(query, base.Request, db);

Is an equivalent short-hand version for:

Dictionary<string,string> queryArgs = Request.GetRequestParams();
var q = AutoQuery.CreateQuery(dto, queryArgs, Request, db);

Which constructs an OrmLite SqlExpression from typed properties on the Request DTO as well as any untyped key/value pairs on the HTTP Requests QueryString or FormData.

At this point you can inspect the SqlExpression that AutoQuery has constructed or append any additional custom criteria to limit the scope of the request like limiting results to the authenticated user, e.g:

var userId = Request.GetRequiredUserId();
q.Ensure(x => x.CreatedBy == userId);

TIP

Use Ensure to force the query to always apply the specified condition irrespective of other OR conditions

After constructing the query from the Request all that's left is executing it:

return AutoQuery.Execute(dto, q, Request);

As the implementation is trivial we can show the implementation inline:

public QueryResponse<Into> Execute<Into>(IDbConnection db, ISqlExpression query)
{
    var q = (SqlExpression<From>)query;
    return new QueryResponse<Into> {
        Offset = q.Offset.GetValueOrDefault(0),
        Results = db.LoadSelect<Into, From>(q, include:q.OnlyFields),
    };
}

Basically just returning results in an QueryResponse<Into> Response DTO. Additional data can be included in the DTO, e.g. if Include=Total is specified on the Request DTO or IncludeTotal=true is configured on the AutoQueryFeature plugin, it will also include the total number of results available in the Total property.

QueryDb Base Class

AutoQuery uses clean Request and Response DTOs including QueryDb<T> which is just an abstract class implementing IQuery<T> and returning a typed QueryResponse<T>:

public abstract class QueryDb<T> : QueryDb, 
    IQuery<T>, IReturn<QueryResponse<T>> { }

public interface IQuery
{
    int? Skip { get; set; }           // How many results to skip
    int? Take { get; set; }           // How many results to return
    string OrderBy { get; set; }      // List of fields to sort by
    string OrderByDesc { get; set; }  // List of fields to sort by descending
    string Include { get; set; }      // Aggregate queries to include
    string Fields { get; set; }       // The fields to return
}

With the IQuery interface including shared features that all Queries support and acts as a interface marker instructing ServiceStack to create AutoQuery APIs for each Request DTO implementing it.

Returning Custom Results

To specify returning results in a custom Response DTO you can inherit from the QueryDb<From, Into> convenience base class:

public abstract class QueryDb<From, Into> 
    : QueryDb, IQuery<From, Into>, IReturn<QueryResponse<Into>> { }

As we can tell from the class definition this tells AutoQuery you want to query against From but return results into the specified Into type. This allows being able to return a curated set of columns, e.g:

public class QueryCustomRockstars : QueryDb<Rockstar, CustomRockstar> {}

public class CustomRockstar
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }
    public string RockstarAlbumName { get; set; }
}

In the example above we're returning only a subset of results. Unmatched properties like RockstarAlbumName are ignored enabling the re-use of custom DTO's for different queries.

AutoQuery also takes advantage of OrmLite's References Support which lets you return related child records that are annotated with [Reference] attribute, e.g:

public class QueryRockstars : QueryDb<Rockstar> {}

public class Rockstar
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int? Age { get; set; }

    [Reference]
    public List<RockstarAlbum> Albums { get; set; } 
}

Joining Tables

AutoQuery lets us take advantage of OrmLite's support for JOINs in typed SqlExpressions

We can tell AutoQuery to join on multiple tables using the IJoin<T1,T2> interface marker:

public class QueryRockstarAlbums 
  : QueryDb<Rockstar,CustomRockstar>, IJoin<Rockstar,RockstarAlbum>
{
    public int? Age { get; set; }
    public string RockstarAlbumName { get; set; }
}

The above example tells AutoQuery to query against an INNER JOIN of the Rockstar and RockstarAlbum tables using OrmLite's reference conventions that's implicit between both tables.

The Request DTO lets us query against fields across the joined tables where each field is matched with the first table containing the field. You can match against fields using the fully qualified {Table}{Field} convention, e.g. RockstarAlbumName queries against the RockstarAlbum.Name column.

This mapping of fields also applies to the Response DTO where now RockstarAlbumName from the above CustomRockstar type will be populated:

public class CustomRockstar
{
    ...
    public string RockstarAlbumName { get; set; }
}

Joining multiple tables

Use the appropriate Join<,> generic interface to specify JOINs across multiple tables. AutoQuery supports joining up to 5 tables with 1 Join<> interface, e.g:

IJoin<T1, T2, T3, T4, T5>

Or alternatively any number of tables can be joined together by annotating the Request DTO with multiple IJoin<> interfaces, e.g:

IJoin<T1,T2>,
IJoin<T2,T3>,
IJoin<T3,T4>,
//...

Support for LEFT JOINS

You can tell AutoQuery to use a LEFT JOIN by using the ILeftJoin<,> marker interface instead, e.g:

public class QueryRockstarAlbumsLeftJoin : QueryDb<Rockstar, CustomRockstar>, 
    ILeftJoin<Rockstar, RockstarAlbum>
{
    public int? Age { get; set; }
    public string AlbumName { get; set; }
}

Customizable Adhoc Queries

The queries up to this point showcase the default behavior of Request DTO fields performing an Exact Match using the = operand to match fields on queried tables. Advanced queries are available via configurable attributes illustrated in the example below which uses explicit templates to enable custom querying, e.g:

public class QueryRockstars : QueryDb<Rockstar>
{
    //Defaults to 'AND FirstName = {Value}'
    public string FirstName { get; set; }  

    //Collections defaults to 'FirstName IN ({Values})'
    public string[] FirstNames { get; set; } 

    [QueryDbField(Operand = ">=")]
    public int? Age { get; set; }

    [QueryDbField(Template = "UPPER({Field}) LIKE UPPER({Value})", 
                Field = "FirstName")]
    public string FirstNameCaseInsensitive { get; set; }

    [QueryDbField(Template="{Field} LIKE {Value}", 
                Field="FirstName", ValueFormat="{0}%")]
    public string FirstNameStartsWith { get; set; }

    [QueryDbField(Template="{Field} LIKE {Value}", 
                Field="LastName", ValueFormat="%{0}")]
    public string LastNameEndsWith { get; set; }

    [QueryDbField(Template = "{Field} BETWEEN {Value1} AND {Value2}", 
                Field="FirstName")]
    public string[] FirstNameBetween { get; set; }

    [QueryDbField(Term = QueryTerm.Or)]
    public string LastName { get; set; }
}

We'll go through each of the examples to give a better idea of the customizations available.

Queries on Collections

ServiceStack properties are case-insensitive and allows populating a collection with comma-delimited syntax, so this query:

/rockstars?firstNames=A,B,C

Will populate the string array:

public string[] FirstNames { get; set; }

That by default IEnumerable properties are queried using an SQL {Field} IN ({Values}) template which will convert the string array into a quoted and escaped comma-delimited list of values suitable for use in SQL that looks like:

"FirstName" IN ('A','B','C')

The {Field} property is substituted according to the OrmLite FieldDefinition it matches, which for a Joined table with an [Alias("FName")] attribute would substitute to a fully-qualified name, e.g: "Table"."FName"

AutoQuerying also supports pluralized versions by trimming the s off Field names as a fall-back which is how FirstNames was able to match the FirstName table property.

Customizable Operands

One of the easiest customization's available is changing the operand used in the query, e.g:

[QueryDbField(Operand = ">=")]
public int? Age { get; set; }

Will change how the Age is compared to:

"Age" >= {Value}

Customizable Templates

By providing a customized template even greater customization achieved and by sticking with TSQL compatible fragments will ensure it's supported by every RDBMS provider, e.g:

[QueryDbField(Template = "UPPER({Field}) LIKE UPPER({Value})",
            Field="FirstName")]
public string FirstNameCaseInsensitive { get; set; }

As noted above {Field} variable place holder is substituted with the qualified Table field whilst {Value} gets quoted and escaped to prevent SQL Injections.

The Field="FirstName" tells AutoQuery to ignore the property name and use the specified field name.

Formatting Values

You can use the ValueFormat modifier to insert custom characters within the quoted {Value} placeholder which we use to insert the % wildcard in with the quoted value enabling StartsWith and EndsWith queries, e.g:

[QueryDbField(Template="{Field} LIKE {Value}", 
            Field="FirstName", ValueFormat="{0}%")]
public string FirstNameStartsWith { get; set; }

[QueryDbField(Template="{Field} LIKE {Value}", 
            Field="LastName", ValueFormat="%{0}")]
public string LastNameEndsWith { get; set; }

Specifying Multi Airity Queries

An alternate way to format multiple values is to use Value{N} variable placeholders which allows supporting statements with multiple values like SQL's BETWEEN statement:

[QueryDbField(Template = "{Field} BETWEEN {Value1} AND {Value2}", 
            Field = "FirstName")]
public string[] FirstNameBetween { get; set; }

Changing Querying Behavior

By default queries act like a filter and every condition is combined with AND boolean term to further filter the result-set. This can be changed to use an OR at the field-level by specifying Term=QueryTerm.Or modifier, e.g:

[QueryDbField(Term=QueryTerm.Or)]
public string LastName { get; set; }

However as your API's should strive to retain the same behavior and call-semantics the recommendation is to instead change the behavior at the API-level so that each property maintains consistent behavior, e.g:

[QueryDb(QueryTerm.Or)]
public class QueryRockstars : QueryDb<Rockstar>
{
    public int[] Ids { get; set; }
    public List<int> Ages { get; set; }
    public List<string> FirstNames { get; set; }
}

In this example each property is inclusive where every value specified is added to the returned result-set.

Dynamic Attributes

Whilst .NET attributes are normally defined with the property they attribute, ServiceStack also allows attributes to be added dynamically allowing them to be defined elsewhere, detached from the DTO's, e.g:

typeof(QueryRockstars)
    .GetProperty("Age")
    .AddAttributes(new QueryDbFieldAttribute { Operand = ">=" });

Custom Fields

You can also customize which fields you want returned using the Fields property available on all AutoQuery Services, e.g:

?Fields=Id,Name,Description,JoinTableId

The Fields still need to be defined on the Response DTO as this feature doesn't change the Response DTO Schema, only which fields are populated. This does change the underlying RDBMS SELECT that's executed, also benefiting from reduced bandwidth between your RDBMS and App Server.

A useful JSON customization that you can add when specifying custom fields is ExcludeDefaultValues, e.g:

/query?Fields=Id,Name,Description,JoinTableId&jsconfig=ExcludeDefaultValues

Using TypeScript JsonServiceClient you can include additional queryString parameters that are not defined on Request DTOs using the 2nd args paramater. e.g:

const api = await client.api(request, { jsconfig: 'ExcludeDefaultValues' })

Wildcards

You can use wildcards to quickly reference all fields on a table using the table.* format, e.g:

?fields=id,departmentid,department,employee.*

Which is a shorthand that expands to manually listing each field in the Employee table, useful for queries which joins multiple tables, e.g:

[Route("/employees", "GET")]
public class QueryEmployees : QueryDb<Employee>,
    IJoin<Employee, EmployeeType>,
    IJoin<Employee, Department>,
    IJoin<Employee, Title> 
{
    //...
}

DISTINCT Custom Fields

To query only results with distinct fields you can prefix the custom fields list with DISTINCT , e.g

Using QueryString:

?Fields=DISTINCT City,Country

Using TypeScript JsonServiceClient:

const api = client.api(new QueryCustomers({ fields: "DISTINCT City,Country" }))

Paging and Ordering

Some functionality defined on the core IQuery interface and available to all queries is the ability to page and order results:

public interface IQuery
{
    int? Skip { get; set; }
    int? Take { get; set; }
    string OrderBy { get; set; }
    string OrderByDesc { get; set; }
}

This works as you would expect where you can modify the returned result set with:

/rockstars?skip=10&take=20&orderBy=Id

Or when accessing via a ServiceClient:

client.api(new QueryRockstars({ skip: 10, take: 20, orderBy: "Id" }))

When results are paged an implicit OrderBy is added using the PrimaryKey of the IQuery<Table> to ensure predictable ordering of results are returned. You can change the OrderBy used by specifying your own:

client.api(new QueryRockstars({ skip: 10, take: 20, orderByDesc: "Id" }))

Or remove the default behavior with:

services.AddPlugin(new AutoQueryFeature { 
    OrderByPrimaryKeyOnPagedQuery = false 
});

Multiple OrderBy's

AutoQuery also supports specifying multiple OrderBy's with a comma-delimited list of field names, e.g:

/rockstars?orderBy=Id,Age,FirstName

Same request via the JsonServiceClient:

client.api(new QueryRockstars({ orderBy: "id,age,firstName" }))

Sort specific fields in reverse order

When specifying multiple order by's you can sort specific fields in reverse order by prepending a - before the field name, e.g:

?orderBy=Id,-Age,FirstName
?orderByDesc=-Id,Age,-FirstName

OrderBy Random

OrderBy includes special support for returning results in Random order using Random, e.g:

/rockstars?OrderBy=Random

Using Service Client:

client.api(new QueryRockstars({ orderBy: "random" }))

Service Clients Support

One of the major benefits of using Typed DTO's to define your Service Contract is that it allows usage of ServiceStack's .NET Service Clients which enables an end-to-end API without code-gen.

With the richer semantics available in queries, we've been able to enhance the Service Clients new GetLazy() API that allows lazy streaming of responses to provide transparent paging of large result-sets, e.g:

var results = client.GetLazy(new QueryMovies { 
    Ratings = ["G","PG-13"]
}).ToList();

Since GetLazy returns a lazy IEnumerable<T> sequence it can also be used within LINQ expressions, e.g:

var top250 = client.GetLazy(new QueryMovies { 
        Ratings = ["G","PG-13"]
    })
    .Take(250)
    .ConvertTo(x => x.Title);

Mime Types and Content-Negotiation

Another benefit we get from AutoQuery Services being regular ServiceStack services is taking advantage of ServiceStack's built-in formats.

CSV Format

The CSV Format especially shines here given queries return a single tabular resultset making it perfect for CSV. In many ways CSV is one of the most interoperable Data Formats given most data import and manipulation programs including Databases and Spreadsheets have native support for CSV allowing for deep and seamless integration.

ServiceStack provides a number of ways to request your preferred content-type, the easiest of which is to just use the .{format} extension at the end of the /pathinfo e.g:

/rockstars.csv
/movies.csv?ratings=G,PG-13

CSV Format responses can use the same scoped custom responses as JSON to allow Typed Results to exclude default values columns when returning limited custom fields with ?fields:

Camel Humps Notation: ?jsconfig=edv
Full configuration: ?jsconfig=ExcludeDefaultValues

JSONL Format

whilst the JSON Lines Format is useful for returning results in a streamable JSON format:

/rockstars.jsonl
/movies.jsonl?ratings=G,PG-13

Named Connection

AutoQuery can also easily be configured to query any number of different databases registered in your AppHost.

In the example below we configure our main RDBMS to use SQL Server and register a Named Connection to point to a Reporting PostgreSQL RDBMS:

var dbFactory = new OrmLiteConnectionFactory(connString, SqlServer2012Dialect.Provider);
container.Register<IDbConnectionFactory>(dbFactory);

dbFactory.RegisterConnection("Reporting", pgConnString, PostgreSqlDialect.Provider);

Any normal AutoQuery Services like QueryOrders will use the default SQL Server connection whilst QuerySales will execute its query on the PostgreSQL Reporting Database instead:

public class QueryOrders : QueryDb<Order> {}

[ConnectionInfo(NamedConnection = "Reporting")]
public class QuerySales : QueryDb<Sales> {}

An alternative to specifying the [ConnectionInfo] Request Filter Attribute on the AutoQuery Request DTO, is to specify the named connection on the POCO Table instead, e.g:

[NamedConnection("Reporting")]
public class Sales { ... }

public class QuerySales : QueryDb<Sales> {}

Include Aggregates in AutoQuery

AutoQuery supports running additional Aggregate queries on the queried result-set. To include aggregates in your Query's response specify them in the Include property of your AutoQuery Request DTO, e.g:

var response = client.Get(new QueryRockstars { Include = "COUNT(*)" })

Or in the Include QueryString param if you're calling AutoQuery Services from a browser, e.g:

/rockstars?include=COUNT(*)

The result is then published in the QueryResponse<T>.Meta String Dictionary and is accessible with:

response.Meta["COUNT(*)"] //= 7

By default any of the functions in the SQL Aggregate whitelist can be referenced:

AVG, COUNT, FIRST, LAST, MAX, MIN, SUM

Which can be added to or removed from by modifying SqlAggregateFunctions collection, e.g, you can allow usage of a CustomAggregate SQL Function with:

services.AddPlugin(new AutoQueryFeature { 
    SqlAggregateFunctions = { "CustomAggregate" }
})

Aggregate Query Usage

The syntax for aggregate functions is modelled after their usage in SQL so they're instantly familiar. At its most basic usage you can just specify the name of the aggregate function which will use * as a default argument so you can also query COUNT(*) with:

?include=COUNT

It also supports SQL aliases:

COUNT(*) Total
COUNT(*) as Total

Which is used to change what key the result is saved into:

response.Meta["Total"]

Columns can be referenced by name:

COUNT(LivingStatus)

If an argument matches a column in the primary table the literal reference is used as-is, if it matches a column in a joined table it's replaced with its fully-qualified reference and when it doesn't match any column, Numbers are passed as-is otherwise its automatically escaped and quoted and passed in as a string literal.

The DISTINCT modifier can also be used, so a complex example looks like:

COUNT(DISTINCT LivingStatus) as UniqueStatus

Which saves the result of the above function in:

response.Meta["UniqueStatus"]

Any number of aggregate functions can be combined in a comma-delimited list:

Count(*) Total, Min(Age), AVG(Age) AverageAge

Which returns results in:

response.Meta["Total"]
response.Meta["Min(Age)"]
response.Meta["AverageAge"]

Include Total

The total records available for a query can be included in the Response by adding it on the QueryString, e.g:

/query?Include=Total

Or on the Request DTO:

var response = client.Get(new MyQuery { Include = "Total" });

Alternatively you can always have the Total returned in every request with:

services.AddPlugin(new AutoQueryFeature {
    IncludeTotal = true
})

Aggregate Query Performance

AutoQuery combines all other aggregate functions like Total and executes them in the same a single query for optimal performance.

Hybrid AutoQuery Services

AutoQuery Services can be easily enhanced by creating a custom Service implementation that modifies the SqlExpression Query that AutoQuery auto populates from the incoming request. In addition to using OrmLite's typed API to perform standard DB queries you can also take advantage of advanced RDBMS features with custom SQL fragments. As an example we'll look at the implementation of techstacks.io fundamental QueryPosts Service which powers every Post feed in TechStacks where its custom implementation inherits all queryable functionality of its QueryDb<Post> AutoQuery Service and adds high-level functionality for AnyTechnologyIds and Is custom high-level properties that's used to query multiple columns behind-the-scenes.

In addition to inheriting all default Querying functionality in a QueryDb<Post> AutoQuery Service, the custom implementation also:

Prevents returning any Deleted Posts
Prevents returning any posts with a closed status unless the query specifically targets a closed label or status
Avoids any table joins by using PostgreSQL advanced Array data type for querying post string labels or int technology ids
Uses AnyTechnologyIds to return any posts tagged with the specified technologies

[Route("/posts", "GET")]
public class QueryPosts : QueryDb<Post>
{
    // Handled by AutoQuery
    public int[] Ids { get; set; }
    public int? OrganizationId { get; set; }
    public int[] OrganizationIds { get; set; }
    public string[] Types { get; set; }

    // Handled by Custom Implementation
    public int[] AnyTechnologyIds { get; set; }
    public string[] Is { get; set; }
}

[CacheResponse(Duration = 600)]
public class PostPublicServices(IAutoQueryDb autoQuery) : Service
{
    public object Any(QueryPosts request)
    {
        using var db = autoQuery.GetDb(query, base.Request);
        var q = autoQuery.CreateQuery(query, base.Request, db) //Populated SqlExpression

        q.Where(x => x.Deleted == null);
        var states = request.Is ?? [];
        if (states.Contains("closed") || states.Contains("completed") || states.Contains("declined"))
            q.And(x => x.Status == "closed");
        else
            q.And(x => x.Hidden == null && (x.Status == null || x.Status != "closed"));

        if (states.Length > 0)
        {
            var labelSlugs = states.Where(x => x != "closed" && x != "open")
                .Map(x => x.GenerateSlug());
            if (labelSlugs.Count > 0)
                q.And($"{PgSql.Array(labelSlugs)} && labels");
        }

        if (!request.AnyTechnologyIds.IsEmpty())
        {
            q.And($"{PgSql.Array(request.AnyTechnologyIds)} && technology_ids");
        }

        return AutoQuery.Execute(request, q, base.Request);
    }
}

The above implementation also caches all QueryPosts responses as a result of being defined in a Service annotated with [CacheResponse] attribute.

IAutoQueryDb API

To increase the versatility of using AutoQuery functionality in custom Service implementations, IAutoQueryDb supports both parallel Sync and Async APIs if needing to enlist AutoQuery functionality in Sync methods that are unable to be refactored to use the async APIs:

public interface IAutoQueryDb : IAutoCrudDb
{
    // Generic API to resolve the DB Connection to use for this request
    IDbConnection GetDb<From>(IRequest req = null);

    // Generate a populated and Typed OrmLite SqlExpression using same model as the source and output target
    SqlExpression<From> CreateQuery<From>(IQueryDb<From> dto, Dictionary<string, string> dynamicArgs,
        IRequest req = null, IDbConnection db = null);

    // Execute an OrmLite SqlExpression using the same model as the source and output target
    QueryResponse<From> Execute<From>(IQueryDb<From> model, SqlExpression<From> query, 
        IRequest req = null, IDbConnection db = null);

    // Async Execute an OrmLite SqlExpression using the same model as the source and output target
    Task<QueryResponse<From>> ExecuteAsync<From>(IQueryDb<From> model, SqlExpression<From> query, 
        IRequest req = null, IDbConnection db = null);

    // Generate a populated and Typed OrmLite SqlExpression using different models for source & output target
    SqlExpression<From> CreateQuery<From,Into>(IQueryDb<From,Into> dto, Dictionary<string,string> dynamicArgs,
        IRequest req = null, IDbConnection db = null);

    // Execute an OrmLite SqlExpression using different models for source and output target
    QueryResponse<Into> Execute<From, Into>(IQueryDb<From,Into> model, SqlExpression<From> query, 
        IRequest req = null, IDbConnection db = null);

    // Async Execute an OrmLite SqlExpression using different models for source and output target
    Task<QueryResponse<Into>> ExecuteAsync<From, Into>(IQueryDb<From,Into> model, SqlExpression<From> query, 
        IRequest req = null, IDbConnection db = null);
}

The IAutoQueryDb inherits IAutoCrudDb APIs below and can access both AutoQuery and CRUD functionality.

Likewise the new AutoQuery Crud APIs also have sync & async implementations:

public interface IAutoCrudDb
{
    // Inserts new entry into Table
    object Create<Table>(ICreateDb<Table> dto, IRequest req);
    
    // Inserts new entry into Table Async
    Task<object> CreateAsync<Table>(ICreateDb<Table> dto, IRequest req);
    
    // Updates entry into Table
    object Update<Table>(IUpdateDb<Table> dto, IRequest req);
    
    // Updates entry into Table Async
    Task<object> UpdateAsync<Table>(IUpdateDb<Table> dto, IRequest req);
    
    // Partially Updates entry into Table (Uses OrmLite UpdateNonDefaults behavior)
    object Patch<Table>(IPatchDb<Table> dto, IRequest req);
    
    // Partially Updates entry into Table Async (Uses OrmLite UpdateNonDefaults behavior)
    Task<object> PatchAsync<Table>(IPatchDb<Table> dto, IRequest req);
    
    // Deletes entry from Table
    object Delete<Table>(IDeleteDb<Table> dto, IRequest req);
    
    // Deletes entry from Table Async
    Task<object> DeleteAsync<Table>(IDeleteDb<Table> dto, IRequest req);

    // Inserts or Updates entry into Table
    object Save<Table>(ISaveDb<Table> dto, IRequest req);

    // Inserts or Updates entry into Table Async
    Task<object> SaveAsync<Table>(ISaveDb<Table> dto, IRequest req);
}

Due to its internal pre-defined behavior, AutoQuery CRUD custom Service implementations have limited customizability over its implementation but still allows you to apply custom logic like apply Custom Filter Attributes, include additional validation, augment the Response DTO, etc.

E.g. This implementation applies the [ConnectionInfo] behavior to all its Services which will instead execute queries on the registered Reporting named connection:

[ConnectionInfo(NamedConnection = "Reporting")]
public class MyReportingServices(IAutoQueryDb autoQuery) : Service
{
    public Task<object> Any(CreateReport request) => autoQuery.CreateAsync(request,base.Request);
}

AutoQuery Response Filters

The Aggregate functions feature is built on the new ResponseFilters support in AutoQuery which provides a new extensibility option enabling customization and additional metadata to be attached to AutoQuery Responses. As the Aggregate Functions support is itself a Response Filter in can disabled by clearing them:

services.AddPlugin(new AutoQueryFeature {
    ResponseFilters = new List<Action<QueryFilterContext>>()
})

The Response Filters are executed after each AutoQuery and gets passed the full context of the executed query, i.e:

class QueryFilterContext
{
    IDbConnection Db             // The ADO.NET DB Connection
    List<Command> Commands       // Tokenized list of commands
    IQuery Request               // The AutoQuery Request DTO
    ISqlExpression SqlExpression // The AutoQuery SqlExpression
    IQueryResponse Response      // The AutoQuery Response DTO
}

Where the Commands property contains the parsed list of commands from the Include property, tokenized into the structure below:

class Command 
{
    string Name
    List<string> Args
    string Suffix
}

With this we could add basic calculator functionality to AutoQuery with the custom Response Filter below:

services.AddPlugin(new AutoQueryFeature {
    ResponseFilters = {
        ctx => {
            var supportedFns = new Dictionary<string, Func<int, int, int>>(StringComparer.OrdinalIgnoreCase)
            {
                {"ADD",      (a,b) => a + b },
                {"MULTIPLY", (a,b) => a * b },
                {"DIVIDE",   (a,b) => a / b },
                {"SUBTRACT", (a,b) => a - b },
            };
            var executedCmds = new List<Command>();
            foreach (var cmd in ctx.Commands)
            {
                Func<int, int, int> fn;
                if (!supportedFns.TryGetValue(cmd.Name, out fn)) continue;
                var label = !string.IsNullOrWhiteSpace(cmd.Suffix) ? cmd.Suffix.Trim() : cmd.ToString();
                ctx.Response.Meta[label] = fn(int.Parse(cmd.Args[0]), int.Parse(cmd.Args[1])).ToString();
                executedCmds.Add(cmd);
            }
            ctx.Commands.RemoveAll(executedCmds.Contains);
        }        
    }
})

Which now lets users perform multiple basic arithmetic operations with any AutoQuery request!

var response = client.Get(new QueryRockstars {
    Include = "ADD(6,2), Multiply(6,2) SixTimesTwo, Subtract(6,2), divide(6,2) TheDivide"
});

response.Meta["ADD(6,2)"]      //= 8
response.Meta["SixTimesTwo"]   //= 12
response.Meta["Subtract(6,2)"] //= 4
response.Meta["TheDivide"]     //= 3

Untyped SqlExpression

If you need to introspect or modify the executed ISqlExpression, it’s useful to access it as a IUntypedSqlExpression so its non-generic API's are still accessible without having to convert it back into its concrete generic SqlExpression<T> Type, e.g:

IUntypedSqlExpression q = ctx.SqlExpression.GetUntypedSqlExpression()
    .Clone();

TIP

Cloning the SqlExpression allows you to modify a copy that won't affect any other Response Filter.

AutoQuery Property Mapping

AutoQuery can map [DataMember] property aliases on Request DTO's to the queried table, e.g:

public class QueryPerson : QueryDb<Person>
{
    [DataMember(Name = "first_name")]
    public string FirstName { get; set; }
}

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

Which can be queried with:

?first_name=Jimi

or by setting the global JsConfig.Init(Config { TextCase = TextCase.SnakeCase }) convention:

public class QueryPerson : QueryDb<Person>
{
    public string LastName { get; set; }
}

Where it's also queryable with:

?last_name=Hendrix

Extensibility with QueryFilters

We've already covered some of extensibility options with Customizable QueryDbFields and Implicit Conventions, the most customizable would be to override the default implementation with a custom one, e.g:

public class MyQueryServices(IAutoQueryDb autoQuery) : Service
{
    //Override with custom implementation
    public object Any(FindMovies dto)
    {
        var q = autoQuery.CreateQuery(dto, Request.GetRequestParams(), base.Request);
        return autoQuery.Execute(dto, q, base.Request);
    }
}

There's also a lighter weight option by registering a typed Query Filter, e.g:

var autoQuery = new AutoQueryFeature()
  .RegisterQueryFilter<QueryRockstarsFilter, Rockstar>((q, dto, req) =>
      q.And(x => x.LastName.EndsWith("son"))
  )
  .RegisterQueryFilter<IFilterRockstars, Rockstar>((q, dto, req) =>
      q.And(x => x.LastName.EndsWith("son"))
  );

services.AddPlugin(autoQuery);

Registering an interface like IFilterRockstars is especially useful as it enables applying custom logic to a number of different Query Services sharing the same interface.

Intercept and Introspect Every Query

As mentioned earlier AutoQuery works by auto-generating ServiceStack Services for each Request DTO marked with IQueryDb unless a custom implementation has been defined for that Query. It's also possible to change the base class for the generated services so that all queries execute your custom implementation instead.

To do this, create a custom Service that inherits from AutoQueryServiceBase and overrides both Exec methods with your own implementation, e.g:

public abstract class MyAutoQueryServiceBase : AutoQueryServiceBase
{
    public override object Exec<From>(IQueryDb<From> dto)
    {
        var q = AutoQuery.CreateQuery(dto, Request.GetRequestParams(), base.Request);
        return AutoQuery.Execute(dto, q, base.Request);
    }

    public override object Exec<From, Into>(IQueryDb<From, Into> dto)
    {
        var q = AutoQuery.CreateQuery(dto, Request.GetRequestParams(), base.Request);
        return AutoQuery.Execute(dto, q, base.Request);
    }
}

Then tell AutoQuery to use your base class instead, e.g:

services.AddPlugin(new AutoQueryFeature { 
    AutoQueryServiceBaseType = typeof(MyAutoQueryServiceBase)
});

Which will now get AutoQuery to execute your implementations instead.

Exclude AutoQuery Collections from being initialized

The default configuration for all languages supported in Add ServiceStack Reference is to InitializeCollections which allows for a nicer client API in which clients can assume Request DTO's have their collections initialized allowing them to use the shorthand collection initializer syntax, e.g:

var response = client.Get(new SearchQuestions { 
    Tags = { "redis", "ormlite" }
});

AutoQuery CRUD Batch Requests

All AutoQuery CRUD operations support auto batch implementations which will by default execute all AutoQuery CRUD Requests within a DB transaction.

By default it will generate AutoBatch implementations for all CRUD operations and can be changed to only generate implementations for specific CRUD operations by changing:

services.AddPlugin(new AutoQueryFeature {
    GenerateAutoBatchImplementationsFor = new() { AutoCrudOperation.Create }
});

It also wont generate implementations for custom AutoBatch implementations, e.g. you can add a custom implementation that does what the generated implementation would've done and execute using the same DB Connection and Transaction with:

public class CustomAutoQueryServices(IAutoQueryDb autoQuery) : Service
{
    public object Any(CreateItem[] requests)
    {
        using var db = autoQuery.GetDb<Item>(Request);
        using var dbTrans = db.OpenTransaction();

        var results = new List<object>();
        foreach (var request in requests)
        {
            var response = await autoQuery.CreateAsync(request, Request, db);
            results.Add(response);
        }

        dbTrans.Commit();
        return results;            
    }
}

As AutoQuery Services are normal ServiceStack Services they can re-use the existing Service Client support for Auto Batched Requests, e.g:

var client = new JsonApiClient(BaseUrl);
var response = client.SendAll(new CreateItem[] { ... });

Queryable APIs

On this page