Document ordering expectations for raw SQL

[kostat]

Queries of the form

...
.Take()
.Include().ThenInclude()

are translated the following way:

SELECT ...
FROM (
  SELECT ...
  FROM (...
  ) as l
  LIMIT... -- Take()
) as t
JOIN ... -- Include() entity
JOIN ... -- ThenInclude() entity
ORDER BY t... l... -- the extra ORDER BY

An additional ORDER BY overrides any ordering of the most inner FROM (or natural ordering) if it's a raw query.

---

EF Core version: both 3.1 and 5
Database provider: (tested on PG)
Target framework: .NET Core 3.1

[roji]

Duplicate of dotnet/efcore#19571

[roji]

An additional ORDER BY overrides any ordering of the most inner FROM (or natural ordering) if it's a raw query.

Note that in SQL there is no natural order, and no deterministic order for rows coming out of a subquery - so technically nothing is being overridden. See dotnet/efcore#19571 for why the ORDER BY is necessary when joining.

[kostat]

@roji : the actual query is as following

SELECT ...
FROM (
  SELECT ...
  FROM ( -- raw custom query
    SELECT ... ORDER BY ...
    UNION ALL
    SELECT ... ORDER BY ...
  ) as l
  LIMIT... -- Take()
) as t
JOIN ... -- Include() entity
JOIN ... -- ThenInclude() entity
ORDER BY t... l... -- the extra ORDER BY

Created using the following technique: https://docs.microsoft.com/en-us/ef/core/querying/raw-sql#composing-with-linq

So the result is correct rows (since LIMIT is inside), but ordered wrongly. Fortunately I could reorder them correctly in the application as a workaround.

By saying natural ordering I wanted to say that there are many ways to imply an order in SQL, not only by using ORDER BY clause. (I can give more examples if needed)

[kostat]

I also think that Take() and the actual result order should be logically consistent between them

[kostat]

You even suggest using ORDER BY with OFFSET here. Or it's a documentation bug?

[smitpatel]

Order By inside raw SQL are opaque to EF Core. EF Core may decide to order query to generate correct result based on how the data being fetched from server. If above query, you would be also getting warning that Take without OrderBy is used. You must specify order by in LINQ (even though it is same as ordering in raw sql, if the ordering is important for results.

[kostat]

@smitpatel: In my specific case your suggestion isn't possible since the ordering comes from UNION operator. Also, as I mention above, the order can be implied in many different ways, including ORDER BY a field that is not part of the returned resultset. So specifying it in LINQ is not generally doable.

Therefore the limitation you describe is very restrictive and actually not mentioned in the docs.

You are a second stuff member who tells this is an esoteric case or my own fault. I want to emphasize that I reported this issue because I thought you missed an important scenario. If you think it's not, or the behavior is by design, that's OK, I already implemented a workaround ;-).

[smitpatel]

EF Core adds order by because it requires it to generate result. If that ordering is not what you want for ordering of result set then outside of your raw SQL you have to specify ordering. How to make that data available is user choice. From LINQ perspective, there is no OrderBy or variation which enforces that result has to have specific ordering and EF Core respects the LINQ semantics specified. In case of no semantics specified then there is no such guarantee and EF Core worked in the bounds of that.

It is not a limitation, rather intended design. It is not restrictive either. You are getting your correct results back. Again if ordering of results is important then it needs to be explicit.

[AndriySvyryd]

@smitpatel We should document this

[roji]

@kostat to reiterate and add on everything @smitpatel wrote... looking at the subquery with alias t above, SQL really does not guarantee that the internal ordering within that subquery is preserved outside of it. We specifically know that with PostgreSQL, ordering will actually be different, so unless an ORDER BY is explicitly specified outside, ordering is non-deterministic.

[kostat]

@roji: thanks for the tip. I'll consider moving the Take() - LIMIT logic inside the raw query.