LINQ builds the wrong SQL query

user_7267325 0
Viewed 99

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%'
entity-framework-core entity-framework sql linq .net
1 Answers

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));
    }
}
user_25571280
Related