If you use distinct in the fields section how do you also get total available records?

ServiceStack Auto Query
1

When using auto query with distinct such Fields=“Distinct InvoiceID” how do you get the total records available, as it appears Include=“Total” takes into account the duplicates/non distinct records as well?

2

That would require executing another query which you should be able to do within a Custom AutoQuery implementation, e.g:

// Override with custom implementation
public class MyQueryServices : Service
{
    public IAutoQueryDb AutoQuery { get; set; }

    // Sync
    public object Any(MyQuery query)
    {
        using var db = AutoQuery.GetDb(query, base.Request);
        var q = AutoQuery.CreateQuery(query, base.Request, db);
        var response = AutoQuery.Execute(query, q, base.Request, db);

        var countSql = q.ToCountStatement();
        response.Total = db.Count(countSql, q.Params);

        return response;
    }
}

You may need to modify the Count SQL to suit your use-case, here’s an example of creating custom SQL reusing the APIs populated SqlExpression:

var countSql = "SELECT COUNT(*)" + q.BodyExpression;

As AutoQuery APIs need to always return an AutoQuery QueryResponse<T> DTO, if needed you can add any custom data in the Meta dictionary, e.g:

response.Meta ??= new();
response.Meta["MyCount"] = db.Count(countSql, q.Params);