TYRONEMICHAEL TYRONEMICHAEL - 3 months ago 17
C# Question

Entity Framework multiple counts with a single query

Sorry if this has been asked, but how can I improve the following with a single call to the database?

var statsModel = new
{
Total = _db.Messages.Count(),
Approved = _db.Messages.Count(x => x.Approved),
Rejected = _db.Messages.Count(x => !x.Approved),
};

Answer

First of all you can compute the Rejected by Total and Accepted like this:

Rejected = Total - Approved

And for further improvement you can compute both of them in one shot;

from m in _db.Messages
let Total =  _db.Messages.Count()
let Accept = _db.Messages.Count(x => x.Approved == true)
select new {Total , Accept})

UPDATE: a simple hack for now : just take the first row

(from m in _db.Messages
let Total =  _db.Messages.Count()
let Accept = _db.Messages.Count(x => x.Approved == true)
select new {Total , Accept}).Take(1);

But I'm looking for a cleaner one