EF multiple aggregate in single query

Question

I want to get count of a set based on different condition:

 var invoices = new AccountingEntities().Transactions
 var c1 = invoices.Count(i=>i.Type = 0);
 var c2 = invoices.Count(i=>i.Type = 1);
 var c3 = invoices.Count(i=>i.Type = 2);

How its possible to call all three queries in one DB round trip to increase performance?

Answer 1

Sure, just wrap up your three counts in a POCO or anonymous type:

using (var invoices = new AccountingEntities())
{
    var c = (from i in invoices.Transactions
             select new 
             {
                 c1 = invoices.Count(i=>i.Type = 0),
                 c2 = invoices.Count(i=>i.Type = 1),
                 c3 = invoices.Count(i=>i.Type = 2)
             }).Single();           
}

Also, dispose your context, as I show.

Answer 2

To aggregate arbitrary subqueries, use a dummy single-row result set from which you nest the desired subqueries. Assuming db represents your DbContext, the code to count invoice types will look like this:

var counts = (
    from unused in db.Invoices
    select new {
        Count1 = db.Invoices.Count(i => i.Type == 0),
        Count2 = db.Invoices.Count(i => i.Type == 1),
        Count3 = db.Invoices.Count(i => i.Type == 2)
    }).First();

If the want to generically get a count of all types, use grouping:

var counts =
    from i in db.Invoices
    group i by i.Type into g
    select new { Type = g.Key, Count = g.Count() };