CODEWITHSUNDEEP
c#.net-coreentity-framework-core
martijn
martijn

Reputation: 1469

Expression tree InvalidOperationException: The LINQ expression can't be resolved

I am currently trying to convert this:

users.Where(s => EF.Functions.Like(EF.Functions.Collate(s.Username, "Latin1_General_CI_AI"), $"%{searchString}%"));

into a expression tree function (code below). I got this far using this answer

public static IQueryable<T> IgnoreAccentSearch<T>(this IQueryable<T> queryable, string searchString, string property)
{
    // Get our generic object
    ParameterExpression entity = Expression.Parameter(typeof(T), "entity");

    // get the Collate method from EF.Functions
    var efCollatMethod = typeof(RelationalDbFunctionsExtensions)
        .GetMethod("Collate")
        .MakeGenericMethod(typeof(T));

    // Get the Like Method from EF.Functions
    var efLikeMethod = typeof(DbFunctionsExtensions).GetMethod("Like",
        BindingFlags.Static | BindingFlags.Public | BindingFlags.NonPublic,
        null,
        new[] { typeof(DbFunctions), typeof(string), typeof(string) },
        null);

    // We make a pattern for the search
    var pattern = Expression.Constant($"%{searchString}%", typeof(string));

    // make the collate constant
    var collate = Expression.Constant("Latin1_General_CI_AI", typeof(string));

    // Create the collate expression
    Expression collateExpression = Expression.Call(efCollatMethod,
        Expression.Property(null, typeof(EF), nameof(EF.Functions)), entity, collate);

    // Get property from our object
    var propertyExpression = Expression.Property(collateExpression, property);

    // Сall the method with all the required arguments
    Expression whereExpression = Expression.Call(efLikeMethod,
         Expression.Property(null, typeof(EF), nameof(EF.Functions)), propertyExpression, pattern);

    // Compose and pass the expression to Where
    var expression = Expression.Lambda<Func<T, bool>>(whereExpression, entity);
    return queryable.Where(expression);
}

I get the following error message:

InvalidOperationException: The LINQ expression 'DbSet() .Where(u => __Functions_0 .Like( matchExpression: __Functions_0 .Collate( operand: u, collation: "Latin1_General_CI_AI").Username, pattern: "%K%"))' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

So I'm doing something wrong. Can anyone help me here? I haven't worked with expression trees a lot before, so any info is welcome.

Upvotes: 2

Views: 432

Answers (1)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

Usually, if it is possible, I'm trying to force compiler to do major part of work:

public static IQueryable<T> IgnoreAccentSearch<T>(this IQueryable<T> queryable, string searchString, string property)
{
    // defining Expression Template with two input parameters
    Expression<Func<string, string, bool>> predicateTemplate = 
        (prop, value) => EF.Functions.Like(EF.Functions.Collate(prop, "Latin1_General_CI_AI"), $"%{value}%"));

    var entityParam = Expression.Parameter(typeof(T), "entity");
    var propExpr = Expression.PropertyOrField(entityParam, property);
    var searchExpr = Expression.Constant(searchString);

    var body = predicateTemplate.Body;
    
    // injecting needed values instead of parameters
    body = ReplacingExpressionVisitor.Replace(predicateTemplate.Parameters[0], propExpr, body);
    body = ReplacingExpressionVisitor.Replace(predicateTemplate.Parameters[1], searchExpr, body);

    var lambda = Expression.Lambda<Func<T, bool>>(body, entityParam);

    return queryable.Where(lambda);
}

Upvotes: 4

Related Questions