Dapper 多表 Object-Relation 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集合都有两条. 

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

https://www.learndapper.com/relationships