Why does adding two .OrderBy (or .OrderByDescending) statements apply the sorts in reverse order?

Question

I came across the following in some code that I was refactoring today.

context.Entities.Where(x => x.ForeignKeyId == id)
    .OrderBy(x => x.FirstSortField)
    .OrderBy(x => x.SecondSortField);

Initially, I took out the .OrderBy(x => x.FirstSortField) thinking that the first OrderBy statement would just be replaced by the second OrderBy. After testing, I realized that it was generating the SQL as ORDER BY SecondSortField, FirstSortField.

Therefore, the equivalent is actually:

context.Entities.Where(x => x.ForeignKeyId == id)
    .OrderBy(x => x.SecondSortField)
    .ThenBy(x => x.FirstSortField);

Can anyone explain the reasoning behind EF6 doing this? It seems to me that it would be more intuitive to replace the first sort field with the second.

Answer 1

This is all in terms of local data, but EF wants to do the logical equivalent when building the expression tree and writing your query.

You should research the concept of stable sorting. When you use a stable sorting algorithm, the original order of equal items is preserved.

So let's say you have data like this with obvious first name/last name fields:

Brad Jones
Tom Smith
Sam Jones
Jim Doe
James Smith
Ryan Smith

If you order initially by only by first name, you get this:

Brad Jones
James Smith
Jim Doe
Ryan Smith
Sam Jones
Tom Smith

If you now take this sorted list, and again sort by last name, you get a result sorted by both fields, where later sorts have precedence over earlier sorts... but you only guarantee the exact order if the sort is stable:

Jim Doe
Brad Jones
Sam Jones
James Smith
Ryan Smith
Tom Smith

This brings us to the question of what algorithm .Net uses, and whether it's stable. To the documentation we go, where we find this in the Remarks section:

This method performs a stable sort

The specific algorithm is not documented here. I believe it's a Quicksort, but leaving that out of the documentation is probably intentional, to allow the maintainers to update for the best available option that meets the stability requirement should something better be discovered.

But, again, that's for local data. Databases will do what the SQL tells them.

Answer 2

I can only conclude that we're actually looking at LINQ-to-SQL here. In Linqpad until v. 5, it's very easy to make this mistake, because the selection of a EF6 DbContext driver is easily overlooked when creating a new connection. (In Linqpad v6 this choice is more conspicuous).

I have tested the reported behavior in EF6, EF-core 3 and 5, and in LINQ-to-SQL. Only in the latter do I see a generated SQL statement with two columns in the ORDER BY.

The statement...

Products.OrderBy(p => p.Description).OrderBy(p => p.LastSale)

...is translated by LINQ-to-SQL as:

SELECT [t0].[ID], [t0].[Description], [t0].[Discontinued], [t0].[LastSale]
FROM [Product] AS [t0]
ORDER BY [t0].[LastSale], [t0].[Description]

The reasoning is explained in this answer, which boils down to: LastSale is the dominant ordering field as it, sort of, overrides the first OrderBy.

All EF queries only have ORDER BY LastSale.

I must say agree with EF's implementation. As this answer explains, the result of two consecutive orderings depends on the sorting algorithm. Which means that all we can say for sure is that the results from any LINQ query will be ordered by LastSale and the ordering within LastSale groups isn't certain. Then, IMO, it's a better choice for SQL translation to handle a second OrderBy statement as a full override of the first one so it's visible that no expectations can be based on the first statement. To me it's more intuitive.

The message is: be explicit when ordering by multiple fields. Use OrderBy - ThenBy. Don't rely on a database provider's handling of consecutive OrderBy statements.

Answer 3

Actually, it is quite simple and it makes total sense:

The first part of the query

context.Entities.Where(x => x.ForeignKeyId == id)

would be translated to SQL more or less like this

select * from Entities

Adding the first order by

.OrderBy(x => x.FirstSortField)

it would be translated to this

select * from (
   select * from Entities
)
order by FirstSortField

and then adding the second order by

.OrderBy(x => x.FirstSortField)
.OrderBy(x => x.SecondSortField)

would be translated to:

select * from (
   select * from (
      select * from Entities
   )
   order by FirstSortField
)
order by SecondSortField

Entity framework is smart enough to simplify to something like

select * from Entities
order by SecondSortField, FirstSortField