Dynamic Expression doesn't support Like

Question

I'm creating a DataTable component for Blazor. It is working but I have a problem when I want to filter the data in memory dynamically with Like (see the code 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)

The function that creates the expression filter is the following

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

but I think this function is working well. The problem is when I apply the filter to the AllItems.

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

Also, I tried to change this line with

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

but I get the same result.

Update

This is the Quick Watch I see for the filterExpression variable.

I have another function for Equals and it is working

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

Update/2

As Richard said, in EF Core 3.1, the DbFunctionExtensions.Like method works with in-memory queries; but the code has since been updated to just throw an InvalidOperationException in commit. The generic implementation with Expression<Func<TModel, bool>> is working for every kind of Type apart from string.

So, I add a custom implementation for 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

The error itself is not related to dynamic queries. You can reproduce it like this:

using Microsoft.EntityFrameworkCore;
// ...

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

This will throw the same exception, and this is kind of query you are dynamically building with expressions in your ContainsFilter. The reason is EF.Functions.Like is not intended to be used with in-memory collections. It's only purpose is to be analyzed by EF Core expression tree analyzer and be converted to SQL LIKE statement. If you execute this function normally (that's what happens when you use it with in-memory collection) it just throws exception:

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

So to filter in-memory you need to use something different (maybe just basic string.Contains will do, maybe not, we don't know).