如何在Dapper.Net中編寫一對多查詢?

.net c# dapper

我已經編寫了這個代碼來實現一對多的關係,但它不起作用:

using (var connection = new SqlConnection(connectionString))
{
   connection.Open();

   IEnumerable<Store> stores = connection.Query<Store, IEnumerable<Employee>, Store>
                        (@"Select Stores.Id as StoreId, Stores.Name, 
                                  Employees.Id as EmployeeId, Employees.FirstName,
                                  Employees.LastName, Employees.StoreId 
                           from Store Stores 
                           INNER JOIN Employee Employees ON Stores.Id = Employees.StoreId",
                        (a, s) => { a.Employees = s; return a; }, 
                        splitOn: "EmployeeId");

   foreach (var store in stores)
   {
       Console.WriteLine(store.Name);
   }
}

任何人都可以發現錯誤嗎?

編輯:

這些是我的實體:

public class Product
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public double Price { get; set; }
        public IList<Store> Stores { get; set; }

        public Product()
        {
            Stores = new List<Store>();
        }
    }

 public class Store
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public IEnumerable<Product> Products { get; set; }
        public IEnumerable<Employee> Employees { get; set; }

        public Store()
        {
            Products = new List<Product>();
            Employees = new List<Employee>();
        }
    }

編輯:

我將查詢更改為:

            IEnumerable<Store> stores = connection.Query<Store, List<Employee>, Store>
                    (@"Select Stores.Id as StoreId ,Stores.Name,Employees.Id as EmployeeId,Employees.FirstName,
                            Employees.LastName,Employees.StoreId from Store Stores INNER JOIN Employee Employees 
                                ON Stores.Id = Employees.StoreId",
                    (a, s) => { a.Employees = s; return a; }, splitOn: "EmployeeId");

我擺脫了異常!但是,員工根本沒有映射。我仍然不確定在第一次查詢中它與IEnumerable<Employee>有什麼問題。

一般承認的答案

這篇文章展示瞭如何查詢高度規範化的SQL數據庫 ,並將結果映射到一組高度嵌套的C#POCO對象。

配料:

  • 8行C#。
  • 一些使用某些連接的相當簡單的SQL。
  • 兩個很棒的庫。

允許我解決此問題的見解是將MicroORMmapping the result back to the POCO Entities分開。因此,我們使用兩個獨立的庫:

基本上,我們使用Dapper查詢數據庫,然後使用Slapper.Automapper將結果直接映射到我們的POCO中。

好處

  • 簡單 。它不到8行代碼。我發現這更容易理解,調試和更改。
  • 更少的代碼 。幾行代碼都是Slapper.Automapper需要處理你拋出的任何東西,即使我們有一個複雜的嵌套POCO(即POCO包含List<MyClass1> ,而List<MyClass1>又包含List<MySubClass2>等)。
  • 速度 。這兩個庫都有大量的優化和緩存,使它們的運行速度幾乎與手動調整的ADO.NET查詢一樣快。
  • 分離關注點 。我們可以將MicroORM更改為另一個,並且映射仍然有效,反之亦然。
  • 靈活性Slapper.Automapper處理任意嵌套的層次結構,它不僅限於幾個級別的嵌套。我們可以輕鬆地進行快速更改,一切都將繼續。
  • 調試 。我們可以首先看到SQL查詢正常工作,然後我們可以檢查SQL查詢結果是否正確映射回目標POCO實體。
  • SQL的易於開發 。我發現使用inner joins創建扁平化查詢以返回平面結果比創建多個select語句容易得多,在客戶端進行拼接。
  • SQL中的優化查詢 。在高度規範化的數據庫中,創建平面查詢允許SQL引擎將高級優化應用於整體,如果構造並運行許多小的單個查詢,這通常是不可能的。
  • 信任 。 Dapper是StackOverflow的後端,而且,Randy Burden是一個超級巨星。我還需要說嗎?
  • 發展速度。我能夠做一些非常複雜的查詢,有很多級別的嵌套,並且開發時間非常短。
  • 更少的錯誤。我寫了一次,它剛剛起作用,這種技術現在正在幫助富時公司。代碼很少,沒有出現意外行為。

