Multiple group by with aggregate in Linq

Question

I currently have this code:

foreach (var newsToPolitician in news.NewsToPoliticians)
{
     var politician = newsToPolitician.Politician;
     var votes = (from s in db.Scores
                   where o.IDPolitician == politician.IDPolitician
                         && o.IDNews == IDNews
                   group o by o.IDAtribute
                   into g
                   select new{
                      Atribute= g.Key,
                      TotalScore= g.Sum(x => x.Score)
                   }).ToList();
}

It works alright, but I want to avoid making multiple queries to my database in foreach loop. My table Scores looks like this:

IDScore | IDNews | IDUser | IDPolitician | IDAtribute | Score
1         40       1010     35             1             1
2         40       1010     35             2            -1
3         40       1002     35             1             1
4         40       1002     35             2             1
5         40       1002     40             1            -1
...

My goal is to aggregate all the scores for all politicians in a news. A news can have up to 7 politicians.
Is it expensive to call my database up to seven times in a foreach loop. I know that isn't best practice so I'm interested is there any way to avoid it in this particular case and make one call to database and then process it on the server side?

Answer 1

Update - Due to user comments have re-jigged to try and ensure aggregation on the server. In this case we can group on the server by both IDPolitician and IDAttribute and then pull the groups in with ToLookup locally as so:

var result = db.Scores.Where(s => s.IDNews == IDNews)
                      .Where(s => news.NewsToPoliticians
                                      .Select(n => n.Politician.IDPolitician)
                                      .Contains(s.IDPolitician))
                      .GroupBy(s => new
                                    {
                                      s.IDPolitician,
                                      s.IDAttribute
                                    },
                                (k,g ) => new
                                          {
                                           k.IDPolitician,
                                           k.IDAttribute,
                                           Sum = g.Sum(x => x.Score)
                                           })
                      .ToLookup(anon => anon.IDPolitician,
                                anon => new { anon.IDAttribute, anon.Sum })

Legacy - You want to use GroupJoin here, it would be something along the lines of:

var result = news.NewsToPoliticians
                 .GroupJoin( db.Scores.Where(s= > s.IDNews == IDNews),
                             p => p.IDPolitician,
                             s => s.IDPolitician,
                             (k,g) => new
                                      {
                                        PoliticianId = k,
                                        GroupedVotes = g.GroupBy(s => s.IDAtribute,
                                                                 (id, group) => new
                                                                                {
                                                                                 Atribute = id,
                                                                                 TotalScore = group.Sum(x => x.Score)
                                                                                 })
                                      })
                 .ToList();

However you are at the mercy of your provider as to how it translates this so it might still be multiple queries to get round this you could use something like:

var politicianIds = news.NewsToPoliticians.Select(p => p.IDPolitician).ToList()
var result = db.Scores.Where(s= > s.IDNews == IDNews)
                      .Where(s => politicianIds.Contains(s.IDPolitician))
                      .GroupBy(p => p.IDPolitician,
                              (k,g) => new
                                       {
                                        PoliticianId = k,
                                        GroupedVotes = g.GroupBy(s => s.IDAtribute,
                                                                 (id, group) => new
                                                                                {
                                                                                 Atribute = id,
                                                                                 TotalScore = group.Sum(x => x.Score)
                                                                                 })
                                       })
                      .ToList();

Which hopefully should be at most 2 query (depending on whether NewsToPoliticians is db dependent). You'll just have to try it out and see.

Answer 2

Use a stored procedure and get the SQL server engine to do all the work. You can still use Linq to call the stored procedure and this will minimize all the calls to the database