AutoQuery for Distinct list of Field values when MaxLimit is set

AutoQuery set to
new AutoQueryFeature
MaxLimit = 100

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

SQL Exception thrown

Basically the sql produced with ordering the select statement

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?

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.

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.

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

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!