LinQ query doesn't order

Question

I have a strange issue. The code below, once executed, does not use any form of ORDER BY in the sql statement, and so it does not return the intented records.

var last5CommesseRisorsaPjId = db.Attivita .Where(a => a.IdRisorsa == loggedUser.Id) .OrderByDescending(a => a.Data) //this looks ignored .Select(a => a.FasiCommessa.Commesse.AxProjId) .Distinct() .Take(5) .ToList();

SQL: (using db.Database.Log):

SELECT [Limit1].[AxProjId] AS [AxProjId] FROM ( SELECT DISTINCT TOP (5) [Extent3].[AxProjId] AS [AxProjId] FROM [dbo].[Attivita] AS [Extent1] LEFT OUTER JOIN [dbo].[FasiCommessa] AS [Extent2] ON [Extent1].[IdFaseCommessa] = [Extent2].[Id] LEFT OUTER JOIN [dbo].[Commesse] AS [Extent3] ON [Extent2].[IdCommessa] = [Extent3].[Id] WHERE [Extent1].[IdRisorsa] = 'ecaffee0-5aeb-45cf-8d40-218ff63a2108' ) AS [Limit1]

I would expect an ORDER BY [Extent1].[Data] DESC after the WHERE clause.

What's wrong?

Dhaval Patel · Accepted Answer · 2014-07-01 09:16:33Z

2

The distinct is probably messing up the order by try calling the orderBy after the distinct()

 var last5CommesseRisorsaPjId = db.Attivita .Where(a => a.IdRisorsa == loggedUser.Id) .Distinct() .OrderByDescending(a => a.Data) //this looks ignored .Select(a => a.FasiCommessa.Commesse.AxProjId) .Take(5) .ToList();

answered Jul 1, 2014 at 9:16

Dhaval Patel

7,6116 gold badges43 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

qwertoyo Over a year ago

But the Distinct just after the where would apply on the whole record, I need the Distinct on a => a.FasiCommessa.Commesse.AxProjId

Dhaval Patel Over a year ago

you can use the select query before the distinct

qwertoyo Over a year ago

but if I use the select before the distinct, it will be before the OrderBy too, so I won't be able to order by something I didn't include in the Select clause

Nadendla · Accepted Answer · 2014-07-01 09:17:51Z

1

Call Distinict First and then OrderBy

answered Jul 1, 2014 at 9:17

Nadendla

7222 gold badges7 silver badges18 bronze badges

1 Comment

qwertoyo Over a year ago

As written in the above answer's comments, the distinct would not work as intended if I move the distinct before the orderby (it will be before the select too)

qwertoyo · Accepted Answer · 2014-07-01 09:50:08Z

Solved rewriting the query as below:

var last5CommesseRisorsaPjId = db.Attivita .Where(a => a.IdRisorsa == loggedUser.Id) .GroupBy(a => a.FasiCommessa.Commesse.AxProjId) .Select(g => new { AxProjId = g.Key, MaxData = g.Max(a => a.Data) }) .OrderByDescending(c => c.MaxData) .Select(c => c.AxProjId) .Take(5) .ToList();

Collectives™ on Stack Overflow

LinQ query doesn't order

3 Answers 3

3 Comments

1 Comment

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

1 Comment

Comments

Related