Dapper 多表 ObjectRelation Mapping

上个博客讲解了SQL参数映射和单表结果的映射, 这篇博客聚焦于多表查询结果的映射.

=====================================

一对一映射

=====================================

以订单和客户为例, 业务对象和后台表正好是对齐的, 两个实体对象分别对应这两个后台表, 而且订单表的客户和客户表是1:1对应关系.

对象模型类:

public class Customer
{
    public string CustomerId { get; set; } = "";
    public string customerName { get; set; } = "";
    public int? Age { get; set; }
}

public  class Order
{
    public string OrderId { get; set; } = "";
    public string OrderDate { get; set; } = "";
    public string Customerid { get; set; } = "";
    public Customer Customer { get; set; } = new Customer();  //关联 Customer 对象
}

后台数据表:

-- 订单表
select 1 OrderId, 'c1' CustomerId, '2021-11-13' OrderDate 
union all 
select 2 OrderId, 'c2' CustomerId, '2021-11-13' OrderDate  
union all
select 3 OrderId, 'c1' CustomerId, '2021-12-13' OrderDate  
union all 
select 4 OrderId, 'c2' CustomerId, '2021-12-13' OrderDate 
;

-- 客户表
select 'c1' CustomerId, 'c1name' customerName,  10 Age 
union all 
select 'c2' CustomerId, 'c2name' customerName,  20 Age 
union all 
select 'c3' CustomerId, 'c3name' customerName,  30 Age 
;

需求: 我们需要通过一次查询DB获取全部的订单对象, 注意每个订单对象中包含了一个Customer对象.

一对一关联查询语句为:

select o.OrderId, o.CustomerId, o.OrderDate,c.CustomerId, c.customerName, c.age from 
(
select 1 OrderId, 'c1' CustomerId, '2021-11-13' OrderDate 
union all 
select 2 OrderId, 'c2' CustomerId, '2021-11-13' OrderDate  
union all
select 3 OrderId, 'c1' CustomerId, '2021-12-13' OrderDate  
union all 
select 4 OrderId, 'c2' CustomerId, '2021-12-13' OrderDate  
) o 
join  
(
select 'c1' CustomerId, 'c1name' customerName,  10 Age 
union all 
select 'c2' CustomerId, 'c2name' customerName,  20 Age 
union all 
select 'c3' CustomerId, 'c3name' customerName,  30 Age 
) c on o.customerId=c.CustomerId 

Dapper Query() 支持从大宽查询结果中一次性地填充多个对象,我们对重载形式做一些解读:

 IEnumerable<TReturn> Query<TFirst, TSecond, TThird, TReturn>(this IDbConnection cnn, string sql, Func<TFirst, TSecond, TThird, TReturn> map, object param = null ) 

  第一个参数是SQL, 第二个参数是对象之间的map匿名函数, 共4个对象, 前三个对象Dapper query()函数能能自动填充, 第四个对象用于告知Dapper返回对象类型是什么.

 这里就出现了一个问题， Dapper是如何将一个宽的结果集分成三个部分，用于初始化前三个对象呢？

答案是， 通过 splitOn 参数来分割宽表结果集，splitOn 参数是要给逗号分隔的字符串， 用于设定分割字段, 比如取值为"SplitOn1,SplitOn2"，

Dapper 会从右到左扫描各个列，最右的分割字段SplitOn2以及右边的所有将用于填充最右边的对象， 然后需要继续向左扫描，碰到前一个splitOn1字段，中间这几个字段将用于第二个对象,  剩余前面字段用于填充第一个字段.

 在Order和 Customer 示例中,  是两个对象之间的关系, 而且是一对一关系,  直接将传入的 customerObj 赋值给 orderObj.Customer属性, 即完成1:1对象绑定

public string SelectTest6()
{ 
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"select o.OrderId, o.CustomerId, o.OrderDate,c.CustomerId, c.customerName, c.Age from 
            (
            select 1 OrderId, 'c1' CustomerId, '2021-11-13' OrderDate 
            union all 
            select 2 OrderId, 'c2' CustomerId, '2021-11-13' OrderDate  
            union all
            select 3 OrderId, 'c1' CustomerId, '2021-12-13' OrderDate  
            union all 
            select 4 OrderId, 'c2' CustomerId, '2021-12-13' OrderDate  
            ) o 
            join  
            (
            select 'c1' CustomerId, 'c1name' customerName,  10 Age 
            union all 
            select 'c2' CustomerId, 'c2name' customerName,  20 Age 
            union all 
            select 'c3' CustomerId, 'c3name' customerName,  30 Age 
            ) c on o.customerId=c.CustomerId 
            ";
        var orderList = conn.Query<Order, Customer, Order>(sql, 
            (orderObj,customerObj)=> {
                orderObj.Customer = customerObj; //直接将传入的 customerObj 赋值给 orderObj.Customer属性, 即完成1:1对象绑定
                return orderObj;
            }, 
            splitOn: "CustomerId");
    }
    return "ok";        
}

检查一对一mapping结果如下:

=====================================

一对多映射

=====================================

订单主表 Order  和 订单明细表 orderLine 是一对多的关系.

