CODEWITHSUNDEEP
c#entity-frameworklinqef-core-3.0
Jani Kärkkäinen
Jani Kärkkäinen

Reputation: 48

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

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

Upvotes: 2

Views: 1974

Answers (1)

Krzysztof
Krzysztof

Reputation: 16150

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

Upvotes: 1

Related Questions