Efcore: Query: incorrect sql generated for queries with Distinct Skip, FirstOrDefault (without order by)

[maumar]

query:

from l1 in ctx.LevelOne
where l1.Id < 3
select (from l3 in ctx.LevelThree
        orderby l3.Id
        select l3).Distinct().Skip(1).FirstOrDefault().Name

produces the following sql:

SELECT (
    SELECT TOP(1) [t].[Name]
    FROM (
        SELECT DISTINCT [l3].*
        FROM [Level3] AS [l3]
        ORDER BY (SELECT 1)
        OFFSET 1 ROWS
    ) AS [t]
)
FROM [Level1] AS [l1]
WHERE [l1].[Id] < 3

which throws:

ORDER BY items must appear in the select list if SELECT DISTINCT is specified. We should probably force client evaluation here

[ajcvickers]

Try to see if we can translate it, otherwise client eval.

[smitpatel]

Non-deterministic. Distinct does not guarantee ordering of the elements. Hence when distinct is applied we clear order by. And then apply skip/take. I wonder if we should try to make this scenario work.

One possible option is to delay the removal of ordering for distinct case at the time of sql generation. another would be to preserve the ordering on SQL server.

@anpete - thoughts?

[sebestyn168]

Possible other effects of Distinct :
removing Include - workaround : setting includes after distinct.
count failes - quering wtih joins to filter on linked criteria requires use of disctinct to avoid duplicated lines. query.ToArrayAsync() works and retrieve 16 elements, query.Count() retrieve 40 corresponding to the duplicated lines count.

[roji]

Note: got another complaint about this in https://github.com/npgsql/efcore.pg/issues/1195. Minimal repro:

c# var result = ctx.Blogs .Distinct() .Skip(1) .ToList();

@smitpatel assuming ORDER BY (SELECT 1) only gets added for Skip/Take without OrderBy, we could maybe just pushdown the query if Skip/Take is applied on SelectExpression with Distinct without OrderBy?

[loic-sharma]

@roji I also ran into this when upgrading to .NET Core 3, EF Core 3, and ASP.NET Core 3. See this: https://github.com/loic-sharma/BaGet/pull/444/files#r368774571

[roji]

@loic-sharma thanks for the note, the right workaround (and probably the right way to right the code regardless of the bug) is to add that OrderBy.