Wednesday 7 November 2007

Issues with LINQ Order By Queries when using Distinct

Encountered an unusual issue today whereby the .Distinct() extension method call on a LINQ query will actually mean that the output SQL will NOT have an order by clause in it. Distinct basically forces an ignore on any orderby statements that you have:

- This will NOT generate the Order By


   1:  var test = (from c in db.CreditRequests

   2:  where c.DateRejected != null

   3:  orderby c.DateRejected

   4:  select c).Distinct();



- This WILL generate the Order By


   1:  var test = (from c in db.CreditRequests

   2:  where c.DateRejected != null

   3:  orderby c.DateRejected

   4:  select c);





A way around this is to use Lamda Expressions



   1:  var test = (from c in db.CreditRequests

   2:                          where c.DateRejected != null

   3:                          select c).Distinct().OrderBy(d => d.DateRejected).OrderBy(d => d.CreditRequestID);

5 comments:

saxx said...

I just stumpled over the same issue - do you have any idea why LINQ to SQL handles it that way?

Unknown said...

This was extremely helpful, thank you!! The Lambda trick worked. In my case the problem was partially hidden by a .Skip(x) function that apparently forces an ORDER BY to be inserted into the SQL query, but the ORDER BY clause isn't inserted if x == 0, so only my very first result was wrong.

Jason Young said...

It's actually the order that counts, not whether or not it's a Lambda:

http://programminglinq.com/blogs/marcorusso/archive/2008/07/20/use-of-distinct-and-orderby-in-linq.aspx

Unknown said...

Ah! Now I get it, thanks :) Learning that I could dump my generated SQL query as a string from my var variable to see how LINQ converts to SQL was a key step for me too :)

Michael Avinash said...

Thanks. Found this helpful.