EF Core Full-text search: parameterized keySelector could not be translated into SQL

Question

I'd like to create a generic extension method which allow me to use Full-text search.

● The code below works:

IQueryable<MyEntity> query = Repository.AsQueryable();

if (!string.IsNullOrEmpty(searchCondition.Name))
    query = query.Where(e => EF.Functions.Contains(e.Name, searchCondition.Name));

return query.ToList();

● But I want a more-generic-way so I create the following extension method

public static IQueryable<T> FullTextContains<T>(this IQueryable<T> query, Func<T, string> keySelector, string value)
{
    return query.Where(e => EF.Functions.Contains(keySelector(e), value));
}

When I call the exxtension method like below, I got an exception

IQueryable<MyEntity> query = Repository.AsQueryable();

if (!string.IsNullOrEmpty(searchCondition.Name))
    query = query.FullTextContains(e => e.Name, searchCondition.Name);

return query.ToList();

> System.InvalidOperationException: 'The LINQ expression 'DbSet
>     .Where(c => __Functions_0
>         .Contains(
>           _: Invoke(__keySelector_1, c[MyEntity])
>           , 
> propertyReference: __value_2))' 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 either AsEnumerable(),
> AsAsyncEnumerable(), ToList(), or ToListAsync().
> See https://go.microsoft.com/fwlink/?linkid=2101038 for more information
>

How do I "rewrite the query in a form that can be translated" as the Exception suggested?

score 1 · Accepted Answer · answered Jun 02 '20 at 06:10

Your are falling into the typical IQueryable<> trap by using delegates (Func<>) instead of expressions (Expression<Func<>). You can see that difference in the lambda arguments of every Queryable extension method vs corresponding Enumerable method. The difference is that the delegates cannot be translated (they are like unknown methods), while the expressions can.

So in order to do what you want, you have to change the signature of the custom method to use expression(s):

public static IQueryable<T> FullTextContains<T>(
    this IQueryable<T> query,
    Expression<Func<T, string>> keySelector, // <--
    string value)

But now you have implementation problem because C# does not support syntax for "invoking" expressions similar to delegates, so the following

keySelector(e)

does not compile.

In order to do that you need at minimum a small utility for composing expressions like this:

public static partial class ExpressionUtils
{
    public static Expression<Func<TOuter, TResult>> Apply<TOuter, TInner, TResult>(this Expression<Func<TOuter, TInner>> outer, Expression<Func<TInner, TResult>> inner)
        => Expression.Lambda<Func<TOuter, TResult>>(inner.Body.ReplaceParameter(inner.Parameters[0], outer.Body), outer.Parameters);

    public static Expression<Func<TOuter, TResult>> ApplyTo<TInner, TResult, TOuter>(this Expression<Func<TInner, TResult>> inner, Expression<Func<TOuter, TInner>> outer)
        => outer.Apply(inner);

    public static Expression ReplaceParameter(this Expression expression, ParameterExpression source, Expression target)
        => new ParameterReplacer { source = source, target = target }.Visit(expression);

    class ParameterReplacer : ExpressionVisitor
    {
        public ParameterExpression source;
        public Expression target;
        protected override Expression VisitParameter(ParameterExpression node)
            => node == source ? target : node;
    }
}

Use Apply or ApplyTo depending of what type of expression you have. Other than that they do the same.

In your case, the implementation of the method with Expression<Func<T, string>> keySelector would be

return query.Where(keySelector.Apply(key => EF.Functions.Contains(key, value)));

EF Core Full-text search: parameterized keySelector could not be translated into SQL

1 Answers1