CODEWITHSUNDEEP
c#linqlambdasql-order-by
Tom Gullen
Tom Gullen

Reputation: 61755

Linq order by isn't ordering anything

// 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!

Upvotes: 3

Views: 847

Answers (6)

Rupal
Rupal

Reputation: 69

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

Upvotes: 2

Reed Copsey
Reed Copsey

Reputation: 564641

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.

Upvotes: 3

Pete M
Pete M

Reputation: 2048

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);

Upvotes: 3

Jon Skeet
Jon Skeet

Reputation: 1502106

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);

Upvotes: 4

Timwi
Timwi

Reputation: 66584

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);

Upvotes: 2

Jim Bolla
Jim Bolla

Reputation: 8295

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?

Upvotes: 3

Related Questions