对象模型代码:

public  class Order
{
    public string OrderId { get; set; } = "";
    public string OrderDate { get; set; } = "";
    public string Customerid { get; set; } = ""; 
    public List<OrderLine> OrderLines { get; set; } = new List<OrderLine>();
}

public class OrderLine
{
    public string OrderId { get; set; } = "";
    public string OrderLineId { get; set; } = "";
    public string productName { get; set; } = "";
    public int Qty { get; set; } = 0;
}

后台数据表:

--订单主表
select 1 OrderId, 'c1' CustomerId, '2021-11-13' OrderDate 
union all 
select 2 OrderId, 'c2' CustomerId, '2021-11-13' OrderDate  
union all
select 3 OrderId, 'c1' CustomerId, '2021-12-13' OrderDate  
union all 
select 4 OrderId, 'c2' CustomerId, '2021-12-13' OrderDate  
;

--订单明细表
select 1 OrderId, 'line1' OrderLineId, 'prod1' ProductName, 10 Qty
union all 
select 1 OrderId, 'line2' OrderLineId, 'prod2' ProductName, 10 Qty
union all 
select 2 OrderId, 'line3' OrderLineId, 'prod1' ProductName, 10 Qty
union all 
select 2 OrderId, 'line4' OrderLineId, 'prod1' ProductName, 10 Qty
union all 
select 3 OrderId, 'line5' OrderLineId, 'prod1' ProductName, 10 Qty
union all 
select 4 OrderId, 'line6' OrderLineId, 'prod1' ProductName, 10 Qty 
;

两表Join的结果如下:

因为是一对多的关系, 经过join后记录数多余订单主表的数量, 而我们的对象模型是以订单为主要对象,  所以O-R Mapping后要做一个去重处理, 即Order 对象整体还是要求只有4个, 对于orderId为1和2, 其OrderLine集合都有两条. 

C# 代码:

public string SelectTest7()
{
    using (IDbConnection conn = new SqlConnection(_connectionString))
    {
        string sql = @"
        select o.OrderId, o.CustomerId, o.OrderDate,ol.OrderLineId,ol.ProductName, ol.Qty  from 
        (
        select 1 OrderId, 'c1' CustomerId, '2021-11-13' OrderDate 
        union all 
        select 2 OrderId, 'c2' CustomerId, '2021-11-13' OrderDate  
        union all
        select 3 OrderId, 'c1' CustomerId, '2021-12-13' OrderDate  
        union all 
        select 4 OrderId, 'c2' CustomerId, '2021-12-13' OrderDate  
        ) o 
        join 
        (
        select 1 OrderId, 'line1' OrderLineId, 'prod1' ProductName, 10 Qty
        union all 
        select 1 OrderId, 'line2' OrderLineId, 'prod2' ProductName, 10 Qty
        union all 
        select 2 OrderId, 'line3' OrderLineId, 'prod1' ProductName, 10 Qty
        union all 
        select 2 OrderId, 'line4' OrderLineId, 'prod1' ProductName, 10 Qty
        union all 
        select 3 OrderId, 'line5' OrderLineId, 'prod1' ProductName, 10 Qty
        union all 
        select 4 OrderId, 'line6' OrderLineId, 'prod1' ProductName, 10 Qty 
        ) ol 
        on o.orderId=ol.OrderId 
            ";

        //构建每个 orderId 唯一的 dictionary, 方便后面 orderList 能按引用去重  
        var uniqueOrderDict = new Dictionary<string, Order>();
        var orderList = conn.Query<Order, OrderLine, Order>(sql,
            (orderObj, orderLineObj) =>
            {
                Order? savedOrderObj = null;
                if (!uniqueOrderDict.TryGetValue(orderObj.OrderId, out savedOrderObj)) 
                {
                    //如果 orderId 不在 uniqueOrderDict 存在, 则加到 uniqueOrderDict 中
                    uniqueOrderDict.Add(orderObj.OrderId, orderObj);
                }
                else {
                    //如果 orderId 已经在 uniqueOrderDict 存在, 则使用之前保存的 order 实例
                    orderObj = savedOrderObj; 
                }


                //确保 OrderObj 下 orderLine 的唯一性 
                if (!orderObj.OrderLines.Any(x => x.OrderLineId == orderLineObj.OrderLineId))
                {   
                    orderObj.OrderLines.Add(orderLineObj);
                }
                return orderObj;
            },
            splitOn: "CustomerId,OrderLineId");

        //orderList有可能有多个相同引用的 Order 实例, 需要去重
        var uniqueOrderList = orderList.Distinct().ToList();
    }
    return "ok";
}

uniqueOrderList 的结果如下, 4个Order 对象, 其中第一个Order 有两个 OrderLine 子对象, 完美实现一对多的情形

=====================================

多对多映射

=====================================

实际中也多对多的情形, 比如 博客 post 和 tag之间的关系, 但视角确定后, 很多时候就转变为一对多情形, 我没有做更进一步实现, 可以参考下面文章. 

https://riptutorial.com/dapper/example/1197/one-to-many-mapping

https://www.learndapper.com/relationships