LINQ to SQL的加入和点心 [英] LINQ to SQL Join and Sum

问题描述

任何人都可以LINQ下面的SQL？

我已经在这两个小时，现在和我没有接近得到这比我当我开始。

  SELECT条款ArticleID，SUM（数量）VoteCount 
从ArticleVotes INNER JOIN 
投票ON ArticleVotes.voteId = Votes.id 
 GROUP BY ArticleVotes.articleId 
 ORDER BY VoteCount DESC 
  

表如下：

ArticleVotes：条款ArticleID，VoteId

投票：ID，金额

解决方案

 从articleVote在ArticleVotes 
加入在art.voteId票投票等于vote.id $ b $通过artarticleVote.articleId 
 b组articleVote选择新的
 {
 =条款ArticleID art.ArticleID，
 VoteCount = Votes.Sum（vote.amount）
} 
排序依据VoteCount 
  

我不完全确信排序依据部分，对于这件事，我不知道剩下的要么，我在测试它的位置不是。如果它不能正常工作，不要怪我好吗：）

更新：使用OP的工作：

 从ArticleVotes 
 artVotes加入对artVotes.VoteId在投票票数相等vote.Id 
组artVotes由artVotes.ArticleId为G 
让总金额= g.Sum（p => p.Vote.Amount）
排序依据总金额下降
选择新{=条款ArticleID g.Key，总=总金额} 
  

---

修改固定使用降序排列的排序依据。

由于运给我个理由，使用let运营商，并最终实现它的美丽。你和你的问题让我非常，只是要求它。 +1为。

Can anyone LINQ the following SQL?

I've been on this for two hours now and am no closer to getting this than I was when I started.

SELECT ArticleId, sum(Amount) AS VoteCount
FROM  ArticleVotes INNER JOIN
Votes ON ArticleVotes.voteId = Votes.id
GROUP BY ArticleVotes.articleId
ORDER BY VoteCount DESC

Tables as follows;

ArticleVotes: ArticleId, VoteId

Votes: id, amount

解决方案

from articleVote in ArticleVotes
join vote in Votes on art.voteId equals vote.id
group articleVote by artarticleVote.articleId
select new 
{
ArticleId = art.ArticleID,
VoteCount = Votes.Sum(vote.amount)
}
orderby VoteCount

I'm not entirely sure about the orderby part, for the matter, I'm not sure about the rest either, as I'm not in the position to test it. If it doesn't work, don't blame me please :)

Update: Using the OP's work:

from artVotes in ArticleVotes
join vote in Votes on artVotes.VoteId equals vote.Id
group artVotes by artVotes.ArticleId into g
let totalAmount = g.Sum(p => p.Vote.Amount)
orderby totalAmount descending
select new { ArticleId = g.Key, total = totalAmount}

---

Edit fixed the orderby to use descending ordering.

Thanks to the op giving me reason to use the let operator, and finally realizing its beauty. You helped me immensely with your question, just by asking it. +1 for that.

这篇关于LINQ to SQL的加入和点心的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！