Combining LINQ-to-Entities Queries into Single Query

Question

I have an Entity Framework entity Provider, with a list of rating votes for that provider. My current queries look something like this:

int previousVote = provider.ProviderRankings.FirstOrDefault(r => r.UserId == CurrUserId);
double averageVote = provider.ProviderRankings.Average(r => r.Rating);
int totalVotes = provider.ProviderRankings.Count();

This seems functionally correct. However, I believe this will result in three additional trips to the database. Is there anyway to have these requests combined into a single query such that only one SQL query will be sent, and all results can be returned with only one additional trip to the server?

@SamLeach: I know they are. That's why I asked if there was any way to merge them into one round trip to the server. This is possible in pure SQL. — Jonathan Wood, May 29 '13 at 01:14
perhaps a dupe of [Can I easily evaluate many IQueryables in a single database call using Entity Framework?](http://stackoverflow.com/questions/8880310), do you agree? — AakashM, May 29 '13 at 08:04

score 4 · Accepted Answer · edited May 23 '17 at 11:50

You could combine the two aggregates fairly easily using a Group By:

Multiple SQL aggregate functions in a single Linq-to-Entities query

I am pretty sure the FirstOrDefault will work if you choose a suitably vague key for the grouping (for example key = 0) i.e:

from t in ProviderRankings
  group t by key = 0
  into g
  select new {
     previousVote  = g.FirstOrDefault(r => r.UserId == CurrUserId),
     totalVotes = g.Count(),
     averageVote = g.Average(x => x.Rating)
  }

Combining LINQ-to-Entities Queries into Single Query

1 Answers1