Entity Framework multiple counts with a single query

Question

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 1

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