score:4

Accepted answer

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

score:3

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

score: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?

score:3

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.

score:2

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

score:2

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


Related Articles