Geek asks and answers

Linq order doesn't order 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 place the order on request, it just arranges them randomly.

Edit: same results with order at the 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 run the code through the database and it returns the results correctly, so it should be something else that is strange, because the code is so simple, it doesn’t matter, though, thanks for your help, I learned a lot of new things with this problem!

+3
source share
6 answers

Is this LINQ to SQL? I suspect the problem is what you order before grouping. You can try the following:

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: Well, if that doesn't work, maybe this is a limitation of SQL grouping ... although it seems rather strange.

, :

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

OrderBy , ;? SQL, , , ... , SQL, .

ToList(), , :

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

GroupJoin, , . , . LINQ to SQL Profiler SQL-, , . OrderBy ?

+3

, 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 , . GroupJoin .

. , LINQ to Objects, GroupJoin.

+3

, , , , , , 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() }
    )
    .OrderBy(s => s.Question.awaitingUserResponse)
    .ThenBy(s => s.Question.dateSubmitted);
+2

ToArray() LINQ. , LINQ , ToArray() .

+2

More articles:


All Articles