Linq order by isn't ordering anything

Question

// Query all records
var q =
dc.tblHelpCentreQuestions.Where(question => question.userID == UserID).Where(question => question.awaitingUserResponse == true || question.awaitingSupportResponse == true).
OrderBy(s => s.awaitingUserResponse).ThenBy(s => s.dateSubmitted).
GroupJoin(
    dc.tblHelpCentreReplies,
    question => question.ID,
    replies => replies.ticketID,
    (question, replies) => new { Question = question, RepliesCount = replies.Count() }
);

No matter where I put the order by query, it seems to just be ordering them randomly.

Edit: same results with order at end:

// Query all records
var q =
dc.tblHelpCentreQuestions.Where(question => question.userID == UserID).Where(question => question.awaitingUserResponse == true || question.awaitingSupportResponse == true).
GroupJoin(
    dc.tblHelpCentreReplies,
    question => question.ID,
    replies => replies.ticketID,
    (question, replies) => new { Question = question, RepliesCount = replies.Count() }
).OrderBy(s => s.Question.awaitingUserResponse).ThenBy(s => s.Question.dateSubmitted);

Edit: Generated SQL

exec sp_executesql N'SELECT [t2].[ID], [t2].[catID], [t2].[subject], [t2].[question], [t2].[userID], [t2].[dateSubmitted], [t2].[isUrgent], [t2].[emailMe], [t2].[awaitingSupportResponse], [t2].[awaitingUserResponse], [t2].[lastReply], [t2].[stopWatchTotalMins], [t2].[isStopWatchOn], [t2].[stopWatchStart], [t2].[priorityLevel], [t2].[value] AS [RepliesCount]
FROM (
    SELECT [t0].[ID], [t0].[catID], [t0].[subject], [t0].[question], [t0].[userID], [t0].[dateSubmitted], [t0].[isUrgent], [t0].[emailMe], [t0].[awaitingSupportResponse], [t0].[awaitingUserResponse], [t0].[lastReply], [t0].[stopWatchTotalMins], [t0].[isStopWatchOn], [t0].[stopWatchStart], [t0].[priorityLevel], (
        SELECT COUNT(*)
        FROM [dbo].[tblHelpCentreReplies] AS [t1]
        WHERE ([t0].[ID]) = [t1].[ticketID]
        ) AS [value]
    FROM [dbo].[tblHelpCentreQuestions] AS [t0]
    ) AS [t2]
WHERE (([t2].[awaitingUserResponse] = @p0) OR ([t2].[awaitingSupportResponse] = @p1)) AND ([t2].[userID] = @p2)
ORDER BY [t2].[awaitingUserResponse], [t2].[dateSubmitted]', N'@p0 int,@p1 int,@p2 int', @p0 = 1, @p1 = 1, @p2 = 81

I've run the code through the database and it returns the results correctly, so it must be something else which is strange because the code is so simple, never mind though, thanks all for your help, I've learnt a lot of new things with this problem!

what are you trying to order? You are doing a join after the ordering, so the order is destroyed basically. — BrokenGlass, Apr 04 '11 at 16:39
Have you seen this post http://stackoverflow.com/questions/5408177/linq-order-by-boolean — TheBoyan, Apr 04 '11 at 16:41
Your question does not contain enough information to answer it. Based alone on the code you posted, the results should be ordered. Maybe you are doing something wrong when iterating over these results (since you’ve only shown us the query, not how you use it). — Timwi, Apr 04 '11 at 16:49
@Timwi it's really simple code, it just dumps the results in an array afterwards then binds it to a repeater. — Tom Gullen, Apr 04 '11 at 16:51

Jon Skeet · Accepted Answer · 2011-04-04T16:49:03.063

4

Is this LINQ to SQL? I suspect the problem is that you're ordering before the grouping. You could try this:

var q = dc.tblHelpCentreQuestions
          .Where(question => question.userID == UserID)
          .Where(question => question.awaitingUserResponse
                          || question.awaitingSupportResponse)
          .GroupJoin(dc.tblHelpCentreReplies,
                     question => question.ID,
                     replies => replies.ticketID,
                     (question, replies) => new { Question = question,
                                                RepliesCount = replies.Count() })
          .OrderBy(s => s.Question.awaitingUserResponse)
          .ThenBy(s => s.Question.dateSubmitted);

