EF Core 如何查询含全层级无限嵌套评论的指定讨论帖

你遇到的问题本质是EF Core原生的Include/ThenInclude机制仅支持固定深度的关联加载，面对自引用的无限层级嵌套场景，硬写链式调用既不现实也不可维护，没有必要硬凑多层ThenInclude，可以根据你的数据规模选择以下可直接落地的实现方案：

方案一：数据库递归CTE全量查询（推荐，适配任意层级深度） #

这个方案的核心逻辑是利用SQL本身支持的递归查询能力，一次性把当前Post下全层级的评论ID集合拿到，再通过EF的跟踪查询特性自动完成嵌套树的组装，避免多次查库的性能问题。

• 先写递归SQL，从一级直接关联Post的评论开始，逐层向下遍历所有子评论，把全树的评论ID查出来，同时可以在查询里嵌入你需要的过滤逻辑（比如排除当前用户隐藏的评论）
  示例代码：

// 递归获取全树评论ID，无深度限制
var allCommentIds = (await context.Database.SqlQuery<int>(
    """
    WITH CommentTree AS (
        -- 锚点：Post下的一级评论
        SELECT c.Id
        FROM Comments c
        WHERE c.PostId = {0}
          AND NOT EXISTS ( -- 替换成你的过滤逻辑，排除当前用户隐藏的评论
              SELECT 1 FROM CommentHiddenBy chb 
              WHERE chb.CommentId = c.Id AND chb.UserId = {1}
          )
        UNION ALL
        -- 递归：逐层查找子评论
        SELECT child.Id
        FROM Comments child
        INNER JOIN CommentTree parent ON child.RepliedToId = parent.Id
        WHERE NOT EXISTS ( -- 同样对子评论应用过滤逻辑
            SELECT 1 FROM CommentHiddenBy chb 
            WHERE chb.CommentId = child.Id AND chb.UserId = {1}
        )
    )
    SELECT Id FROM CommentTree
    """, 
    id, user.Id)).ToList();

• 加载目标Post的基础属性和全集合的评论关联，这时候只要所有评论已经被加载到EF上下文里，EF的内置关系修复机制会自动根据外键关系，把每个子评论挂载到对应父评论的Replies集合上，完全不需要你手动写递归拼接树。
  示例代码：

selected = await context.Posts
    .Include(p => p.Author)
    .Include(p => p.SavedBy)
    .Include(p => p.HiddenBy)
    .Include(p => p.UpvotedBy)
    .Include(p => p.DownvotedBy)
    .Include(p => p.Replies)
        .ThenInclude(c => c.Author)
    .Include(p => p.Replies)
        .ThenInclude(c => c.SavedBy)
    .Include(p => p.Replies)
        .ThenInclude(c => c.HiddenBy)
    .Include(p => p.Replies)
        .ThenInclude(c => c.UpvotedBy)
    .Include(p => p.Replies)
        .ThenInclude(c => c.DownvotedBy)
    .Where(p => p.Id == id)
    .Select(p => new Post {
        Id = p.Id,
        Title = p.Title,
        Content = p.Content,
        DateCreated = p.DateCreated,
        Author = p.Author,
        SavedBy = p.SavedBy,
        HiddenBy = p.HiddenBy,
        UpvotedBy = p.UpvotedBy,
        DownvotedBy = p.DownvotedBy,
        // 直接把全树评论集合关联上来，EF会自动修复嵌套结构
        Replies = context.Comments.Where(c => allCommentIds.Contains(c.Id)).ToList()
    })
    .SingleAsync();

方案二：逐层批量循环加载（适配中小数据量场景） #

如果不想写原生SQL，可以在内存里逐层批量拉取评论，避免N+1查询问题，等所有层级的评论都加载到上下文后，同样依靠EF的关系修复机制自动组装嵌套树。

• 先加载Post下的一级评论集合
• 循环收集当前层所有评论的ID，一次性批量查询这些ID作为父级的所有子评论，直到查不到新的子评论为止
  示例代码：

// 先加载一级评论
var rootComments = await context.Comments
    .Where(c => c.PostId == id && !c.HiddenBy.Contains(user))
    .Include(c => c.Author)
    .Include(c => c.SavedBy)
    .Include(c => c.HiddenBy)
    .Include(c => c.UpvotedBy)
    .Include(c => c.DownvotedBy)
    .ToListAsync();

var currentParentIds = rootComments.Select(c => c.Id).ToList();
// 逐层查所有子评论
while (currentParentIds.Any())
{
    var childComments = await context.Comments
        .Where(c => currentParentIds.Contains(c.RepliedTo!.Id))
        .Include(c => c.Author)
        .Include(c => c.SavedBy)
        .Include(c => c.HiddenBy)
        .Include(c => c.UpvotedBy)
        .Include(c => c.DownvotedBy)
        .ToListAsync();
    
    if (!childComments.Any()) break;
    // 下一轮查询用当前子评论的ID作为父级
    currentParentIds = childComments.Select(c => c.Id).ToList();
}

// 加载Post的基础属性，关联一级评论根集合即可
selected = await context.Posts
    .Where(p => p.Id == id)
    .Select(p => new Post {
        Id = p.Id,
        Title = p.Title,
        Content = p.Content,
        Author = p.Author,
        SavedBy = p.SavedBy,
        HiddenBy = p.HiddenBy,
        UpvotedBy = p.UpvotedBy,
        DownvotedBy = p.DownvotedBy,
        Replies = rootComments
    })
    .SingleAsync();

避坑提示 #

• 不要在循环里单条查询子评论，必须按当前层的父ID集合批量查询，否则评论层级稍高就会产生严重的N+1性能问题
• 你之前在Include里加的过滤条件（比如排除当前用户隐藏的评论）仅对一级评论生效，深层嵌套的评论不会应用这个过滤，建议把过滤逻辑嵌入到递归查询或者逐层加载的查询条件里，避免漏过滤
• 如果单条Post下评论量过万、层级深度超过10层，优先用递归CTE方案，逐层循环查询会产生多次数据库往返，接口延迟会明显升高

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