LINQ builds the wrong SQL query

Question

Why does LINQ build the wrong SQL query? It takes so long and is missing a "WHERE" clause (using EntityFrameworkCore 2.2).

My model:

public class SearchModel
{
    public string PersonName { get; set; }
    public string Id { get; set; }
}

Simple code where I use IQueryable and call query:

var nameParts = (model.PersonName ?? "").Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
var query = _dbContext.People
                .Where(p => nameParts.Any(part => p.Name.Contains(part)))
                .AsQueryable();
var test = await query.ToListAsync();

This sql I see in output:

SELECT [p].[Id], [p].[Age], [p].[Name] FROM [base].[People] AS [p]

Where is the WHERE clause? I need something like:

SELECT [p].[Id], [p].[Age], [p].[Name]  
FROM [base].[People] AS [p] 
WHERE [p].[Name] LIKE '%Text%' OR [p].[Name] LIKE '%Hello%'

seems like entityframework is not able to create the where query and does it in memory after the select... try with .Where(p => nameParts.Contains(p.Name)) and see what it does — sdi, Mar 10 '20 at 08:14
@sdi Yes, If I add this, it build where query (WHERE [p].[Name] IN ('dfs')) But nameParts.Contains(p.Name) it not that I need. How make my request work? — IngBond, Mar 10 '20 at 09:10
If you check your runtime output you'll see that EF is issuing a warning about client-side evaluation. The problem is ``nameParts.Any` - this typically maps to `EXISTS()`, not `OR`. `nameParts` isn't a table though, so there's no way of generating a proper subquery — Panagiotis Kanavos, Mar 10 '20 at 15:58
Check this answerhttps://stackoverflow.com/questions/457316/combining-two-expressions-expressionfunct-bool — mtkachenko, Mar 11 '20 at 07:18

score 0 · Answer 1 · answered Mar 10 '20 at 17:18

Add LINQKit to gain some helpful Expression utilities, then use these extensions - they create && and || Where expressions that can be translated to SQL. This is why EF Core 3.x no longer automatically does client evaluation (though I think they need to do more on the SQL side still.)

public static class LinqKitExt { // using LINQKit
    public static IQueryable<T> WhereAny<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,TKey>> keyFne, IEnumerable<TKey> searchTerms) {
        Expression<Func<T,bool>>  pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).Equals(s));

        return dbq.Where(pred.Expand());
    }

    public static IQueryable<T> WhereAnyIsContained<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> searchTerms) {
        Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).Contains(s));

        return dbq.Where(pred.Expand());
    }

    public static IQueryable<T> WhereAllIsContained<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> searchTerms) {
        Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.And(a => keyFne.Invoke(a).Contains(s));

        return dbq.Where(pred.Expand());
    }

    public static IQueryable<T> WhereAnyIsContained<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,IEnumerable<TKey>>> keyFne, IEnumerable<TKey> searchTerms) {
        Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).Contains(s));

        return dbq.Where(pred.Expand());
    }

    public static IQueryable<T> WhereAllIsContained<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,IEnumerable<TKey>>> keyFne, IEnumerable<TKey> searchTerms) {
        Expression<Func<T,bool>> pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.And(a => keyFne.Invoke(a).Contains(s));

        return dbq.Where(pred.Expand());
    }

    public static IOrderedQueryable<T> OrderByAny<T, TKey>(this IQueryable<T> dbq, Expression<Func<T,TKey>> keyFne, IEnumerable<TKey> searchTerms) {
        var  pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).Equals(s));

        var orderBody = Expression.Condition(pred.Body.Expand(), Expression.Constant(1), Expression.Constant(2));
        return dbq.OrderBy(Expression.Lambda<Func<T, int>>(orderBody, pred.Parameters));
    }

    public static IOrderedQueryable<T> OrderByAnyIsContained<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> searchTerms) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).StartsWith(s));

        var orderBody = Expression.Condition(pred.Body.Expand(), Expression.Constant(1), Expression.Constant(2));
        return dbq.OrderBy(Expression.Lambda<Func<T, int>>(orderBody, pred.Parameters));
    }

    public static IOrderedQueryable<T> OrderByAllIsContained<T>(this IQueryable<T> dbq, Expression<Func<T,string>> keyFne, IEnumerable<string> searchTerms) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.And(a => keyFne.Invoke(a).StartsWith(s));

        var orderBody = Expression.Condition(pred.Body.Expand(), Expression.Constant(1), Expression.Constant(2));
        return dbq.OrderBy(Expression.Lambda<Func<T, int>>(orderBody, pred.Parameters));
    }

    public static IOrderedQueryable<T> OrderByAnyIsContained<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,IEnumerable<TKey>>> keyFne, IEnumerable<TKey> searchTerms) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.Or(a => keyFne.Invoke(a).Contains(s));

        var orderBody = Expression.Condition(pred.Body.Expand(), Expression.Constant(1), Expression.Constant(2));
        return dbq.OrderBy(Expression.Lambda<Func<T, int>>(orderBody, pred.Parameters));
    }

    public static IOrderedQueryable<T> OrderByAllIsContained<T,TKey>(this IQueryable<T> dbq, Expression<Func<T,IEnumerable<TKey>>> keyFne, IEnumerable<TKey> searchTerms) {
        var pred = PredicateBuilder.New<T>();
        foreach (var s in searchTerms)
            pred = pred.And(a => keyFne.Invoke(a).Contains(s));

        var orderBody = Expression.Condition(pred.Body.Expand(), Expression.Constant(1), Expression.Constant(2));
        return dbq.OrderBy(Expression.Lambda<Func<T, int>>(orderBody, pred.Parameters));
    }
}

LINQ builds the wrong SQL query

1 Answers1