c# - 动态表达式不支持 Like

Question

我正在为 Blazor 创建一个 DataTable 组件。它正在工作，但是当我想使用 Like 动态过滤内存中的数据时遇到问题(见代码 here)

public IList<TModel> Items { get; set; } = new List<TModel>();
private IQueryable<TModel> AllItems { get; set; }

if (AllFilterRules.Count == 0) Items = AllItems;
else
{
    Expression<Func<TModel, bool>>? filterExpression = null;

    foreach (var filterRule in AllFilterRules)
    {
        var filterRuleExpression = filterRule.GenerateExpression();

        if (filterExpression == null) filterExpression = filterRuleExpression;
        else filterExpression = PredicateBuilder.And(filterExpression, filterRuleExpression);
    }

    Items = AllItems.Where(filterExpression).ToList();

    useFilteredResult = true;
}

crit: Microsoft.AspNetCore.Components.WebAssembly.Rendering.WebAssemblyRenderer[100]

Unhandled exception rendering component: The 'Like' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server.

System.InvalidOperationException: The 'Like' method is not supported because the query has switched to client-evaluation. This usually happens when the arguments to the method cannot be translated to server. Rewrite the query to avoid client evaluation of arguments so that method can be translated to server.

at Microsoft.EntityFrameworkCore.DbFunctionsExtensions.LikeCore(String matchExpression, String pattern, String escapeCharacter)

at Microsoft.EntityFrameworkCore.DbFunctionsExtensions.Like(DbFunctions _, String matchExpression, String pattern)

at System.Linq.Enumerable.WhereArrayIterator`1[[PSC.Blazor.Examples.Data.WeatherForecast, PSC.Blazor.Examples, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext()

at System.Collections.Generic.List1[[PSC.Blazor.Examples.Data.WeatherForecast, PSC.Blazor.Examples, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]]..ctor(IEnumerable1 collection)

at System.Linq.Enumerable.ToList[WeatherForecast](IEnumerable`1 source)

at PSC.Blazor.Components.DataTable.DataTable`1[[PSC.Blazor.Examples.Data.WeatherForecast, PSC.Blazor.Examples, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].PerformClientSideDataManipulations() in C:\Projects\PSC.Blazor.Components.DataTable\PSC.Blazor.Components.DataTable\DataTable.razor:line 560

at PSC.Blazor.Components.DataTable.DataTable`1.d__172[[PSC.Blazor.Examples.Data.WeatherForecast, PSC.Blazor.Examples, Version=1.0.0.0, Culture=neutral, PublicKeyToken=null]].MoveNext() in C:\Projects\PSC.Blazor.Components.DataTable\PSC.Blazor.Components.DataTable\DataTable.razor:line 511

at Microsoft.AspNetCore.Components.ComponentBase.CallStateHasChangedOnAsyncCompletion(Task task)

at Microsoft.AspNetCore.Components.RenderTree.Renderer.GetErrorHandledTask(Task taskToHandle)

创建表达式过滤器的函数如下

private class ContainsFilter : ObjectFilter
{
    public override bool ValueRequired => true;

    public override bool IsNumberAllowed => false;

    public override bool IsBoolAllowed => false;

    public override bool IsStringAllowed => true;

    public override bool IsDateTimeAllowed => false;

    public override bool IsNonNullableAllowed => true;

    internal ContainsFilter(int id, string name)
        : base(id, name)
    {
    }

    public override Expression<Func<TModel, bool>> GenerateExpression<TModel>(
        string propertyName,
        object value)
    {
        Expression expression = (Expression)Expression.Parameter(typeof(TModel), "e");

        string str = propertyName;
        char[] chArray = new char[1] { '.' };

        foreach (string propertyOrFieldName in str.Split(chArray))
            expression = (Expression)Expression.PropertyOrField(expression, propertyOrFieldName);

        ConstantExpression constantExpression = Expression.Constant((object)string.Format("%{0}%", value));
            
