LINQ to SQL Join and Sum

Question

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

Answer 1

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.

Answer 2

This is what i have now but it's missing the order by the total.

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

Answer 3

Try the following code. Because it was working for me.

                          var credit = (from bm in BulkMessage
                          join sms in SMS on bm.BulkMessageId equals sms.BulkMessageId
                          where bm.ProfileId == pid && bm.IsActive == true
                           group sms by sms.SMSCredit into g

                          select new { SMSCredits = g.Sum(s => s.SMSCredit) }).FirstOrDefault();

                          string totalCredit=credit.SMSCredits.ToString();