如何将动态SQL条件追加到EF的IQueryable并优化Kendo Grid查询性能？

核心问题分析 #

你当前用_db.Database.SqlQuery<T>直接拼接SQL字符串的方式性能极差，原因在于：

1. 该方法返回IEnumerable<T>，会把所有符合条件的500万行数据全量加载到内存，再做Kendo Grid的分页/排序，内存和IO开销极大；
2. 直接拼接字符串存在SQL注入风险，且无法利用EF的查询优化和数据库索引的下推能力。

最优实现方案：保持IQueryable特性，让查询下推到数据库 #

我们需要将动态条件转换为EF可识别的表达式，保持IQueryable<T>类型，这样Kendo Grid的ToDataSourceResult会自动将分页、排序、过滤逻辑转换成SQL，只查询当前页需要的数据，彻底解决性能问题。

方案1：使用Dynamic LINQ库（快速实现复杂动态条件） #

这是最简便的方式，通过第三方库直接将字符串条件转换为EF可执行的表达式。

1. 安装依赖：NuGet安装System.Linq.Dynamic.Core包；
2. 修改服务层方法：给ComBigDataService添加重载方法，支持字符串条件：

public class ComBigDataService<T> where T : class
{
    private readonly DbSet<T> _dbset;
    // 构造函数注入DbSet省略...

    // 原有的Lambda条件方法
    public IQueryable<T> GetAll(Expression<Func<T, bool>> predicate)
    {
        return _dbset.Where(predicate);
    }

    // 新增：支持字符串动态条件
    public IQueryable<T> GetAll(string whereCondition)
    {
        return _dbset.Where(whereCondition);
    }

    // 带参数的安全版本（避免SQL注入）
    public IQueryable<T> GetAll(string whereCondition, params object[] parameters)
    {
        return _dbset.Where(whereCondition, parameters);
    }
}

3. 适配Kendo Grid接口：

public ActionResult REFDARMANREQRead([DataSourceRequest] DataSourceRequest request)
{
    // 安全的参数化动态条件（推荐，避免注入）
    var temp = new ComBigDataService<WMISREFDARMANREQMSTView>()
        .GetAll("bookType == @0 AND Level in @1", 2, new[] {1,2,4,6,9});
    
    // 直接用字符串条件（仅当条件完全由后端生成，无用户输入时使用）
    // var temp = new ComBigDataService<WMISREFDARMANREQMSTView>()
    //     .GetAll("bookType == 2 AND Level in (1,2,4,6,9)");

    var data = temp.ToDataSourceResult(request);
    return Json(data, JsonRequestBehavior.AllowGet);
}

方案2：手动构建表达式树（无第三方依赖，更安全） #

如果不想引入第三方库，可以手动构建Expression<Func<T, bool>>表达式，将动态条件转换为EF可识别的查询逻辑：

// 构建动态条件的工具方法
public static Expression<Func<WMISREFDARMANREQMSTView, bool>> BuildBookQueryCondition(int bookType, List<int> levels)
{
    var param = Expression.Parameter(typeof(WMISREFDARMANREQMSTView), "x");
    
    // 构建bookType == 2的表达式
    var bookTypeProp = Expression.Property(param, nameof(WMISREFDARMANREQMSTView.bookType));
    var bookTypeValue = Expression.Constant(bookType);
    var bookTypeMatch = Expression.Equal(bookTypeProp, bookTypeValue);
    
    // 构建Level in (1,2,4,6,9)的表达式
    var levelProp = Expression.Property(param, nameof(WMISREFDARMANREQMSTView.Level));
    var levelsConstant = Expression.Constant(levels);
    var containsMethod = typeof(List<int>).GetMethod(nameof(List<int>.Contains), new[] {typeof(int)});
    var levelMatch = Expression.Call(levelsConstant, containsMethod, levelProp);
    
    // 组合两个条件
    var combinedCondition = Expression.AndAlso(bookTypeMatch, levelMatch);
    return Expression.Lambda<Func<WMISREFDARMANREQMSTView, bool>>(combinedCondition, param);
}

// Kendo接口中使用
public ActionResult REFDARMANREQRead([DataSourceRequest] DataSourceRequest request)
{
    var whereExpr = BuildBookQueryCondition(2, new List<int>{1,2,4,6,9});
    var temp = new ComBigDataService<WMISREFDARMANREQMSTView>().GetAll(whereExpr);
    var data = temp.ToDataSourceResult(request);
    return Json(data, JsonRequestBehavior.AllowGet);
}

方案3：FromSqlRaw结合IQueryable（兼容原生SQL场景） #

如果必须使用原生SQL字符串，要确保返回IQueryable<T>，并做参数化处理：

public ActionResult REFDARMANREQRead([DataSourceRequest] DataSourceRequest request)
{
    // 参数化SQL，避免注入
    var temp = _dbset.FromSqlRaw(
        "SELECT * FROM book WHERE bookType = @p0 AND Level IN @p1",
        2, new[] {1,2,4,6,9}
    ).AsQueryable();
    
    var data = temp.ToDataSourceResult(request);
    return Json(data, JsonRequestBehavior.AllowGet);
}

额外性能优化建议 #

1. 数据库索引优化：针对bookType和Level创建复合索引，大幅提升查询速度：

CREATE NONCLUSTERED INDEX IX_Book_BookType_Level 
ON book(bookType, Level);

2. **避免SELECT ***：仅查询Kendo Grid需要的字段，减少数据传输量，比如用投影：

var temp = _dbset.Where(whereExpr)
    .Select(x => new 
    {
        x.Id,
        x.bookType,
        x.Level,
        x.BookName // 只保留需要的字段
    });

3. 确认Kendo分页配置：前端Grid需设置pageSize，确保后端只返回当前页数据，避免全量加载。

内容的提问来源于stack exchange，提问作者milad.sh