AutoQuery for Distinct list of Field values when MaxLimit is set

ServiceStack Auto Query
1

AutoQuery set to
new AutoQueryFeature
{
MaxLimit = 100
}

RequestDto
var findReportRequest = new FindReportRequest
{
Fields = $“distinct {fieldName}”
};

SQL Exception thrown

image.png824×115 9.69 KB

Basically the sql produced with ordering the select statement
ORDER BY “Table”.“Id” OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

I can make the request work by removing the MaxLimit.

Is it possible to both the MaxLimit set and get distinct lists of field values?

2

Looks like you’ve hit a limitation of SQL Server where it needs any OrderBy fields to appear in the select list.

You can either specify your own OrderBy or OrderByDesc (of the distinct field) or disable implicit ordering for paged queries with:

Plugins.Add(new AutoQueryFeature {
    OrderByPrimaryKeyOnPagedQuery = false
})

But that would reduce the predictability of paged query results.

3

I changed the settings to include

OrderByPrimaryKeyOnPagedQuery = false

It didn’t change the SQL issued. The order by clause is still being included in the sql.

4

Are you sure, It only adds it if it’s enabled:

It would also be the last thing I’d change, did you try changing the OrderBy?:

var findReportRequest = new FindReportRequest {
    Fields = $$"distinct {fieldName}",
    OrderBy = fieldName
};
5

I didn’t try the OrderBy setting directly. I tried the suggestion and it worked.

On the OrderByPrimaryKeyOnPagedQuery = false
Pretty sure, I’m doing this;
new AutoQueryFeature
{
MaxLimit = 100,
OrderByPrimaryKeyOnPagedQuery = false
}
The MaxLimit is being respected.

Now I have a work around though, I’m good. Thanks for your help!