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

This might help:

var statsModel =(
        from message in _db.Messages
        group message by 1 into g
        select new
        {
            Total = g.Count(),
            Approved =g.Count (x =>x.Approved),
            Rejected =g.Count (x =>!x.Approved)
        }
    ).FirstOrDefault();

Answer 2

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

Answer 3

In C# (rather than LINQ query), async syntax:

var statsModel = await _db.Messages
.GroupBy(m => 1, (g, mm) => new
{
    Total = mm.Count(),
    Approved = mm.Count(m => m.Approved),
    Rejected = mm.Count(m => !m.Approved)
})
.SingleAsync();