如何在LINQ查询中获取EF Core的多层嵌套子问题

要实现单次SQL查询获取多层嵌套的子问题，最优方案肯定是利用递归CTE（Common Table Expression）——不管是通过EF Core的LINQ递归查询（EF Core 5及以上支持），还是直接写原生SQL，都能高效完成单次数据库交互，避免N+1查询的性能问题。

先给你梳理下具体的实现步骤，顺便优化下你的实体模型，让关联更清晰：

第一步：优化实体与关联配置 #

你的实体模型可以补充双向导航属性，让EF Core更好地理解层级关系：

QuestionDataModel 补充属性：

public class QuestionDataModel : IDataModel<Guid>
{
    public QuestionDataModel()
    {
        ChildQuestionsInHierarchy = new HashSet<QuestionHierarchyDataModel>();
        ParentQuestionsInHierarchy = new HashSet<QuestionHierarchyDataModel>();
    }
    public Guid Id { get; set; }
    public bool IsSubQuestion { get; set; } // 你查询里用到了这个属性，实体里记得补上

    public virtual ICollection<QuestionHierarchyDataModel> ChildQuestionsInHierarchy { get; set; }
    public virtual ICollection<QuestionHierarchyDataModel> ParentQuestionsInHierarchy { get; set; }
}

QuestionHierarchyDataModel 补充父问题导航：

public class QuestionHierarchyDataModel 
{
    public Guid Id { get; set; }
    public Guid ParentQuestionId { get; set; }
    public Guid ChildQuestionId { get; set; }
    public virtual QuestionDataModel ParentQuestion { get; set; } // 新增父问题导航
    public virtual QuestionDataModel ChildQuestion { get; set; }
}

然后在DbContext的OnModelCreating里配置关联关系，避免歧义：

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // 父问题 -> 子层级关联
    modelBuilder.Entity<QuestionHierarchyDataModel>()
        .HasOne(h => h.ParentQuestion)
        .WithMany(q => q.ChildQuestionsInHierarchy)
        .HasForeignKey(h => h.ParentQuestionId)
        .OnDelete(DeleteBehavior.Restrict);

    // 子问题 -> 父层级关联
    modelBuilder.Entity<QuestionHierarchyDataModel>()
        .HasOne(h => h.ChildQuestion)
        .WithMany(q => q.ParentQuestionsInHierarchy)
        .HasForeignKey(h => h.ChildQuestionId)
        .OnDelete(DeleteBehavior.Restrict);
}

第二步：实现多层嵌套查询 #

这里给你两种方案，根据你的EF Core版本和需求选择：

方案1：EF Core 5+ 用LINQ递归查询（类型安全）

EF Core 5及以上支持LINQ递归查询，会自动生成递归CTE的SQL，完全不用写原生SQL：

首先定义一个嵌套模型来承载结果：

public class QuestionWithHierarchy
{
    public QuestionDataModel Question { get; set; }
    public List<QuestionWithHierarchy> Children { get; set; } = new List<QuestionWithHierarchy>();
}

然后编写递归LINQ查询：

// 定义递归方法（方法语法更直观）
IQueryable<QuestionWithHierarchy> GetHierarchy(Guid? parentId = null)
{
    var query = from q in context.Questions
                where (parentId == null && !q.IsSubQuestion) || 
                      (parentId != null && q.ParentQuestionsInHierarchy.Any(h => h.ParentQuestionId == parentId))
                select new QuestionWithHierarchy
                {
                    Question = q,
                    Children = GetHierarchy(q.Id).ToList()
                };
    return query;
}

// 获取所有根节点及其多层子节点
var fullHierarchy = GetHierarchy().ToList();

这个查询会生成单次递归CTE的SQL，直接从数据库获取所有层级数据，然后自动映射成嵌套结构。

方案2：原生SQL递归CTE（兼容低版本EF Core）

如果你的EF Core版本低于5，或者需要更复杂的自定义逻辑，可以直接写递归CTE的SQL，然后在内存中构建嵌套结构：

首先编写SQL：

WITH RecursiveQuestions AS (
    -- 锚点：根节点（非子问题）
    SELECT 
        q.Id AS QuestionId,
        q.*,
        h.ParentQuestionId,
        1 AS HierarchyLevel
    FROM Questions q
    LEFT JOIN QuestionHierarchies h ON q.Id = h.ChildQuestionId
    WHERE q.IsSubQuestion = 0

    UNION ALL

    -- 递归：获取子节点
    SELECT 
        q.Id AS QuestionId,
        q.*,
        h.ParentQuestionId,
        rq.HierarchyLevel + 1 AS HierarchyLevel
    FROM Questions q
    JOIN QuestionHierarchies h ON q.Id = h.ChildQuestionId
    JOIN RecursiveQuestions rq ON h.ParentQuestionId = rq.QuestionId
)
SELECT * FROM RecursiveQuestions
ORDER BY HierarchyLevel, ParentQuestionId

然后在EF Core中执行并构建嵌套结构：

// 执行SQL获取扁平化的层级数据
var flatData = context.Questions
    .FromSqlRaw(@"
        WITH RecursiveQuestions AS (
            SELECT 
                q.Id AS QuestionId,
                q.*,
                h.ParentQuestionId,
                1 AS HierarchyLevel
            FROM Questions q
            LEFT JOIN QuestionHierarchies h ON q.Id = h.ChildQuestionId
            WHERE q.IsSubQuestion = 0

            UNION ALL

            SELECT 
                q.Id AS QuestionId,
                q.*,
                h.ParentQuestionId,
                rq.HierarchyLevel + 1 AS HierarchyLevel
            FROM Questions q
            JOIN QuestionHierarchies h ON q.Id = h.ChildQuestionId
            JOIN RecursiveQuestions rq ON h.ParentQuestionId = rq.QuestionId
        )
        SELECT * FROM RecursiveQuestions
        ORDER BY HierarchyLevel, ParentQuestionId
    ")
    .Select(q => new 
    {
        Question = q,
        ParentQuestionId = (Guid?)q.ParentQuestionsInHierarchy.FirstOrDefault()?.ParentQuestionId
    })
    .ToList();

// 用字典快速查找节点，构建嵌套结构
var nodeDict = new Dictionary<Guid, QuestionWithHierarchy>();
var rootNodes = new List<QuestionWithHierarchy>();

foreach (var item in flatData)
{
    var node = new QuestionWithHierarchy { Question = item.Question };
    nodeDict[item.Question.Id] = node;

    if (!item.ParentQuestionId.HasValue || !nodeDict.ContainsKey(item.ParentQuestionId.Value))
    {
        rootNodes.Add(node);
    }
    else
    {
        nodeDict[item.ParentQuestionId.Value].Children.Add(node);
    }
}

// rootNodes就是包含所有层级的嵌套结构

为什么这是最优方案？ #

• 单次SQL查询：不管哪种方案，都是一次数据库交互，避免了多次查询的性能开销；
• 数据库层面递归：递归CTE是数据库原生支持的高效递归方式，比在应用层多次查询快得多；
• 灵活性：两种方案分别适配了类型安全和自定义需求，覆盖不同场景。

对比你原来的查询，这种方式能一次性获取所有层级的子问题，完美解决你的需求。

内容的提问来源于stack exchange，提问作者K.Z