如何将SQL查询结果映射到C#嵌套模型

一、SQL查询语句 #

SELECT d.domain_id, d.name, sd.subdomain_id, sd.name, sd.[description] 
FROM [myDB].SubDomains sd INNER JOIN [myDB].Domains d 
ON d.domain_id = sd.domain_id ORDER BY d.[name], sd.[name]

二、查询返回结果 #

domain_id | name | subdomain_id | name | description
1         |  A   |      11      |  AB  | AB description
1         |  A   |      12      |  AC  | AC description
2         |  B   |      21      |  BA  | BA description

三、定义的C#模型 #

// 顶层模型，包含域名列表
public class HigherLevel
{
  public List<DomainModel> Domains { get; set; }
}

// 域名模型，包含子域名列表
public class DomainModel
{
 public int Id { get; set; }
 public string Name { get; set; }
 public List<SubDomainModel> SubDomains { get; set; }
}

// 子域名模型
public class SubDomainModel
{
 public int Id { get; set; }
 public string Name { get; set; }
 public string Description { get; set; }
}

四、期望的API响应格式 #

{
  "higherLevel": {
    "domains": [
      {
        "id": 1,
        "name": "A",
        "subdomains": [
          { "id": 11, "name": "AB", "description": "AB description" },
          { "id": 12, "name": "AC", "description": "AC description" }
        ]
      },
      {
        "id": 2,
        "name": "B",
        "subdomains": [
          { "id": 21, "name": "BA", "description": "BA description" }
        ]
      }
    ]
  }
}

五、映射实现方案 #

方案1：手动遍历+字典分组（高效直观） #

先定义一个临时扁平DTO类，适配SQL查询的字段结构：

// 临时扁平DTO，用于接收SQL单条查询结果
public class FlatDomainSubDomain
{
    public int DomainId { get; set; }
    public string DomainName { get; set; }
    public int SubDomainId { get; set; }
    public string SubDomainName { get; set; }
    public string Description { get; set; }
}

从数据库读取所有扁平结果后，通过字典按domain_id分组构建嵌套模型：

// 假设已从数据库获取扁平结果集合flatResults
var domainDict = new Dictionary<int, DomainModel>();
foreach (var item in flatResults)
{
    // 字典中无当前域名则创建并加入
    if (!domainDict.TryGetValue(item.DomainId, out var domain))
    {
        domain = new DomainModel
        {
            Id = item.DomainId,
            Name = item.DomainName,
            SubDomains = new List<SubDomainModel>()
        };
        domainDict.Add(item.DomainId, domain);
    }
    // 向当前域名的子列表添加子域名
    domain.SubDomains.Add(new SubDomainModel
    {
        Id = item.SubDomainId,
        Name = item.SubDomainName,
        Description = item.Description
    });
}

// 构建最终返回的顶层对象
var higherLevel = new HigherLevel
{
    Domains = domainDict.Values.ToList()
};

方案2：LINQ分组查询（简洁优雅） #

直接用LINQ的GroupBy方法完成分组与模型转换：

// 假设已获取扁平结果集合flatResults
var higherLevel = new HigherLevel
{
    Domains = flatResults
        // 按域名ID和名称分组
        .GroupBy(r => new { r.DomainId, r.DomainName })
        // 将分组转换为DomainModel
        .Select(group => new DomainModel
        {
            Id = group.Key.DomainId,
            Name = group.Key.DomainName,
            // 将组内项转换为SubDomainModel列表
            SubDomains = group.Select(item => new SubDomainModel
            {
                Id = item.SubDomainId,
                Name = item.SubDomainName,
                Description = item.Description
            }).ToList()
        })
        .ToList()
};

注意事项 #

• 你的SQL已按d.name, sd.name排序，两种方案都会保留该排序顺序，最终域名和子域名列表将按名称排序。
• 若使用Entity Framework等ORM，可通过配置实体导航属性让框架自动加载关联数据，无需手动处理扁平结果；执行原生SQL时，上述方案完全适用。
• 模型中string类型属性默认支持空值，无需额外处理SQL返回的NULL值。

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