1

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
  • Can you provide more details on what you have tried so far? There is a small library that allows you to plug in an arbitrary expression into a query. I can't remember its name right now. – usr Jan 31 '15 at 08:26
  • @usr: This is my problem. I don't know how to approach, where to start. Google is full of predicated builders for the where clause, it's easy no doubts. But it seems that nobody tries to write a selector. – abatishchev Feb 02 '15 at 19:30

1 Answers1

1

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.

abatishchev
  • 98,240
  • 88
  • 296
  • 433
usr
  • 168,620
  • 35
  • 240
  • 369
  • Hm, are you sure I need to call `Complle()()`? I'm getting an error: Unable to cast object of type `System.Linq.Expressions.InstanceMethodCallExpressionN` to type `System.Linq.Expressions.LambdaExpression`. – abatishchev Feb 02 '15 at 21:01
  • These didn't help much: http://stackoverflow.com/questions/2564975/linqkit-system-invalidcastexception-when-invoking-method-provided-expression-on http://stackoverflow.com/questions/23629442/trying-to-use-parent-property-as-parameter-in-child-collection-expression-linqk or maybe I'm just overlooking. – abatishchev Feb 02 '15 at 21:02
  • Hm. It works just fine for `IQueryable` but not for `IGroupping`. Will figure out, I hope) – abatishchev Feb 02 '15 at 21:07
  • Well, if there is any problem it can only be with the AsExpandable code. The expression that you hand to EF should be indistinguishable from manually written code. Maybe you need to dig into AsExpandable. I don't think it does anything really complicated. It should just "paste" in the expression into the tree. Converting a.Compile().Invoke(x) to a(x) basically. – usr Feb 02 '15 at 21:12