Multiple aggregate functions in one query using Linq to SQL [duplicate]

Question

I'm trying to achieve the following in LINQ (EF6):

SELECT count(A), sum(B), average(C)
FROM TableA,
LEFT JOIN TableB ON ...
LEFT JOIN TableC ON ...
WHERE
    (very complicated conditions)

The C# code looks like following:

IQueryable<Entity> = dbSet
    .Include(e => e.entityB)
    .Include(e => e.EntityC)
    .Where( <very complicated conditions> );

How can I apply multiple aggregate functions on different fields? Specifically, in a way, which won't cause the complicated conditions to be copied over and over in resulting query?

please specify a column name sum(B) to sum(b.Qty). same for average(C) — sebu
– sebu, Commented Jul 17, 2017 at 12:19
You can check this link: stackoverflow.com/questions/3414080/… — sebu
– sebu, Commented Jul 17, 2017 at 12:36

Ivan Stoev · Accepted Answer · 2017-07-17 12:21:35Z

11

You can use the group by constant trick to get all the aggregates with single SQL query (and shared complicated filter):

var result = dbSet
    .Where( <very complicated conditions> )
    .GroupBy(e => 1) // aribitrary value
    .Select(g => new
    {
        CountA = g.Count(),
        SumB = g.Sum(e => e.EntityB.PropertyB),
        AverageC = g.Average(e => e.EntityC.PropertyC),
    })
    .FirstOrDefault();

answered Jul 17, 2017 at 12:21

Ivan Stoev

208k17 gold badges356 silver badges376 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3259797 Over a year ago

It's probably easier for people to grasp using bool instead of int. In other words, "collection.GroupBy(=>true).Select(all=>new ( Sum=all.Sum(), Avg = all.Average(). })

Collectives™ on Stack Overflow

Multiple aggregate functions in one query using Linq to SQL [duplicate]

1 Answer 1

1 Comment

Hot Network Questions