SelectDistinct cause exception, Why the Order By is mandatory

[layoric]

Now that your QueryDb is using QueryDb<Company>, you only need to use SelectDistinct(x => x.<specific fields>) as it is already operating on that table, but generally you’ll want to specify what columns DISTINCT is being used against, and generally not all of them which is what SelectDistinct<T>(x => x) is doing.

You can use any type for specifying fields, even anonymous types for specifying these fields, see here.

Autoquery Distinct on Joined Column

Here’s some valid Examples for SelectDistinct(): .SelectDistinct(x => x.Field) // single field .SelectDistinct(x => new { x.Field1, x.Field2 }) // multiple fields .SelectDistinct<T1,T2>((x,y) => new { x.Field1, y.Field2 }) //multi fields/tables

The response.Total is not something you should need to update yourself. And since you are using AutoQuery, you can let the user specify if DISTINCT is needed by using ?Fields=DISTINCT Field1,Field2 in the URL as well if that is what you need.

Auto Query RDBMS Distinct?

I’ve had a look and calling .SelectDistinct() isn’t going to work because the .Select(fields) from the custom fields list overrides it, it would need to call .SelectDistinct(fields) which I’ve just added to OrmLite. I’ve also added support for DISTINCT in AutoQuery in this commit by prefixing your fields list with DISTINCT, e.g: /query?Fields=DISTICT Field1,Field2 This change is available from v5.0.1 that’s now available on MyGet.

For counting distinct, you can use Sql.CountDistinct in a db.Select but here you shouldn’t need to as the response total is calculated for you.

Another useful tool to check what SQL is being generated after a query is run is to use db.GetLastSql(). This will return the SQL used for the last query, you can use this to inspect how things are working.