I was using EF.Functions.Like method for my query on my ASP.NET Core 3.1 SPI but as soon as I upgraded it to .NET 6 along with with all nugets, this particular query started to throw following error

InvalidOperationException : The Like method is not supported because the query has switched to client-evaluation

I have done some research and I know InMemory databases can cause this issue, but thats not the case I've tried localhosting the API and also published SPI and in both cases the database is always on SQL Server (not local on my machine) so its not an inmemory database, also the fact it worked fine on asp.net core 3.1

Example query

Query Method

public static async Task<IQueryable<Evidence>> QueryEvidencesAsync(this EvidenceContext Context, EvidenceQuery query)
    {

            var predicate = PredicateBuilder.True<Evidence>();
            predicate = predicate.And(x => x.IsActive && x.EvidenceStatus != EvidenceStatus.ReleasedToOwner && x.EvidenceStatus != EvidenceStatus.Transfer);
            if (query.EvidenceStatus is not EvidenceStatus.Disposed)
            {
                predicate = predicate.And(x => x.IsActive && x.EvidenceStatus != EvidenceStatus.Disposed);
            }
            if (query.EvidenceStatus is not EvidenceStatus.Incoming)
            {
                predicate = predicate.And(x => x.IsActive && x.EvidenceStatus != EvidenceStatus.Incoming);
            }

            var predicateAndOr = query.IsOr ? PredicateBuilder.False<Evidence>() : PredicateBuilder.True<Evidence>();

            if (!string.IsNullOrWhiteSpace(query.CaseNumber))
            {
                predicateAndOr = predicateAndOr.AndOr(x => EF.Functions.Like(x.CaseFileNumber, $"%{query.CaseNumber}%"), query.IsOr);
            }

            predicate = predicate.And(predicateAndOr);
            var predicateCompiled = predicate.Compile();

            return query.IncludeRequests
                ? Context.Evidences.Include(x => x.Requests).AsNoTracking().Where(predicateCompiled).AsQueryable().OrderByDescending(x => x.Modified)
                : Context.Evidences.Where(predicateCompiled).AsQueryable().OrderByDescending(x => x.Modified);
    }

PredicateBuilder

public static class PredicateBuilder
{
    public static Expression<Func<T, bool>> True<T>()
    {
        return (T _) => true;
    }

    public static Expression<Func<T, bool>> False<T>()
    {
        return (T _) => false;
    }

    public static Expression<Func<T, bool>> Or<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        InvocationExpression right = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.OrElse(expr1.Body, right), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> And<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2)
    {
        InvocationExpression right = Expression.Invoke(expr2, expr1.Parameters.Cast<Expression>());
        return Expression.Lambda<Func<T, bool>>(Expression.AndAlso(expr1.Body, right), expr1.Parameters);
    }

    public static Expression<Func<T, bool>> AndOr<T>(this Expression<Func<T, bool>> expr1, Expression<Func<T, bool>> expr2, bool or = false)
    {
        if (!or)
        {
            return expr1.And(expr2);
        }

        return expr1.Or(expr2);
    }
}

in this particular example, query.IsOr will always be false so predicate will always build with And predicate. Also query.CaseNumber will have the string value for caseNumber

I have removed many more conditions and if statement all of them just checked for one property and add that check to predicate, but as I said they are not relevant here as in this scenario only the if statements I've shown here are true and hence validated.

CodePudding user response：

You're building a predicate as an Expression<Func<Evidence, bool>>, but then you're .Compile()ing it into a Func<Evidence, bool>. That means when you call .Where() you're no longer calling Queryable.Where<T>(this IQueryable<T> source, Expression<Func<Evidence, bool>> criteria), but rather Enumerable.Where(this IEnumerable<T> source, Func<Evidence, bool> criteria).

The difference there is that the method you're calling will treat the query as an IEnumerable<T>, and rather than allowing Entity Framework to issue a query against the database with your criteria as a WHERE clause, it will bring all of the objects back from the database, and then pass them into the criteria function (which has been compiled into memory) to determine which ones to keep.

In other words, your code in the past was terribly inefficient, and you didn't realize it. EF.Functions.Like was never meant to be invoked: it was only supposed to be evaluated as part of a query. Earlier versions of EF.Functions.Like did their best to mimic the behavior of SQL's LIKE when they were called, whereas newer versions will just throw an exception to warn you that you're doing it wrong.

You need to avoid .Compile(), and pass predicate directly into your .Where() call.

            predicate = predicate.And(predicateAndOr);

            return query.IncludeRequests
                ? Context.Evidences.Include(x => x.Requests).AsNoTracking().Where(predicate).OrderByDescending(x => x.Modified)
                : Context.Evidences.Where(predicate).OrderByDescending(x => x.Modified);

That may lead to other issues, but it'll get you one step closer to doing it right.