EF Core 3.1 generates ORDER BY (SELECT 1) which breaks old code

Question

I'm upgrading our project from .NET Core 2.1 to 3.1 and am facing the following issue with EF Core.

I have the following code:

var all = this.Context.Devices.OrderBy(c => c.Id).Where(predicate).Distinct();
var result = await all.Skip(pagedRequest.Offset).Take(pagedRequest.Limit).ToListAsync();
var totalCount = await all.CountAsync();

This is basically used for pagination and also gives the total element count to the client. Predicate is a dynamically generated filter condition. Why is Distinct() really used I haven't figured out yet (it's some old code I'm facing). One may well argue if it's needed, but that's certainly valid, and so does not change the question. However the following SQL is generated:

 SELECT [d].[Id], ...
FROM [Device] AS [d]
WHERE ([d].[State] = 2) AND EXISTS (
    SELECT 1
    FROM [AnotherTable] AS [r]
    WHERE ([d].[Id] = [r].[DeviceId]) AND ([r].[UserId] = 1))
ORDER BY (SELECT 1)
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY

And than I get ORDER BY items must appear in the select list if SELECT DISTINCT is specified. It was not appearing in .net ef 2.1. I looked at the breaking changes and didn't find anything that could explain it. Also I don't understand what the heck is ORDER BY (SELECT 1) and why is it generated instead of order by id.

I've checked now the SQL generated with the same code but EF 2.1. It's exactly the same only that there is ORDER BY [c].[Id] instead of ORDER BY (SELECT 1), as I would expect.

Can somebody explain this behaviour?

P.S.: another interesting thing is that if I hover over all in debugger and see the Results view, I see there the objects. Exception comes from the second code line where Skip and Take are added.

Answer 1

OrderBy followed by Distinct means that all has no defined ordering (the Distinct can and will reorder things).

In turn, this means that the Skip and Take are ill-defined, because they're operating against an unordered set of rows.

So the code was always broken, it's just now more apparent.

If you make sure Skip and Take are working against an IOrderedEnumerable, and more specifically, one which uniquely defines the ordering of all rows, then the code will be well-defined, and that specific ordering will be reflected in the code that EF generates.

One way to do that would be to remove the Distinct. Another would be another call to OrderBy after the Distinct.

---

I don't understand what the heck is ORDER BY (SELECT 1)

It's an unfortunate workaround that someone has found to silence the warning that was generated when no ORDER BY is specified when using FETCH NEXT. Unfortunately, people can and will do such things (even people working on tools like EF) rather than considering that the warning was there for good reason (that the set of rows was unordered).