CODEWITHSUNDEEP
c#sqllinqentity-framework
Jonathan Wood
Jonathan Wood

Reputation: 67195

Combining LINQ-to-Entities Queries into Single Query

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?

Upvotes: 3

Views: 1116

Answers (1)

Squid
Squid

Reputation: 4810

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)
  }

Upvotes: 4

Related Questions