缺點

  • 縮放超過1,000,000行返回。返回<100,000行時效果很好。但是,如果我們帶回> 1,000,000行,為了減少我們和SQL服務器之間的流量,我們不應該使用inner join (它帶回重複)將其展平,我們應該使用多個select語句並將所有內容縫合回來在客戶端一起(參見本頁的其他答案)。
  • 該技術是面向查詢的 。我沒有使用這種技術寫入數據庫,但我確信Dapper能夠通過一些額外的工作來完成這項工作,因為StackOverflow本身使用Dapper作為其數據訪問層(DAL)。

性能測試

在我的測試中, Slapper.Automapper為Dapper返回的結果增加了一個小開銷,這意味著它仍然比Entity Framework快10倍,並且組合仍然非常接近SQL + C#能夠達到的理論最大速度

在大多數實際情況中,大部分開銷都是在一個不太理想的SQL查詢中,而不是在C#端對結果進行一些映射。

性能測試結果

迭代總數:1000

  • Dapper by itself :每個查詢1.889毫秒,使用3 lines of code to return the dynamic
  • Dapper + Slapper.Automapper :每次查詢2.463毫秒,使用額外的3 lines of code for the query + mapping from dynamic to POCO Entities

工作示例

在此示例中,我們有Contacts列表,每個Contact可以有一個或多個phone numbers

POCO實體

public class TestContact
{
    public int ContactID { get; set; }
    public string ContactName { get; set; }
    public List<TestPhone> TestPhones { get; set; }
}

public class TestPhone
{
    public int PhoneId { get; set; }
    public int ContactID { get; set; } // foreign key
    public string Number { get; set; }
}

SQL表TestContact

在此輸入圖像描述

SQL表TestPhone

請注意,此表具有引用TestContact表的外鍵ContactID (這對應於上面POCO中的List<TestPhone> )。

在此輸入圖像描述

產生平面結果的SQL

在我們的SQL查詢中,我們使用盡可能多的JOIN語句來獲取所需的所有數據,採用扁平的非規範化形式 。是的,這可能會在輸出中產生重複,但是當我們使用Slapper.Automapper自動將此查詢的結果直接映射到我們的POCO對像圖中時,這些重複項將自動消除。

USE [MyDatabase];
    SELECT tc.[ContactID] as ContactID
          ,tc.[ContactName] as ContactName
          ,tp.[PhoneId] AS TestPhones_PhoneId
          ,tp.[ContactId] AS TestPhones_ContactId
          ,tp.[Number] AS TestPhones_Number
          FROM TestContact tc
    INNER JOIN TestPhone tp ON tc.ContactId = tp.ContactId

在此輸入圖像描述

C#代碼

const string sql = @"SELECT tc.[ContactID] as ContactID
          ,tc.[ContactName] as ContactName
          ,tp.[PhoneId] AS TestPhones_PhoneId
          ,tp.[ContactId] AS TestPhones_ContactId
          ,tp.[Number] AS TestPhones_Number
          FROM TestContact tc
    INNER JOIN TestPhone tp ON tc.ContactId = tp.ContactId";

string connectionString = // -- Insert SQL connection string here.

using (var conn = new SqlConnection(connectionString))
{
    conn.Open();    
    // Can set default database here with conn.ChangeDatabase(...)
    {
        // Step 1: Use Dapper to return the  flat result as a Dynamic.
        dynamic test = conn.Query<dynamic>(sql);

        // Step 2: Use Slapper.Automapper for mapping to the POCO Entities.
        // - IMPORTANT: Let Slapper.Automapper know how to do the mapping;
        //   let it know the primary key for each POCO.
        // - Must also use underscore notation ("_") to name parameters in the SQL query;
        //   see Slapper.Automapper docs.
        Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(TestContact), new List<string> { "ContactID" });
        Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(TestPhone), new List<string> { "PhoneID" });

        var testContact = (Slapper.AutoMapper.MapDynamic<TestContact>(test) as IEnumerable<TestContact>).ToList();      

        foreach (var c in testContact)
        {                               
            foreach (var p in c.TestPhones)
            {
                Console.Write("ContactName: {0}: Phone: {1}\n", c.ContactName, p.Number);   
            }
        }
    }
}

