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.

might want to look here msdn.microsoft.com/en-us/vcsharp/aa336747#countGrouped — Conrad Frix
– Conrad Frix, Commented Apr 8, 2011 at 21:13

Jimmy · Accepted Answer · 2011-04-11 14:47:09Z

5

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)

edited Apr 11, 2011 at 14:47

answered Apr 8, 2011 at 21:12

Jimmy

9,82514 gold badges62 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kon Over a year ago

Looks promising, but kvp.Values does not exist.

Kon Over a year ago

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 Over a year ago

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.

Bobby Borszich · Accepted Answer · 2011-04-08 21:41:56Z

-1

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.

answered Apr 8, 2011 at 21:41

Bobby Borszich

11.8k9 gold badges41 silver badges35 bronze badges

Collectives™ on Stack Overflow

LINQ to Entity query optimization

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related