SelectDistinct cause exception, Why the Order By is mandatory

[aleblanc]

System.ArgumentException: ‘ORDER BY items must appear in the select list if SELECT DISTINCT is specified.’

        public object Any(QueryUserCompany query)
        {

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

            q.LeftJoin<Company>((t1, t2) => t1.CompanyId == t2.Id ).OrderBy(x => x.UserId).SelectDistinct();

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

[layoric]

Hi @aleblanc,

Can you let us know what version of ServiceStack you are using as well as the database provider you are using + have configured with OrmLite. Thanks

[aleblanc]

Hi layoric,

Service Stack 5.11

    public void Configure(IServiceCollection services)
    {

        var con = Configuration.GetConnectionString("DefaultConnection");
        services.AddSingleton<IDbConnectionFactory>(new OrmLiteConnectionFactory(con, SqlServer2019Dialect.Provider));
    }

My first try was without the OrderBy, then I add the OrderBy. I have the exception on both case.

Thanks

[layoric]

It looks like the error is coming from SQL server due to invalid SQL as the error suggests.

I’m having trouble replicating the problem, could you share your DB model classes, eg UserCompany and Company. I created some mock ones that match your code above but likely I’m missing something.

Alternatively, I think if you specify a column for SelectDistinct that is also in your OrderBy, that should at least get you going again. Let me know how you go.

[aleblanc]

My correct result is return with this sql:

select distinct c.*
from UserSettings as us
left join Companies as c on us.CompanyId =c.Id
where us.UserId = ‘d2c4a6b5-8d59-4277-acca-0ede2c0a6e10’

CREATE TABLE [dbo].[Companies](
	[CompanyName] [varchar](255) NULL,
	[CompanyCode] [varchar](16) NULL,
	[Id] [uniqueidentifier] NOT NULL,
	[CreatedBy] [varchar](255) NULL,
	[CreatedDate] [datetime] NOT NULL,
	[ModifiedBy] [varchar](255) NULL,
	[ModifiedDate] [datetime] NULL,
	[IsActive] [bit] NOT NULL,
	[Timestamp] [datetime] NOT NULL,

CREATE TABLE [dbo].[UserSettings](
[UserId] [uniqueidentifier] NOT NULL,
[CompanyId] [uniqueidentifier] NOT NULL,
[FeatureId] [uniqueidentifier] NOT NULL,
[Add] [bit] NOT NULL,
[Read] [bit] NOT NULL,
[Update] [bit] NOT NULL,
[Delete] [bit] NOT NULL,
[Id] [uniqueidentifier] NOT NULL,
[CreatedBy] varchar NULL,
[CreatedDate] [datetime] NOT NULL,
[ModifiedBy] varchar NULL,
[ModifiedDate] [datetime] NULL,
[IsActive] [bit] NOT NULL,
[Timestamp] [datetime] NOT NULL,

[layoric]

I still have an incomplete picture of what is going on, so I will assume UserSettings is mapping to the QueryUserCompany via QueryDb<UserSettings>?

Currently your SelectDistinct won’t be operating on the Company table if that is the case since the original QueryDb is operation on another table and then LeftJoining to it.

I’d suggest removing the OrderBy AND also changing the select distinct to .SelectDistinct<Company>(x => x) to specify you want company columns in the results.

To see what SQL is running you can check the full exception on the AutoQuery.Execute line, or also use var sql = q.ToSelectStatement(). and log that out, it will likely give you a better idea of what is going on. I get the following sql in my attempt at a minimal reproduction.

SELECT DISTINCT "Company"."Id", "Company"."CompanyName", "Company"."CompanyCode" 
FROM "UserCompany" 
LEFT JOIN "Company" ON ("UserCompany"."CompanyId" = "Company"."Id") 
ORDER BY "UserCompany"."Id" 
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY

Otherwise, create a minimal reproduction of the problem and post it up on GitHub and I’ll take a look.

[aleblanc]

@layoric

.SelectDistinct(x => x)

Was the missing part on my side. The Total in response do not seem to reflect the return results

I rework my code a little bit

[Route("/usercompanies", "GET")]
public class QueryUserCompany
    : QueryDb<Company>, ILeftJoin<Company, UserSetting>, IGet
{ 
    public Guid? UserId { get; set; }
    public string UserEmail{ get; set; }
}

    public object Any(QueryUserCompany query)
    {
        using var db = AutoQuery.GetDb(query, base.Request);
        var q = AutoQuery.CreateQuery(query, base.Request, db);

        q.SelectDistinct<Company>(x=>x);
        var response = AutoQuery.Execute(query, q, base.Request, db);
        **response.Total = (int) db.Count(q);**
        return response;
    }

The total is not taken into account the DISTINCT. So yes I have 3 records in my result set but the distinct is filtering it correctly but the total is not accurate.

{
“offset”: 0,
“total”: 3,
“results”: [
{
“companyName”: “Lallemand LHS”,
“companyCode”: “30”,
“fullCompanyName”: “30-Lallemand LHS”,
“id”: “5c7a88d4-2be0-4a71-8044-044cb6fbe0e8”,
“createdBy”: null,
“createdDate”: “2008-05-12T09:27:57.4430000”,
“modifiedBy”: null,
“modifiedDate”: null,
“isActive”: true,
“timestamp”: “2021-02-03T10:18:22.7600000”
},
{
“companyName”: “Siebel”,
“companyCode”: “0”,
“fullCompanyName”: “0-Siebel”,
“id”: “168a058d-4e47-4983-9004-8bf892d925d2”,
“createdBy”: null,
“createdDate”: “2021-02-07T00:00:00.0000000”,
“modifiedBy”: null,
“modifiedDate”: null,
“isActive”: false,
“timestamp”: “2021-02-07T20:21:52.1830000”
}
],
“meta”: null,
“responseStatus”: null
}

Thanks a lot for your support!

[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.

[aleblanc]

@layoric

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.

I tried to update response total because it was not accurate. When it’s not there I still have a 3. I will work my query further.

Thanks a lot for the amazing support as usual