產量

在此輸入圖像描述

POCO實體層次結構

在Visual Studio中,我們可以看到Slapper.Automapper已經正確填充了我們的POCO實體,即我們有一個List<TestContact> ,每個TestContact都有一個List<TestPhone>

在此輸入圖像描述

筆記

Dapper和Slapper.Automapper都在內部緩存所有內容以提高速度。如果遇到內存問題(非常不可能),請確保偶爾清除這兩個問題的緩存。

確保使用下劃線( _ )表示法命名返回的列,以便為Slapper.Automapper提供有關如何將結果映射到POCO實體的線索。

確保為每個POCO實體的主鍵提供Slapper.Automapper線索(請參閱Slapper.AutoMapper.Configuration.AddIdentifiers行)。您也可以在POCO上使用Attributes 。如果你跳過這一步,那麼它可能會出錯(理論上),因為Slapper.Automapper不會知道如何正確地進行映射。

更新2015-06-14

成功將此技術應用於具有40多個規範化表的龐大生產數據庫。它完美地將高級SQL查詢與超過16個inner joinleft join映射到適當的POCO層次結構(具有4個嵌套級別)。查詢速度非常快,幾乎和在ADO.NET中手動編碼一樣快(查詢通常為52毫秒,從平面結果到POCO層次結構的映射為50毫秒)。這實際上並不是革命性的,但它肯定勝過實體框架的速度和易用性,特別是如果我們所做的只是運行查詢。

更新2016-02-19

代碼在生產中運行了9個月完美無瑕。最新版本的Slapper.Automapper具有我應用的所有更改,以修復與SQL查詢中返回的空值相關的問題。

更新2017-02-20

Code已經在生產中運行了21個月,並且已經處理了FTSE 250公司數百名用戶的持續查詢。

Slapper.Automapper也非常適合將.csv文件直接映射到POCO列表中。將.csv文件讀入IDictionary列表,然後將其直接映射到目標POCO列表中。唯一的技巧是你必須添加一個屬性int Id {get; set} ,並確保它對每一行都是唯一的(否則automapper將無法區分行)。

更新2019-01-29

輕微更新以添加更多代碼註釋。

請參閱: https//github.com/SlapperAutoMapper/Slapper.AutoMapper


熱門答案

我想讓它盡可能簡單,我的解決方案:

public List<ForumMessage> GetForumMessagesByParentId(int parentId)
{
    var sql = @"
    select d.id_data as Id, d.cd_group As GroupId, d.cd_user as UserId, d.tx_login As Login, 
        d.tx_title As Title, d.tx_message As [Message], d.tx_signature As [Signature], d.nm_views As Views, d.nm_replies As Replies, 
        d.dt_created As CreatedDate, d.dt_lastreply As LastReplyDate, d.dt_edited As EditedDate, d.tx_key As [Key]
    from 
        t_data d
    where d.cd_data = @DataId order by id_data asc;

    select d.id_data As DataId, di.id_data_image As DataImageId, di.cd_image As ImageId, i.fl_local As IsLocal
    from 
        t_data d
        inner join T_data_image di on d.id_data = di.cd_data
        inner join T_image i on di.cd_image = i.id_image 
    where d.id_data = @DataId and di.fl_deleted = 0 order by d.id_data asc;";

    var mapper = _conn.QueryMultiple(sql, new { DataId = parentId });
    var messages = mapper.Read<ForumMessage>().ToDictionary(k => k.Id, v => v);
    var images = mapper.Read<ForumMessageImage>().ToList();

    foreach(var imageGroup in images.GroupBy(g => g.DataId))
    {
        messages[imageGroup.Key].Images = imageGroup.ToList();
    }

    return messages.Values.ToList();
}

我仍然對數據庫進行一次調用,而我現在執行2次查詢而不是一次,第二次查詢使用INNER連接而不是最佳的LEFT連接。



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因