How can I force EF Core to convert LINQ's .Contains() to EF.Functions.Like()?

Question

I'm using Syncfusion's Grid component in ASP.NET Core project. When sorting, filtering and paginating the grid view it performs LINQ-operations to my IQueryable data source.

When searching text fields, it uses .Contains(string) method, which can't be translated to SQL query and will be evaluated locally.

Is there any way to force EF Core to alter the LINQ query (or to do it by myself) to use .EF.Functions.Like(column, string) instead, because it can be translated to SQL?

var dataSource = ...;
var operation = new QueryableOperation();

// Sorting
if (dm.Sorted != null)
{
   dataSource = operation.PerformSorting(dataSource, dm.Sorted);
}

// Filtering
if (dm.Where != null)
{
   // PerformFiltering uses .Contains(), which I don't want
   dataSource = operation.PerformFiltering(dataSource, dm.Where, dm.Where[0].Operator);
}

// At this point, I want to alter LINQ to use EF.Functions.Like instead of Contains.

var count = dataSource.Count();

// Paging
if (dm.Skip != 0)
{
   dataSource = operation.PerformSkip(dataSource, dm.Skip);
}

// Paging
if (dm.Take != 0)
{
   dataSource = operation.PerformTake(dataSource, dm.Take);
}

return dm.RequiresCounts ? Json(new { result = dataSource, count }) : Json(dataSource);

This post states `Contains` is translated to `CHARINDEX`: https://stackoverflow.com/questions/1033007/like-operator-in-entity-framework/46489164#46489164, are you sure it is run locally? — Martin Staufcik, Sep 13 '19 at 10:06
That is a very good question! I had to debug my problem a bit more and found out that indeed ```.Contains``` is not my problem, I presumed too much. Error message is: "The LINQ expression '(ti) => (ti.Outer.Name ?? "Blanks").ToString().ToLower().Contains("b")' could not be translated. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly...". Seems that those other functions which seems to be most simple ones can't be translated then. I'll have to find which one it excatly is. — Jani Kärkkäinen, Sep 13 '19 at 11:55

score 1 · Answer 1 · answered Sep 13 '19 at 10:12

You can modify ExpressionTree before execution and replace "".Contains() calls with EF.Functions.Like("", ""):

public static class LinqExtensions
{
    public static IQueryable<T> FixQuery<T>(this IQueryable<T> query)
    {
        return query.Provider.CreateQuery<T>(
            new FixQueryVisitor().Visit(query.Expression)
        );
    }

    class FixQueryVisitor : ExpressionVisitor
    {
        private readonly MethodInfo _likeMethod = ExtractMethod(() => EF.Functions.Like(string.Empty, string.Empty)); 

        private static MethodInfo ExtractMethod(Expression<Action> expr)
        {
            MethodCallExpression body = (MethodCallExpression)expr.Body;

            return body.Method;
        }

        protected override Expression VisitMethodCall(MethodCallExpression node)
        {
            if (node.Method.DeclaringType == typeof(string) && node.Method.Name == "Contains")
            {
                return Expression.Call(this._likeMethod, Expression.Constant(EF.Functions), node.Object, node.Arguments[0]);
            }

            return base.VisitMethodCall(node);
        }
    }
}

[...]
dataSource = dataSource.FixQuery();

Thank you for your help! I'll familiarize myself with ExpressionTree and ExpressionVisitor class, looks excactly what I'm looking for! — Jani Kärkkäinen, Sep 13 '19 at 11:48
@Krzysztof, how to apply this as a global behavior for every query? I have a use case when I need the EF to convert the list. contains in the were link, to another way. and I want it to be global, and not to manually add it before every query. — Mahamad Husen, Dec 15 '22 at 11:02

How can I force EF Core to convert LINQ's .Contains() to EF.Functions.Like()?

1 Answers1