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集合都有兩條.

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

posted @ 2021-11-30 22:40 harrychinese 閱讀(269) 評論(0) 收藏舉報

刷新頁面返回頂部

harrychinese

Dapper 多表 Object-Relation Mapping

公告