Linq order by 没有订购任何东西

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

无论我在哪里按查询排序，它似乎只是随机排序。

编辑：结果与末尾的顺序相同：

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

编辑：生成的 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

我已经通过数据库运行了代码，它正确返回了结果，所以它一定是其他奇怪的东西，因为代码是如此简单，不过没关系，谢谢感谢您的帮助，我从这个问题中学到了很多新东西！

Answer 1

这是 LINQ to SQL 吗？我怀疑问题在于您在分组之前订购。你可以尝试这个：

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

编辑：好的，如果这不起作用，那么可能是 SQL 分组的限制......尽管这看起来很奇怪。

不过，您始终可以强制在客户端执行排序：

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

Answer 2

GroupJoin 很可能会覆盖该顺序。它可能是作为子查询实现的。如果您运行 LINQ to SQL Profiler 或 SQL Profiler 来查看底层查询，它将能够阐明这一点。您是否尝试过将 OrderBy 作为方法链中的最后一个操作？

Answer 3

您是否尝试过将 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);

Answer 4

如果您想对分组的问题进行排序，则需要在创建 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 之后转换为可枚举来消除此问题。

Answer 5

很难测试您的代码，因为我没有您的所有声明，但我怀疑您获得看似随机行为的原因是因为 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);

Answer 6

尝试在 LINQ 查询后添加 ToArray() 。我确实知道 LINQ 遵循惰性求值规则，而 ToArray() 强制求值急切。