CODEWITHSUNDEEP
c#linqentity-frameworklinq-to-entitiesaggregate-functions
abatishchev
abatishchev

Reputation: 100248

Expression Tree for Aggregate Function in LINQ to Entities

I need to dynamically substitute an aggregate function on top of a LINQ to Entities query to perform the operation in database and in the same time to do not hard-code what function do I use, e.g. replace Average() with Max() or Min() in a query like this:

IQueryable<Order> orders = ordersRepository.GetAll();
var q = from o in orders
        group o by o.OrderDate into g
        select new
        {
            OrderDate = g.Key,
            AggregatedAmount = g.AsQueryable()
                                .Average(x => x.Amount)
        };

I'm stuck to translate Queryable.Average() into an expression tree that can be put inside the query and would be successfully translated into SQL.

Upvotes: 1

Views: 1630

Answers (1)

usr
usr

Reputation: 171178

g.Average(x => x.Amount) is Queryable.Average(g, x => x.Amount). We want to replace the Queryable.Average part. Like that:

Expression<Func<IQueryable<Order>, Func<Order, double>>> aggregate = ...;

And then use the expression:

AggregatedAmount = aggregate(g, x => x.Amount)

The problem is that you cannot call an expression. So use AsExpandable:

var q = from o in orders.AsExpandable()
...
AggregatedAmount = aggregate.Compile()(g, x => x.Amount)

This inlines aggregate into the query expression so that EF does not even know it was there.

Upvotes: 1

Related Questions