LINQ to Entity query optimization

Question

I currently have the following logic that builds a list of 4 integers, where each integer represents a sum of all votes for a certain item ID (1, 2, 3 or 4):

List<int> totals = new List<int>();
using (RepositoryEntities entityContext = new RepositoryEntities())
{
 totals.Add(entityContext.ItemVotes.Count(v => v.Vote == 1));
 totals.Add(entityContext.ItemVotes.Count(v => v.Vote == 2));
 totals.Add(entityContext.ItemVotes.Count(v => v.Vote == 3));
 totals.Add(entityContext.ItemVotes.Count(v => v.Vote == 4));
}

This works very well, but I'm questioning the efficiency of such querying, because this seems to actually generate and execute 4 separate queries. Ideally I'd want to have a single, efficient query that returns me the 4 sums.

Any ideas?
Thanks in advance.

• Conrad Frix: might want to look here msdn.microsoft.com/en-us/vcsharp/aa336747#countGrouped

Answer 1

You could wrap your logic into one query

totals.AddRange(entityContext.ItemVotes
 .Where(iv => iv.Vote >= 1 && iv.Vote <= 4)
 .GroupBy(iv => iv.Vote)
 .OrderBy(grp => grp.Key)
 .Select(grp => grp.Count());

(This code is untested and could be way off base but just throwing out an idea)

• Kon: Looks promising, but kvp.Values does not exist.
• Kon: Getting close. But if any of the votes have a count of zero, they are not in the list. For example, my original query returns a list: 0, 1, 3, 0. But your list returned is just: 1, 3. Took out Where clause and no change.
• Kon: Well, this doesn't yield the exact result I was looking for, but it's close enough to get someone on the right path. Thanks.

Answer 2

in LINQ the .Count() method forces execution of a query. If you need 4 different totals there is no other way to produce that in one statement.