LINQ：如何排序依据一个表的SUM和他人的COUNT [英] Linq: How to OrderBy the SUM of one table and the COUNT of another
问题描述
由于每一个 previous问题，我有以下LINQ EX pression。
As per a previous question, I've got the following LINQ expression.
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
Which converts to the following 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]
我现在的问题是，当谈到在订货时一些事件没有任何投票或评论。
My problem now is when it comes to ordering when some events don't have any votes or comments.
下面是个什么EventVotes表看起来像（它的全部）
Here is what the EventVotes table looks like (in it's entirety)
| UserID | EventID | Vote |
| 1 | 51 | 1 |
| 1 | 52 | 1 |
| 2 | 52 | 1 |
| 1 | 53 | 1 |
| 2 | 53 | -1 |
| 3 | 53 | -1 |
comments表完全是空的，所以因为我们只是做了计数就可以了，我们可以假设，一切都回到零。
The Comments table is completely empty, so since we're just doing a Count on it, we can assume that everything comes back Null.
现在，当我运行上面的查询时，结果顺序如下
Now when I run the query above, the result order is as follows
52
51
53
1
2
3
52
51
53
1
2
3
当它应该
52
51
1
2
3
53
52
51
1
2
3
53
，因为事件数53具有的表决计数-1，而事件号1，2，和3具有的表决计数0
because event number 53 has a vote count of "-1" while event numbers 1, 2, and 3 have a vote count of "0"
谁能帮助找出如何提升LINQ的EX pression考虑到还没有被评选上的事件？
Can anyone help figure out how to enhance the Linq expression to account for events that haven't been voted on?
下面是截图
好了，我已经简化到这个查询LinqPad，其结果是，我只得到三个结果。
Ok, so I've simplified the query in LinqPad to this, and the result is that I only get three results.
Events.OrderByDescending(Function(e) (((e.EventVotes.Sum(Function(s) s.Vote)) * 2) + (e.Comments.Count)))
这是什么告诉我的是，排序依据是只有抓住这三个结果（51，52，53），然后将其追加订单条款后的结果的其余部分。我需要找出一种方法，包括对拉姆达EX pression的空的结果的其余部分。
What this is telling me is that the orderby is only grabbing those three results (51, 52, 53), then it appends the rest of the results AFTER the order clause. I need to figure out a way to include the rest of the "null" results in the Lambda expression.
推荐答案
下面是一个C＃的解决方案。我试图把它翻译成VB的，但我的VB技能（和欲望去学，老老实实）是零，我放弃了。我会尽量突出的关键点，以帮助翻译。
Here's a C# solution. I tried to translate it into VB, but my VB skills (and desire to learn it, honestly) are nil, and I gave up. I'll try to highlight the critical points to help with the translation.
下面是完整的语句：
context.Events.OrderByDescending(e => (((e.EventVotes.Sum(s => (int?)s.Vote) ?? 0) * 2) + e.Comments.Count))
什么是关键的是， s.Vote 首先要转换成空整型让和（）将是一个空整型。可空的结果，您可以明确地检查空和使用零计算的其余部分，如果结果确实是空。
What's critical is that s.Vote is first cast to a nullable integer so that the result of Sum() will be a nullable integer. A nullable result allows you to explicitly check for null and use zero for the rest of calculation if the result was indeed null.
有关VB翻译的目的， ?? 是一个C＃空聚结算哪里（例如） myNullableIntVar？ 0 的计算结果为myNullableIntVar的价值，或者如果它为空，则0。
For VB translation purposes, ?? is a C# null-coalesce operator where (for example) myNullableIntVar ?? 0 evaluates to the value of myNullableIntVar, or if it is null, then 0.
下面是由以上语句生成的SQL，FWIW：
Here's the SQL generated by the above statement, 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，整型？），但它抛出一个错误，指出整数不能转换为整数？，所以我不得不采取这种方式。
Edit by rockinthesixstring
I've added the VB.NET version below - This works exactly as expected. I tried to do a DirectCast(s.Vote, Integer?) but it threw an error saying that Integer cannot be converted to Integer?, So I had to take this approach.
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查询
Which resulted in this SQL Query
-- 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
这篇关于LINQ：如何排序依据一个表的SUM和他人的COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
