NotSupportedException when using compiled lambda expression for Average

Question

I tried to answer this question but failed:

So let's take the original query:

var result = db.Employees.GroupBy(x => x.Region)
               .Select(g => new { Region = g.Key, 
                                  Avg = g.Average(x => x.BaseSalary)});

Works fine. Now we want to dynamically decide what to average. I try to create the lambda for Average dynamically:

string property = "BaseSalary";
var parameter = Expression.Parameter(typeof(Employee));
var propAccess = Expression.PropertyOrField(parameter, property);
var expression = (Expression<Func<Employee,int?>>)Expression.Lambda(propAccess, parameter);
var lambda = expression.Compile();

and use it:

var result = db.Employees.GroupBy(x => x.Region)
               .Select(g => new { Region = g.Key, 
                                  Avg = g.Average(lambda)});

With Linq2Sql this results in a NotSupportedException:

Für den Abfrageoperator "Average" wurde eine nicht unterstützte Überladung verwendet.

(I only have the German error message, it says that the used overload of Average is not supported, feel free to edit if you have the English version).

The original question used Linq2Entities and got the error

Internal .NET Framework Data Provider error 102

IntelliSense (or some other IDE feature) tells me that in both versions the compiler chooses the same overload of Average:

double? Enumerable.Average(this IEnumerable<Employee> source, Func<Employee, int?> selector);

And I rechecked with an ExpressionVisitor that my lambda is exactly the same expression as x => x.BaseSalary.

So: Why it suddenly isn't supported anymore?

---

Interesting: there is no such exception if I don't group and use it simply like:

double? result = db.Employees.Average(lambda);

---

With YuvalShap's answer I also tried Avg = g.AsQueryable().Average(expression) (using an expression instead of the lambda), but with the same result.

Answer 1

You should not compile the lambda. EF works with expression trees not with the compiled code, so that it can transform the Expression to SQL rather then running it in code.

There is no compilation error because there is an Enumerable.Average which does take a Func<T, int?> so that overload is used. But when converting to SQL EF does not know what to do with the compiled lambda.

Since Average is on the grouping, you can't pass the expression to it, you have to build up the entire expression to Select.

Since that can create very obfuscated code, you can create a custom version of Select that replaces a portion of the expression with your custom expression for average, so at least the main part of the select is readable:

public static class Helper
{
    public static IQueryable<TResult> SelectWithReplace<T, TKey, TResult>(this  IQueryable<IGrouping<TKey, T>> queryable, Expression<Func<IGrouping<TKey, T>, Func<T, int?>, TResult>> select, Expression<Func<T, int?>> replaceWith)
    {
        var paramToReplace = select.Parameters[1];
        var newBody = new ReplaceVisitor(paramToReplace, replaceWith).Visit(select.Body);

        var newSelect = Expression.Lambda<Func<IGrouping<TKey, T>, TResult>>(newBody, new[] { select.Parameters.First() });
        return queryable.Select(newSelect);
    }

    public class ReplaceVisitor : ExpressionVisitor
    {
        private readonly ParameterExpression toReplace;
        private readonly Expression replaceWith;

        public ReplaceVisitor(ParameterExpression toReplace, Expression replaceWith)
        {
            this.toReplace = toReplace;
            this.replaceWith = replaceWith;
        }
        protected override Expression VisitParameter(ParameterExpression node)
        {
            if(node == toReplace)
            {
                return this.replaceWith;
            }
            return base.VisitParameter(node);
        }
    }
}

Usage:

string property = "BaseSalary";
var parameter = Expression.Parameter(typeof(Employee));
var propAccess = Expression.PropertyOrField(parameter, property);
var expression = (Expression<Func<Employee, int?>>)Expression.Lambda(propAccess, parameter);
var result = db.Employees
    .GroupBy(x => x.Region)
    .SelectWithReplace((g, willReplace) => new
    {
        Region = g.Key,
        Avg = g.Average(willReplace)
    }, expression);