OrderBy and Distinct using LINQ-to-Entities

Question

Here is my LINQ query:

(from o in entities.MyTable
orderby o.MyColumn
select o.MyColumn).Distinct();

Here is the result:

{"a", "c", "b", "d"}

Here is the generated SQL:

SELECT 
[Distinct1].[MyColumn] AS [MyColumn]
FROM ( SELECT DISTINCT 
    [Extent1].[MyColumn] AS [MyColumn]
    FROM [dbo].[MyTable] AS [Extent1]
)  AS [Distinct1]

Is this a bug? Where's my ordering, damnit?

score 10 · Accepted Answer · edited Mar 12 '10 at 20:23

10

You should sort after Distinct as it doesn't come with any guarantee about preserving the order:

entities.MyTable.Select(o => o.MyColumn).Distinct().OrderBy(o => o);

edited Mar 12 '10 at 20:23

BlueRaja - Danny Pflughoeft

84,206
33
197
283

answered Mar 12 '10 at 20:09

Mehrdad Afshari

414,610
91
852
789

This code does not compile. OrderBy gives the error: `The type arguments cannot be inferred from the usage. Try specifying the type arguments explicitly.` – BlueRaja - Danny Pflughoeft Mar 12 '10 at 20:13
Oh I see, it needs to be `OrderBy(o => o)` since by that point the items have already been selected (and we are working with an `IQueryable`). I've fixed your code and given you the checkmark - Thanks! – BlueRaja - Danny Pflughoeft Mar 12 '10 at 20:22

score 1 · Answer 2 · edited May 23 '17 at 12:19

1

This question discusses the rules for Linq to Objects: Preserving order with LINQ

In the database, even fewer operations preserve order. No one anywhere preserves order when Distinct'ing (as generally a Hash algorithm is used).

edited May 23 '17 at 12:19

Community

1
1

answered Mar 12 '10 at 20:21

Amy B

108,202
21
135
185

Still, the query `SELECT DISTINCT ... ORDER BY ...` works as you would expect in SQL Server, and likely in other DBMS's - is there a reason the `orderby` LINQ clause is just ignored in this case when generating the SQL (it could at least throw an error)? I still think this is a bug - the LINQ query works correctly when used on a `String[]`. – BlueRaja - Danny Pflughoeft Mar 12 '10 at 21:49
If you check the query plan, it will reveal that the ORDER BY occurs after the DISTINCT. As such, there are strict limitations on what may appear in the ORDER BY. This is just another case where SQL's syntax causes confusion. – Amy B Mar 13 '10 at 17:04
What's the "query plan," and how do I view it? I obtained that query above using SQL Server 2008 Profiler, but it obviously only gives you the final query. – BlueRaja - Danny Pflughoeft Mar 13 '10 at 18:15
1

put the query into a query window in SqlStudio (formerly known as Query Analyzer). Click "display estimated execution plan". – Amy B Mar 13 '10 at 22:33

OrderBy and Distinct using LINQ-to-Entities

2 Answers2