使用EF时大量Concat/Where子句导致栈溢出问题求助

在Entity Framework中构建包含大量Concat或多条件Where子句的查询时，出现栈溢出错误。需求是构建形如(A AND B) OR (C AND D) OR ...的动态查询，其中OR连接的AND子句数量为动态值（0到数千条）。尝试为每个AND子句创建查询后用Concat合并，触发了栈溢出异常。

错误信息 #

Stack overflow.
Repeat 798 times: #

at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.Visit[[System.__Canon, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]](System.Collections.ObjectModel.ReadOnlyCollection1<System.__Canon>, System.Func2<System.__Canon,System.__Canon>, StateType ByRef, State[] ByRef, Boolean)
at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.Visit(System.Collections.ObjectModel.ReadOnlyCollection`1<System.Linq.Expressions.Expression>, StateType ByRef, State[] ByRef, Boolean)
at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.VisitMethodCall(System.Linq.Expressions.MethodCallExpression)
at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.Visit(System.Linq.Expressions.Expression) #

at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.Visit[[System.__Canon, System.Private.CoreLib, Version=8.0.0.0, Culture=neutral, PublicKeyToken=7cec85d7bea7798e]](System.Collections.ObjectModel.ReadOnlyCollection1<System.__Canon>, System.Func2<System.__Canon,System.__Canon>, StateType ByRef, State[] ByRef, Boolean)
at Microsoft.EntityFrameworkCore.Query.Internal.ExpressionTreeFuncletizer.Visit(System.Collections.ObjectModel.ReadOnlyCollection`1<System.Linq.Expressions.Expression>, StateType ByRef, State[] ByRef, Boolean)

使用的类库版本 #

• EFCore.BulkExtensions.PostgreSql Version="8.1.2"
• Microsoft.EntityFrameworkCore.Design Version="9.0.0"
• Npgsql.EntityFrameworkCore.PostgreSQL Version="9.0.2"
• Npgsql.EntityFrameworkCore.PostgreSQL.Design Version="1.1.0"

复现代码 #

private class SimpleDbContext : DbContext
{
    // Stores the values 0, 1, 2, ..., 100000
    public virtual DbSet<SequencePoint> SequencePoints { get; set; }
}

private class SequencePoint
{
    public int SequenceNumber { get; set; }
}
    
private void ConcatErrorTest()
{
    SimpleDbContext simpleDbContext = new();
        
    List<Tuple<int, int>> selectRanges = new(); // 0, 10, 11, 20, 21, 21, etc...
        
    for (int i = 0; i < 7500; i++)
    {
        int startRange = i * 10;
        int endRange = startRange + (i % 5);
        selectRanges.Add(new Tuple<int, int>(startRange, endRange));
    }

    IQueryable<SequencePoint> queryable = null;

    foreach (Tuple<int,int> selectRange in selectRanges)
    {
        IQueryable<SequencePoint> whereQueryable = simpleDbContext.SequencePoints.AsQueryable().Where(point =>
            (point.SequenceNumber >= selectRange.Item1) &&
            (point.SequenceNumber <= selectRange.Item2)
        );
            
        queryable = queryable == null ? whereQueryable : queryable.Concat(whereQueryable);
    }

    // Throws Stack overflow.
    List<int> result = queryable.Select(sequenceNumber => sequenceNumber.SequenceNumber).ToList();
        
    _logger.LogInformation("result = {result}", result);
}

解决方案：动态构建OR表达式树 #

问题根源是多次Concat会生成嵌套极深的表达式树，EF解析时递归深度超过栈限制。正确做法是直接构建包含所有OR条件的单个Where子句，通过表达式树动态拼接。

代码实现 #

using System.Linq.Expressions;

private void DynamicOrQueryTest()
{
    SimpleDbContext simpleDbContext = new();
        
    List<Tuple<int, int>> selectRanges = new();
    for (int i = 0; i < 7500; i++)
    {
        int startRange = i * 10;
        int endRange = startRange + (i % 5);
        selectRanges.Add(new Tuple<int, int>(startRange, endRange));
    }

    // 初始化表达式：默认返回false（无查询条件时返回空结果）
    Expression<Func<SequencePoint, bool>> combinedExpression = point => false;

    foreach (var range in selectRanges)
    {
        // 单个范围的查询表达式
        Expression<Func<SequencePoint, bool>> rangeExpr = point => 
            point.SequenceNumber >= range.Item1 && point.SequenceNumber <= range.Item2;
            
        // 合并OR条件
        combinedExpression = Expression.Lambda<Func<SequencePoint, bool>>(
            Expression.OrElse(combinedExpression.Body, rangeExpr.Body),
            combinedExpression.Parameters.Single()
        );
    }

    // 执行最终查询
    List<int> result = simpleDbContext.SequencePoints
        .Where(combinedExpression)
        .Select(p => p.SequenceNumber)
        .ToList();
        
    _logger.LogInformation("result = {result}", result);
}

优化说明 #

• 生成扁平结构的OR表达式树，避免EF解析时的递归栈溢出
• 最终SQL为WHERE (SequenceNumber BETWEEN x1 AND y1) OR (SequenceNumber BETWEEN x2 AND y2) OR ...，性能优于多次Concat生成的UNION查询
• 若查询条件数量过万，可分批次查询后合并结果，避免生成过长SQL导致数据库解析压力

替代方案：使用Contains批量查询（范围跨度小时适用） #

如果范围可转换为离散值集合，可利用Contains方法，但需注意PostgreSQL对IN子句的数量限制（默认65535个值），超过时需分批次：

private void ContainsQueryTest()
{
    SimpleDbContext simpleDbContext = new();
        
    List<Tuple<int, int>> selectRanges = new();
    for (int i = 0; i < 7500; i++)
    {
        int startRange = i * 10;
        int endRange = startRange + (i % 5);
        selectRanges.Add(new Tuple<int, int>(startRange, endRange));
    }

    // 生成所有目标数字集合
    HashSet<int> targetNumbers = new();
    foreach (var range in selectRanges)
    {
        for (int num = range.Item1; num <= range.Item2; num++)
        {
            targetNumbers.Add(num);
        }
    }

    List<int> result = new();
    // 分批次查询，每批次取1000个值
    foreach (var batch in targetNumbers.Chunk(1000))
    {
        result.AddRange(simpleDbContext.SequencePoints
            .Where(p => batch.Contains(p.SequenceNumber))
            .Select(p => p.SequenceNumber)
            .ToList());
    }
        
    _logger.LogInformation("result = {result}", result);
}

注：此方案仅适用于范围跨度小的场景，跨度大时会占用过多内存，优先选择动态OR表达式树方案。

内容的提问来源于stack exchange，提问作者Peter