Linq query for two tables using SUM and Group By

Question

I have two tables

Replies

|ReplyID     |ReplyText | QuestionID | CreatedOn  |
|1           |xxxxxx    |1           | 01/01/2016 |
|2           |yyyyyy    |2           | 02/01/2016 |
|3           |zzzzzz    |3           | 03/01/2015 |

and Votes

|ReplyID     |VoteValue |UserId   |
|1           |1         |1        |
|1           |1         |2        |
|2           |-1        |3        |
|3           |1         |4        |

I m trying to join two tables on ReplyIds against id value which I m getting as a controller parameter.

This is the linq query I m using:

Replies = from r in db.Replies
          join v in db.Votes on r.ReplyID equals v.ReplyId
          where r.QuestionId == id
          orderby r.CreatedOn descending
          group v by v.ReplyId into g
          select g.Key

where Replies is

public IGrouping<Reply, Vote> Replies { get; set; }

I m having this error:

Cannot implicitly convert type 'System.Linq.IQueryable' to System.Linq.IGrouping'

I m unable to proceed from here. I need to Sum(VoteValues) in Votes table and join it with Replies table to get all column values. Any help would be appreciated.

Answer 1

You can use a group join instead:

var result= from r in db.Replies
            join v in db.Votes on r.ReplyID equals v.ReplyId into votes
            where r.QuestionId == id
            orderby r.CreatedOn descending
            select new {Reply=r, Votes=votes.Sum(v=>v.VoteValue)};

According what you describe you need to get all columns from Reply and the sum of all votes. This way you can project your query using an anonymous to save the Reply instance and the sum that you need. Now, maybe you need to save the projection in a known type because you need to pass the result of this query to a controller. In that case I suggest create a custom class (also known as DTO):

public class ReplySum
{ 
  public Reply Reply {get;set;}
  public int Votes{get;set;}
}

And now you can project your query this way:

IQueryable<ReplySum> query =from r in db.Replies
                            join v in db.Votes on r.ReplyID equals v.ReplyId into votes
                            where r.QuestionId == id
                            orderby r.CreatedOn descending
                            select new ReplySum{Reply=r, Votes=votes.Sum(v=>v.VoteValue)};

Another thing you need to consider is materialize your query before pass the result to your view:

var result=query.ToList();//Or ToArray

Answer 2

Your query can be simpler by using lambda expression:

var result = db.Replies.Select(i => new
                    {
                        VoteCount = i.Votes.Sum(j => j.VoteValue),
                        Reply = i
                    })
                    .ToList();