Entity Framework multiple counts with a single query
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();
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();
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