将编译的lambda表达式用于平均值时，出现NotSupportedException [英] NotSupportedException when using compiled lambda expression for Average

问题描述

我试图回答此问题，但失败了:

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)});

工作正常.现在我们要动态地决定平均水平.我尝试为Average动态创建lambda:

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();

并使用它:

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

使用Linq2Sql，结果为NotSupportedException:

With Linq2Sql this results in a NotSupportedException:

Fürden Abfrageoperator"Average" wurde eine nichtunterstützteÜberladungverwendet.

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

(我只有德语错误消息，它说不支持使用的Average重载，如果有英文版本，请随时进行编辑).

(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).

最初的问题使用Linq2Entities并得到错误

The original question used Linq2Entities and got the error

内部.NET Framework数据提供程序错误102

Internal .NET Framework Data Provider error 102

IntelliSense(或其他一些IDE功能)告诉我，在两个版本中，编译器都选择>的相同重载:

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);

然后我用ExpressionVisitor重新检查了我的lambda与x => x.BaseSalary完全相同表达式.

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);

---

使用 YuvalShap的答案我也尝试了Avg = g.AsQueryable().Average(expression)(使用表达式而不是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.

推荐答案

您不应该编译lambda. EF使用表达式树而不是编译代码，因此EF可以将Expression转换为SQL，而不是在代码中运行.

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.

没有编译错误，因为有一个Enumerable.Average确实占用了Func<T, int?>，所以使用了重载.但是当转换为SQL EF时，不知道如何处理已编译的lambda.

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.

由于Average在分组中，因此无法将表达式传递给它，因此必须将整个表达式构建到Select.

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

由于可以创建非常混乱的代码，因此您可以创建Select的自定义版本，以用您的自定义表达式平均替换表达式的一部分，因此至少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);
        }
    }
}

用法:

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);

这篇关于将编译的lambda表达式用于平均值时，出现NotSupportedException的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！