如何用Dapper执行原生SQL并映射1对多关联的C#实体类？

需要通过原生SQL结合Dapper从一对多关系的表中获取结果集，将Category数据正确映射到Supplier实体的Categories集合中，但当前代码未得到预期结果。

实体类定义 #

Supplier类 #

public class Supplier: IId
{
    public int Id { get; set; }
    public string Ref{ get; set; }
    public DateTime CreatedOn { get; set; }
    public List<Category> Categories{ get; set; }
    public int? Ticket { get; set; }
}

Category类 #

public class Category: IId
{
    public int Id { get; set; }
    public int SupplierId{ get; set; }
    [JsonIgnore] public Supplier Supplier{ get; set; }
    public Type Type { get; set; }
    public Status Status { get; private set; }
    public string CompletedBy { get; private set; }
    public string CreatedBy { get; private set; }
    public DateTime? CreatedOn { get; private set; }
    public int? Ticket { get; set; }
}

当前实现代码 #

var reference = 5055;

var sql = "SELECT S.Ref,S.CreatedOn,S.Ticket" +
          "       C.TaskId AS 'Id', C.SupplierId, C.Type, C.Status, C.CompletedBy, C.CreatedBy, C.CreatedOn, C.Ticket " + 
          "FROM Category C " +
          "INNER JOIN Supplier S ON S.SupplierId = C.SupplierId " +
          "WHERE S.Ref = @reference ";

 var parameters = new {reference};

 var suppliers = connection.QueryAsync<Supplier, List<Category>, Supplier>(sql, (supplier,category)   => {
                    supplier.Categories = category;
                    return supplier;
                }, parameters, splitOn: "Id" );

var result = suppliers.Result.OrderByDescending(x => x.CreatedOn).FirstOrDefault();

一、现有Dapper代码问题修复 #

1. SQL语句错误 #

• 字段拼接缺少逗号：S.Ticket后未加逗号，导致SQL语法错误，修正后SQL：

  SELECT S.Id, S.Ref, S.CreatedOn, S.Ticket,
         C.TaskId AS 'Id', C.SupplierId, C.Type, C.Status, C.CompletedBy, C.CreatedBy, C.CreatedOn, C.Ticket 
  FROM Category C 
  INNER JOIN Supplier S ON S.Id = C.SupplierId 
  WHERE S.Ref = @reference 
  

• 关联条件错误：Supplier主键是Id，不是SupplierId，关联条件需改为S.Id = C.SupplierId。
• 缺失Supplier.Id字段：Dapper需要通过主键分组关联数据，必须在SELECT中包含S.Id。

2. Dapper映射逻辑错误 #

Dapper的QueryAsync不支持直接映射到List<Category>作为泛型参数，需通过字典分组聚合单个Category对象：

var supplierDict = new Dictionary<int, Supplier>();

var suppliers = connection.QueryAsync<Supplier, Category, Supplier>(
    sql,
    (supplier, category) =>
    {
        if (!supplierDict.TryGetValue(supplier.Id, out var existingSupplier))
        {
            existingSupplier = supplier;
            existingSupplier.Categories = new List<Category>();
            supplierDict.Add(existingSupplier.Id, existingSupplier);
        }
        existingSupplier.Categories.Add(category);
        return existingSupplier;
    },
    parameters,
    splitOn: "Id"
).Result;

var result = supplierDict.Values.OrderByDescending(x => x.CreatedOn).FirstOrDefault();

原理：SQL返回的是扁平化结果集（每个Supplier-Category对应一行），通过字典按Supplier.Id分组，将同一供应商的多个分类合并到集合中。

二、替代实现方案 #

1. 使用SQL视图 #

创建视图预定义关联查询，简化后续Dapper调用：

CREATE VIEW vw_SupplierWithCategories
AS
SELECT 
    S.Id AS SupplierId,
    S.Ref,
    S.CreatedOn AS SupplierCreatedOn,
    S.Ticket AS SupplierTicket,
    C.Id AS CategoryId,
    C.SupplierId,
    C.Type,
    C.Status,
    C.CompletedBy,
    C.CreatedBy,
    C.CreatedOn AS CategoryCreatedOn,
    C.Ticket AS CategoryTicket
FROM Supplier S
LEFT JOIN Category C ON S.Id = C.SupplierId

查询视图后，仍需使用上述字典分组逻辑映射实体。

2. 使用存储过程 #

创建存储过程封装查询逻辑，支持复杂业务扩展：

CREATE PROCEDURE GetSupplierWithCategories
    @reference INT
AS
BEGIN
    SELECT 
        S.Id,
        S.Ref,
        S.CreatedOn,
        S.Ticket,
        C.Id,
        C.SupplierId,
        C.Type,
        C.Status,
        C.CompletedBy,
        C.CreatedBy,
        C.CreatedOn,
        C.Ticket
    FROM Supplier S
    LEFT JOIN Category C ON S.Id = C.SupplierId
    WHERE S.Ref = @reference
END

调用存储过程的Dapper代码：

var supplierDict = new Dictionary<int, Supplier>();

var suppliers = connection.QueryAsync<Supplier, Category, Supplier>(
    "GetSupplierWithCategories",
    (supplier, category) =>
    {
        if (!supplierDict.TryGetValue(supplier.Id, out var existingSupplier))
        {
            existingSupplier = supplier;
            existingSupplier.Categories = new List<Category>();
            supplierDict.Add(existingSupplier.Id, existingSupplier);
        }
        if (category != null)
        {
            existingSupplier.Categories.Add(category);
        }
        return existingSupplier;
    },
    parameters,
    commandType: CommandType.StoredProcedure,
    splitOn: "Id"
).Result;

var result = supplierDict.Values.OrderByDescending(x => x.CreatedOn).FirstOrDefault();

3. 两次查询（简单高效） #

数据量不大时，可分两次查询手动关联，逻辑更清晰：

// 第一步：查询目标供应商
var supplier = connection.QueryFirstOrDefaultAsync<Supplier>(
    "SELECT Id, Ref, CreatedOn, Ticket FROM Supplier WHERE Ref = @reference",
    parameters
).Result;

if (supplier != null)
{
    // 第二步：查询该供应商的所有分类
    supplier.Categories = connection.QueryAsync<Category>(
        "SELECT Id, SupplierId, Type, Status, CompletedBy, CreatedBy, CreatedOn, Ticket FROM Category WHERE SupplierId = @supplierId",
        new { supplierId = supplier.Id }
    ).Result.ToList();
}

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