Linq：如何按一个表的 SUM 和另一个表的 COUNT 进行排序

Question

根据上一个问题，我得到了以下 LINQ 表达式。

Events.Where(Function(e) e.EventDate >= Date.Today) _
            .OrderByDescending(Function(e) (((e.EventVotes.Sum(Function(s) s.Vote)) * 2) + (e.Comments.Count))) _
            .Skip(0) _
            .Take(5)

它将转换为以下 SQL

-- Region Parameters
DECLARE @p0 DateTime2 = '2011-01-17 00:00:00.0000000'
DECLARE @p1 Int = 2
DECLARE @p2 Int = 0
DECLARE @p3 Int = 5
-- EndRegion
SELECT [t3].[ID], [t3].[UserID], [t3].[RegionID], [t3].[LocationID], [t3].[Title], [t3].[Description], [t3].[EventDate], [t3].[URL], [t3].[Phone], [t3].[TicketPriceLow], [t3].[TicketPriceHigh], [t3].[DatePosted], [t3].[isHighlighted]
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY (((
        SELECT SUM([t1].[Vote])
        FROM [dbo].[EventVotes] AS [t1]
        WHERE [t1].[EventID] = [t0].[ID]
        )) * @p1) + ((
        SELECT COUNT(*)
        FROM [dbo].[Comments] AS [t2]
        WHERE [t2].[EventID] = [t0].[ID]
        )) DESC) AS [ROW_NUMBER], [t0].[ID], [t0].[UserID], [t0].[RegionID], [t0].[LocationID], [t0].[Title], [t0].[Description], [t0].[EventDate], [t0].[URL], [t0].[Phone], [t0].[TicketPriceLow], [t0].[TicketPriceHigh], [t0].[DatePosted], [t0].[isHighlighted]
    FROM [dbo].[Events] AS [t0]
    WHERE [t0].[EventDate] >= @p0
    ) AS [t3]
WHERE [t3].[ROW_NUMBER] BETWEEN @p2 + 1 AND @p2 + @p3
ORDER BY [t3].[ROW_NUMBER]

我现在的问题是当某些事件没有任何投票或评论时进行排序。

以下是 EventVotes 表的样子（完整）

| UserID | EventID | Vote |    
| 1      | 51      | 1    |   
| 1      | 52      | 1    |   
| 2      | 52      | 1    |   
| 1      | 53      | 1    |   
| 2      | 53      | -1   |   
| 3      | 53      | -1   |

Comments 表完全是空的，因此由于我们只是对其进行 Count 操作，因此我们可以假设所有内容都返回 Null。

现在，当我运行上面的查询时，结果顺序如下

52
51
53
1
2
3

什么时候“应该”

52
51
1
2
3
53

因为事件编号 53 的投票计数为“-1”，而事件编号 1、2 和 3 的投票计数为“0”

任何人都可以帮助弄清楚如何增强 Linq 表达式以解释尚未发生的事件被投票通过？

这是一个屏幕截图

编辑：

好的，所以我将 LinqPad 中的查询简化为这样，结果是我只得到三个结果。

Events.OrderByDescending(Function(e) (((e.EventVotes.Sum(Function(s) s.Vote)) * 2) + (e.Comments.Count)))

这告诉我的是，orderby 仅获取这三个结果（51、52、53），然后将其余结果附加在 order 子句之后。我需要找到一种方法将其余的“null”结果包含在 Lambda 表达式中。

Answer 1

这是一个 C# 解决方案。我试图将其翻译成 VB，但我的 VB 技能（老实说，也没有学习它的愿望）为零，所以我放弃了。我将尽力强调关键点以帮助翻译。

完整的语句如下：

context.Events.OrderByDescending(e => (((e.EventVotes.Sum(s => (int?)s.Vote) ?? 0) * 2) + e.Comments.Count))

关键是 s.Vote 首先被转换为一个可为空的整数，以便 Sum() 的结果将是一个可为空的整数。可空结果允许您显式检查空值，并在结果确实为空时使用零进行其余计算。

出于 VB 转换目的，?? 是一个 C# 空合并运算符，其中（例如）myNullableIntVar ?? 0 计算为 myNullableIntVar 的值，或者如果它为 null，则为 0。

这是由上述语句生成的 SQL，FWIW：

exec sp_executesql N'SELECT [t0].[EventID], [t0].[Name]
FROM [dbo].[Events] AS [t0]
ORDER BY ((COALESCE((
    SELECT SUM([t2].[value])
    FROM (
        SELECT [t1].[Vote] AS [value], [t1].[EventID]
        FROM [dbo].[EventVotes] AS [t1]
        ) AS [t2]
    WHERE [t2].[EventID] = [t0].[EventID]
    ),@p0)) * @p1) + ((
    SELECT COUNT(*)
    FROM [dbo].[Comments] AS [t3]
    WHERE [t3].[EventID] = [t0].[EventID]
    )) DESC',N'@p0 int,@p1 int',@p0=0,@p1=2

---

由 rockinthesixstring 编辑

我在下面添加了 VB.NET 版本 - 其工作原理与预期的。我尝试执行 DirectCast(s.Vote, Integer?) 但它抛出一个错误，指出 Integer 无法转换为 Integer?，所以我不得不采取这种方法。

Events.OrderByDescending(Function(e) (((If(e.EventVotes.Sum(Function(s) s.Vote),
                                           e.EventVotes.Sum(Function(s) s.Vote),
                                           0)) * 2) + e.Comments.Count))

这导致了这个 SQL 查询

-- Region Parameters
DECLARE @p0 Int = 0
DECLARE @p1 Int = 2
-- EndRegion
SELECT [t0].[ID], [t0].[UserID], [t0].[RegionID], [t0].[LocationID], [t0].[Title], [t0].[Description], [t0].[EventDate], [t0].[URL], [t0].[Phone], [t0].[TicketPriceLow], [t0].[TicketPriceHigh], [t0].[DatePosted], [t0].[isHighlighted]
FROM [dbo].[Events] AS [t0]
ORDER BY ((
    (CASE 
        WHEN (CONVERT(Bit,(
            SELECT SUM([t1].[Vote])
            FROM [dbo].[EventVotes] AS [t1]
            WHERE [t1].[EventID] = [t0].[ID]
            ))) = 1 THEN (
            SELECT SUM([t2].[Vote])
            FROM [dbo].[EventVotes] AS [t2]
            WHERE [t2].[EventID] = [t0].[ID]
            )
        ELSE @p0
     END)) * @p1) + ((
    SELECT COUNT(*)
    FROM [dbo].[Comments] AS [t3]
    WHERE [t3].[EventID] = [t0].[ID]
    )) DESC