EF中如何一次性加载自引用嵌套列表的所有层级？

模型定义 #

WhereClause类 #

public class WhereClause
{
    [Key]
    public int Id { get; set; }

    public int? ColumnId { get; set; }

    [StringLength(100)]
    public string Logic { get; set; }

    [StringLength(250)]
    public string Value { get; set; }

    public List<WhereClause?> WhereClauses { get; set; }

    public short HierarchyCount { get; set; }
}

Report类 #

public class Report:ModelAbstract
{
    [Key]
    public int Id { get; set; }

    [Required]
    [StringLength(100)]
    public string Name { get; set; }

    [Required]
    public Table Table { get; set; }

    public List<SelectClause> SelectClauses { get; set; }

    [ForeignKey("WhereClause")]
    public int? WhereClauseId { get; set; }

    public WhereClause WhereClause { get; set; }
}

问题需求 #

如上所示，WhereClause类包含自身类型的列表属性WhereClauses，我希望在一次查询中加载所有层级的内部列表。我知道可以用EF的Include和ThenInclude，但不确定嵌套层级的数量。目前我通过模型中的HierarchyCount属性，构造类似.Include("WhereClauses,WhereClauses.WhereClauses,WhereClauses.WhereClauses.WhereClauses")的字符串来实现，但想知道有没有更优的解决方案。

更新说明 #

WhereClause是Report表的一部分，不能为每个报表查询加载全部WhereClause数据。

方案1：基于HierarchyCount动态构建强类型Include链 #

避免字符串拼接的硬编码错误，用强类型循环构建Include层级：

public IQueryable<Report> GetReportWithAllWhereClauses(int reportId)
{
    var query = _context.Reports.Where(r => r.Id == reportId);
    
    // 获取当前报表对应WhereClause的层级数
    var hierarchyCount = _context.WhereClauses
        .Where(w => w.Id == _context.Reports.Where(r => r.Id == reportId).Select(r => r.WhereClauseId).FirstOrDefault())
        .Select(w => w.HierarchyCount)
        .FirstOrDefault();

    // 初始化Include链
    IIncludableQueryable<Report, WhereClause> includedQuery = query.Include(r => r.WhereClause);
    var currentInclude = includedQuery.ThenInclude(w => w.WhereClauses);
    
    // 循环添加多层级Include
    for (int i = 1; i < hierarchyCount; i++)
    {
        currentInclude = currentInclude.ThenInclude(w => w.WhereClauses);
    }

    return includedQuery;
}

方案2：EF Core延迟加载（小数据场景适用） #

开启EF Core延迟加载后，访问WhereClauses时会自动加载子层级，但需注意N+1查询问题：

1. 修改WhereClause的导航属性为virtual：

public virtual List<WhereClause?> WhereClauses { get; set; }

2. 在DbContext配置中启用延迟加载代理：

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseSqlServer("你的数据库连接字符串")
                  .UseLazyLoadingProxies();
}

此方案仅适合层级少、数据量小的场景，大数据场景不推荐。

方案3：递归CTE查询（EF Core 5+支持） #

利用递归CTE一次性查询所有层级的WhereClause，再手动组装层级结构，需给WhereClause添加父引用属性：

1. 修改WhereClause模型，添加父关联：

public int? ParentId { get; set; }
[ForeignKey(nameof(ParentId))]
public WhereClause? ParentWhereClause { get; set; }

2. 编写递归查询代码：

public async Task<Report> GetReportWithAllWhereClauses(int reportId)
{
    var report = await _context.Reports
        .Include(r => r.WhereClause)
        .FirstOrDefaultAsync(r => r.Id == reportId);

    if (report?.WhereClause == null)
        return report;

    // 递归CTE查询所有子层级WhereClause
    var allWhereClauses = await _context.WhereClauses
        .FromSqlRaw(@"
            WITH RecursiveWhereClauses AS (
                SELECT * FROM WhereClauses WHERE Id = {0}
                UNION ALL
                SELECT wc.* FROM WhereClauses wc
                INNER JOIN RecursiveWhereClauses rwc ON wc.ParentId = rwc.Id
            )
            SELECT * FROM RecursiveWhereClauses", report.WhereClauseId)
        .ToListAsync();

    // 手动组装层级结构
    var clauseDict = allWhereClauses.ToDictionary(c => c.Id);
    foreach (var clause in allWhereClauses)
    {
        if (clause.ParentId.HasValue && clauseDict.ContainsKey(clause.ParentId.Value))
        {
            clauseDict[clause.ParentId.Value].WhereClauses.Add(clause);
        }
    }

    return report;
}

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