        return Expression.Lambda<Func<TModel, bool>>(Expression.Call(typeof(DbFunctionsExtensions), 
            nameof(DbFunctionsExtensions.Like), null, Expression.Constant(EF.Functions), 
            expression, constantExpression));
    }
}

但我认为这个功能运行良好。问题是当我将过滤器应用于 AllItems 时.

Items = AllItems.Where(filterExpression).ToList();

另外，我试图用

Items = new List<TModel>();
var t = AllItems.Where(filterExpression);
foreach (var i in t)
    Items.Add(i);

但我得到了相同的结果。
更新
这是我看到的关于 filterExpression 的 Quick Watch多变的。

我有 Equals 的另一个功能它正在工作

private class IsEqualsFilter : ObjectFilter
{
    public override bool ValueRequired => true;

    public override bool IsNumberAllowed => true;

    public override bool IsBoolAllowed => true;

    public override bool IsStringAllowed => true;

    public override bool IsDateTimeAllowed => true;

    public override bool IsNonNullableAllowed => true;

    internal IsEqualsFilter(int id, string name)
        : base(id, name)
    {
    }

    public override Expression<Func<TModel, bool>> GenerateExpression<TModel>(
        string propertyName,
        object value)
    {
        ParameterExpression parameterExpression = Expression.Parameter(typeof(TModel), "e");
        Expression expression = (Expression)parameterExpression;

        string str = propertyName;
        char[] chArray = new char[1] { '.' };

        foreach (string propertyOrFieldName in str.Split(chArray))
            expression = (Expression)Expression.PropertyOrField(expression, propertyOrFieldName);

        UnaryExpression unaryExpression = !expression.Type.IsEnum ?
                Expression.ConvertChecked(Expression.Constant(value), expression.Type) :
                Expression.ConvertChecked(Expression.Constant(
                (object)Convert.ToInt32(Enum.Parse(expression.Type, value.ToString()))), 
                expression.Type);

        return Expression.Lambda<Func<TModel, bool>>(Expression.Equal(expression, unaryExpression), 
            parameterExpression);
    }
}

更新/2
如 Richard说，在 EF Core 3.1 中， DbFunctionExtensions.Like 方法适用于内存查询；但此后代码已更新为仅在 commit 中抛出 InvalidOperationException | .使用 Expression<Func<TModel, bool>> 的通用实现适用于各种 Type除了 string .
所以，我为 string 添加了一个自定义实现

public override IList<TModel> ApplyEmbeddedFilter<TModel>(IList<TModel> models, 
    string propertyName, string value)
{
    var searchStrLower = value.ToLower();
    var propsToCheck = typeof(TModel).GetProperties()
          .Where(a => a.PropertyType == typeof(string) && 
                 a.Name == propertyName && a.CanRead);

    return models.Where(obj => {
        foreach (PropertyInfo prop in propsToCheck)
        {
            string value = (string)prop.GetValue(obj);
            if (value != null && value.ToLower().Contains(searchStrLower)) 
                return true;
        }
        return false;
    }).ToList();
}

Answer 1

错误本身与动态查询无关。你可以像这样重现它:

using Microsoft.EntityFrameworkCore;
// ...

IQueryable<string> test = (new[] { "a", "b" }).AsQueryable();
var result = test.Where(c => EF.Functions.Like(c, "a")).ToArray();

这将引发相同的异常，这是您使用 ContainsFilter 中的表达式动态构建的查询类型。 .原因是 EF.Funcions.Like不打算与内存中的集合一起使用。它的唯一目的是被 EF Core 表达式树分析器分析并转换为 SQL LIKE陈述。如果您正常执行此函数(这就是将它与内存中集合一起使用时会发生的情况)，它只会抛出异常:

EF.Functions.Like("a", "b"); // throws the same exception

所以要过滤内存，你需要使用不同的东西(也许只是基本的 string.Contains 会做，也许不会，我们不知道)。