时间戳

首页 > 解决方案 > LINQ 构建错误的 SQL 查询

sql - LINQ 构建错误的 SQL 查询

问题描述

为什么 LINQ 会构建错误的 SQL 查询？它需要很长时间并且缺少“WHERE”子句（使用EntityFrameworkCore 2.2）。

我的模型：

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

我使用 IQueryable 和调用查询的简单代码：

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

我在输出中看到的这个 sql：

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

WHERE 子句在哪里？我需要类似的东西：

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

标签： sql.netentity-frameworklinqentity-framework-core

解决方案

添加LINQKit以获得一些有用的Expression实用程序，然后使用这些扩展 - 它们创建可以转换为 SQL 的表达式&&。|| Where这就是 EF Core 3.x 不再自动进行客户端评估的原因（尽管我认为他们仍然需要在 SQL 方面做更多事情。）

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

推荐阅读