EDIT: Okay, if this isn't working then perhaps it's a limitation of SQL grouping... although that seems pretty odd.

You could always force the ordering to be performed at the client side instead though:

var q = dc.tblHelpCentreQuestions
          .Where(question => question.userID == UserID)
          .Where(question => question.awaitingUserResponse
                          || question.awaitingSupportResponse)
          .GroupJoin(dc.tblHelpCentreReplies,
                     question => question.ID,
                     replies => replies.ticketID,
                     (question, replies) => new { Question = question,
                                                RepliesCount = replies.Count() })
          // Force the rest of the query to execute in .NET code (Enumerable.XXX)
          .AsEnumerable()
          .OrderBy(s => s.Question.awaitingUserResponse)
          .ThenBy(s => s.Question.dateSubmitted);

edited Apr 04 '11 at 16:49

answered Apr 04 '11 at 16:42

Jon Skeet

1,421,763
867
9,128
9,194

Thanks for answering, same result though :S – Tom Gullen Apr 04 '11 at 16:44
According to [this](http://stackoverflow.com/questions/204505/preserving-order-with-linq/204777#204777) and [this](http://msdn.microsoft.com/en-us/library/bb534297.aspx), `GroupJoin` preserves ordering – BlueRaja - Danny Pflughoeft Apr 04 '11 at 16:45
1

@BlueRaja: That's LINQ to Objects, which is an entirely different kettle of fish. – Jon Skeet Apr 04 '11 at 16:47
1

@Tom: I've edited with a suggestion. I'm still surprised it's not working though... what does the SQL look like? – Jon Skeet Apr 04 '11 at 16:51
@BlueRaja: while [Enumerable.GroupJoin](http://msdn.microsoft.com/en-us/library/bb534297.aspx) does make guarantees about order, no such guarantees are documented for [Queryable.GroupJoin](http://msdn.microsoft.com/en-us/library/bb534805.aspx). – R. Martinho Fernandes Apr 04 '11 at 16:51
@BlueRaja - I'm guessing it's L2S based on the presence of a DataContext. I don't think that would hold true in Linq to SQL where the OrderBy has to be translated and executed server side. SQL Server is inherently flippant about order in general unless that's the last operation executed. – Pete M Apr 04 '11 at 16:51
Also, X2 on the curiosity regarding the actual TSQL being generated here. L2S is making a mistake in translating the query if moving the order operations to the end of the expression chain failed. – Pete M Apr 04 '11 at 17:03
Back home from work at the moment, I'll try and get the SQL tommorow to see what's going on – Tom Gullen Apr 04 '11 at 17:25
@Pete I've included the SQL that was generated by viewing SQL server profiler – Tom Gullen Apr 05 '11 at 08:32
I'm a moron, sorry for wasting all your time but I've learnt a lot from all this anyway – Tom Gullen Apr 05 '11 at 08:37

Pete M · Answer 2 · 2011-04-04T16:53:56.017

Have you tried moving the OrderBy to the very last operation, just before the ;? Like SQL, I wouldn't expect to receive an ordered set if I performed ANY operations on it after I did the ordering... I'd be curious to see what the actual SQL generated is when presented in that order.

Edit for code exploring using ToList() to force execution, then do the ordering clientside:

var q = dc.tblHelpCentreQuestions
    .Where(question => question.userID == UserID)
    .Where(question => question.awaitingUserResponse == true || question.awaitingSupportResponse == true)
    .GroupJoin(
        dc.tblHelpCentreReplies,
        question => question.ID,
        replies => replies.ticketID,
        (question, replies) => new { Question = question, RepliesCount = replies.Count() }
    )
    .ToList()
    .OrderBy(s => s.Question.awaitingUserResponse)
    .ThenBy(s => s.Question.dateSubmitted);

Are you going to deal with the results right away? How big of a data set is it? You could call .ToList() to force the query to physically execute and get your in-memory set, then do your ordering "after the fact" as it were. — Pete M, Apr 04 '11 at 16:47

score 3 · Answer 3 · answered Apr 04 '11 at 16:41

3

The GroupJoin is most likely overriding the order. It's likely implemented as a subquery. If you run either LINQ to SQL Profiler or SQL profiler to see the underlying query, it would be able to shed light on this. Have you tried doing OrderBy as the last operation in the method chain?

answered Apr 04 '11 at 16:41

Jim Bolla

8,265
36
54

Thanks for answering, same result though :S – Tom Gullen Apr 04 '11 at 16:45
2

What if you do `GroupJoin(...).AsEnumerable().OrderBy(...).ThenBy(...)` so that the ordering is done in .NET as opposed to the database? This changes `q` from `IQueryable` to `IEnumerable`, but that might ok for the remaining code. Also, sometimes sorting is faster in .NET than SQL. – Jim Bolla Apr 04 '11 at 16:51
Very slight derail to @Jim and @Jon: I see you both went with AsEnumerable() where I reflexively went to ToList(). Are there any negative implications for going the list route that I should be aware of? – Pete M Apr 04 '11 at 17:30
@Jon: I didn't see that. Great Minds Think Alike. – Jim Bolla Apr 04 '11 at 17:43
@Pete: ToList() forces LINQ to create a List which then gets immediated GC'd, whereas AsEnumerable() could potentially be more effecient, although I doubt it would be significant. – Jim Bolla Apr 04 '11 at 17:45

Reed Copsey · Answer 4 · 2011-04-04T16:57:00.233

If you want to order your grouped questions, you need to do that after you create the GroupJoin:

var q =
    dc.tblHelpCentreQuestions.Where(question => question.userID == UserID).Where(question => question.awaitingUserResponse == true || question.awaitingSupportResponse == true).
    GroupJoin(
        dc.tblHelpCentreReplies,
        question => question.ID,
        replies => replies.ticketID,
        (question, replies) => new { Question = question, RepliesCount = replies.Count() }
    ).
    AsEnumerable().
    OrderBy(s => s.Question.awaitingUserResponse).
    ThenBy(s => s.Question.dateSubmitted);

GroupJoin will effectively remove your ordering, since it's taking your ordered collection and grouping it by question. GroupJoin does not preserve the initial ordering of the keys.

Edit: You can eliminate this issue by forcing the ordering to occur in LINQ to Objects, by converting to an enumerable after the GroupJoin.

Thanks for answering, same result though :S – Tom Gullen Apr 04 '11 at 16:45 — Tom Gullen, Apr 04 '11 at 16:45

score 2 · Answer 5 · answered Apr 04 '11 at 16:42

It’s hard to test your code because I don’t have all your declarations, but I suspect that the reason you are getting seemingly random behaviour is because the GroupJoin makes no guarantees of keeping the order intact.

Therefore, you will have to do the ordering after the grouping.

For example:

var q = dc.tblHelpCentreQuestions
    .Where(question => question.userID == UserID)
    .Where(question => question.awaitingUserResponse == true || question.awaitingSupportResponse == true)
    .GroupJoin(
        dc.tblHelpCentreReplies,
        question => question.ID,
        replies => replies.ticketID,
        (question, replies) => new { Question = question, RepliesCount = replies.Count() }
    )
    .OrderBy(s => s.Question.awaitingUserResponse)
    .ThenBy(s => s.Question.dateSubmitted);

Thanks for answering, same result though :S – Tom Gullen Apr 04 '11 at 16:44 — Tom Gullen, Apr 04 '11 at 16:44

score 2 · Answer 6 · answered Apr 04 '11 at 17:32

2

try adding ToArray() after your LINQ query. I do know that LINQ follows lazy evaluation rule, and ToArray() forces evaluation to be eager.

answered Apr 04 '11 at 17:32

Rupal

69
4

Linq order by isn't ordering anything

Edit: same results with order at end:

Edit: Generated SQL

6 Answers6