如何在C#中通过SQL数据库高效返回父级及子级集合

你当前的实现属于典型的N+1查询问题：先查所有父数据，再循环每个父级查询子数据，数据库调用次数等于父级数量+1，数据量大时性能会急剧下降。以下是几种高效优化方案，按推荐优先级排序：

方案1：使用Entity Framework导航属性预加载（最简洁高效） #

如果你的项目基于Entity Framework（EF Core/EF6），直接利用EF的Include方法实现预加载，EF会自动生成优化的JOIN查询，仅需一次数据库调用即可获取所有父子数据。

前提：确保实体类关联配置正确 #

在Parent类的Childrens属性上配置导航关系（可通过数据注解或Fluent API）：

// Parent.cs
public int Id { get; set; }
public string Name { get; set; }
// 用数据注解配置反向关联
[InverseProperty(nameof(Child.ParentId))]
public List<Child> Childrens { get; set; } = new List<Child>(); // 初始化避免空引用

// Child.cs
public int Id { get; set; }
public string Name { get; set; }
public int ParentId { get; set; }
// 可选：添加父实体导航属性（如果需要双向关联）
// public Parent Parent { get; set; }

调用代码 #

var parents = myContext.Parents
    .Include(p => p.Childrens) // 预加载子表数据
    .ToList();

EF会自动生成类似SELECT * FROM Parents LEFT JOIN Childrens ON Parents.Id = Childrens.ParentId的查询，一次性拉取所有数据并自动映射到实体类的导航属性中。

方案2：一次SQL查询拉取所有数据，内存中关联 #

如果不使用EF导航属性，或需要手写SQL控制查询逻辑，可以一次性拉取所有父、子数据，再在内存中分组关联，仅需一次数据库调用。

方式A：通过JOIN查询返回合并数据，再分组映射 #

先定义一个中间DTO用于接收JOIN后的结果：

public class ParentChildDto
{
    public int ParentId { get; set; }
    public string ParentName { get; set; }
    public int? ChildId { get; set; }
    public string ChildName { get; set; }
}

然后执行JOIN查询并分组构建实体：

var rawData = myContext.Database.SqlQuery<ParentChildDto>(@"
    SELECT 
        p.Id AS ParentId, 
        p.Name AS ParentName, 
        c.Id AS ChildId, 
        c.Name AS ChildName
    FROM Parents p
    LEFT JOIN Childrens c ON p.Id = c.ParentId
").ToList();

// 内存中分组构建Parent实体
var parents = rawData
    .GroupBy(dto => dto.ParentId)
    .Select(g => new Parent
    {
        Id = g.Key,
        Name = g.First().ParentName,
        Childrens = g.Where(dto => dto.ChildId.HasValue)
                     .Select(dto => new Child
                     {
                         Id = dto.ChildId.Value,
                         Name = dto.ChildName,
                         ParentId = g.Key
                     }).ToList()
    }).ToList();

方式B：返回两个独立结果集（更清晰） #

通过一次SQL返回父表和子表两个结果集，再在内存中关联：

using (var command = myContext.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = @"
        SELECT * FROM Parents;
        SELECT * FROM Childrens;
    ";
    myContext.Database.OpenConnection();
    using (var reader = command.ExecuteReader())
    {
        // 读取父表数据
        var parents = new List<Parent>();
        while (reader.Read())
        {
            parents.Add(new Parent
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString()
            });
        }

        // 切换到子表结果集
        reader.NextResult();
        var children = new List<Child>();
        while (reader.Read())
        {
            children.Add(new Child
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString(),
                ParentId = (int)reader["ParentId"]
            });
        }

        // 关联父子数据
        foreach (var parent in parents)
        {
            parent.Childrens = children.Where(c => c.ParentId == parent.Id).ToList();
        }
    }
}

方案3：优化存储过程，返回两个结果集 #

如果你必须使用存储过程，可以修改存储过程一次返回父、子表数据，再通过C#读取多结果集并关联。

修改后的存储过程 #

CREATE PROCEDURE GetParentsWithChildren
AS
BEGIN
    SET NOCOUNT ON;
    SELECT * FROM Parents;
    SELECT * FROM Childrens;
END

C#调用代码 #

using (var command = myContext.Database.GetDbConnection().CreateCommand())
{
    command.CommandText = "GetParentsWithChildren";
    command.CommandType = CommandType.StoredProcedure;
    myContext.Database.OpenConnection();
    
    using (var reader = command.ExecuteReader())
    {
        // 读取父表
        var parents = new List<Parent>();
        while (reader.Read())
        {
            parents.Add(new Parent
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString()
            });
        }

        // 读取子表
        reader.NextResult();
        var children = new List<Child>();
        while (reader.Read())
        {
            children.Add(new Child
            {
                Id = (int)reader["Id"],
                Name = reader["Name"].ToString(),
                ParentId = (int)reader["ParentId"]
            });
        }

        // 关联数据
        foreach (var parent in parents)
        {
            parent.Childrens = children.Where(c => c.ParentId == parent.Id).ToList();
        }
    }
}

总结 #

• 优先选择方案1：利用EF的预加载功能，代码简洁且ORM会自动优化查询，是最省心的高效方案。
• 若需手写SQL，选择方案2的任意一种方式，避免N+1查询，减少数据库往返次数。
• 存储过程场景下使用方案3，确保仅一次数据库调用。

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