c# - 如何在 Dapper.Net 中编写一对多查询？

Question

我已经编写了这段代码来投影一对多关系，但它不起作用:

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> 有什么问题在第一个查询中。

Answer 1

这篇文章展示了如何查询 highly normalised SQL database ，并将结果映射到一组高度嵌套的 C# POCO 对象中。

成分:

8 行 C#。

一些使用一些连接的相当简单的 SQL。

两个很棒的图书馆。

让我解决这个问题的洞察力是将 MicroORM 分开来自 mapping the result back to the POCO Entities .因此，我们使用两个独立的库:

Dapper作为 MicroORM。

Slapper.Automapper用于映射。

本质上，我们使用 Dapper查询数据库，然后使用 Slapper.Automapper将结果直接映射到我们的 POCO。

优势

简单 .它的代码不到 8 行。我发现这更容易理解、调试和更改。

少码 .几行代码就是Slapper.Automapper需要处理你扔给它的任何东西，即使我们有一个复杂的嵌套 POCO(即 POCO 包含 List<MyClass1>，而后者又包含 List<MySubClass2> 等)。

速度 .这两个库都进行了大量优化和缓存，使它们的运行速度几乎与手动调整的 ADO.NET 查询一样快。

关注点分离 .我们可以为不同的 MicroORM 更改 MicroORM，映射仍然有效，反之亦然。

灵活性 . Slapper.Automapper处理任意嵌套的层次结构，它不限于几个级别的嵌套。我们可以轻松地进行快速更改，一切仍然有效。

调试 .我们首先可以看到 SQL 查询是否正常工作，然后我们可以检查 SQL 查询结果是否正确映射回目标 POCO 实体。

易于在 SQL 中开发 .我发现使用 inner joins 创建扁平化查询返回平面结果比创建多个选择语句容易得多，在客户端拼接。

SQL 中的优化查询 .在高度规范化的数据库中，创建平面查询允许 SQL 引擎对整体应用高级优化，如果构建和运行许多小的单个查询，这通常是不可能的。

信任 . Dapper 是 StackOverflow 的后端，而且，Randy Burden 是一位 super 巨星。还需要我多说吗？

发展速度。 我能够执行一些非常复杂的查询，具有许多嵌套级别，并且开发时间非常短。

更少的错误。 我写过一次，它刚刚奏效，现在这项技术正在帮助插入一家富时公司。代码很少，没有意外的行为。

缺点

缩放超过 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
注意这个表有一个外键 ContactID这是指 TestContact表(这对应于上面 POCO 中的 List<TestPhone>)。



产生扁平结果的 SQL

在我们的 SQL 查询中，我们使用了尽可能多的 JOIN语句，因为我们需要获取我们需要的所有数据，在 flat, denormalized form 中.是的，这可能会在输出中产生重复，但是当我们使用 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 都在内部缓存所有内容以提高速度。如果遇到内存问题(不太可能)，请确保偶尔清除它们的缓存。

确保使用 underscore ( _ ) notation 命名返回的列为 Slapper.Automapper 提供有关如何将结果映射到 POCO 实体的线索。

确保为每个 POCO 实体的主键提供 Slapper.Automapper 线索(请参阅行 Slapper.AutoMapper.Configuration.AddIdentifiers)。您也可以使用 Attributes在 POCO 上为此。如果你跳过这一步，那么它可能会出错(理论上)，因为 Slapper.Automapper 不知道如何正确地进行映射。

更新 2015-06-14

成功地将此技术应用于具有 40 多个规范化表的庞大生产数据库。它完美地映射了超过 16 个的高级 SQL 查询 inner join和 left join进入适当的 POCO 层次结构(具有 4 级嵌套)。查询速度非常快，几乎和在 ADO.NET 中手动编码一样快(查询通常需要 52 毫秒，而从平面结果到 POCO 层次结构的映射需要 50 毫秒)。这真的没什么革命性的，但它确实在速度和易用性方面胜过 Entity Framework ，特别是如果我们所做的只是运行查询。

更新 2016-02-19

代码已经在生产环境中完美运行了 9 个月。最新版 Slapper.Automapper具有我应用的所有更改来解决与 SQL 查询中返回的空值相关的问题。

更新 2017-02-20

代码在生产环境中完美运行了 21 个月，并处理了来自 FTSE 250 公司的数百名用户的持续查询。
Slapper.Automapper也非常适合将 .csv 文件直接映射到 POCO 列表中。将 .csv 文件读入 IDictionary 列表，然后将其直接映射到 POCO 的目标列表中。唯一的技巧是你必须添加一个属性 int Id {get; set} ，并确保它对每一行都是唯一的(否则自动映射器将无法区分行)。

更新 2019-01-29

小更新以添加更多代码注释。

见: https://github.com/SlapperAutoMapper/Slapper.AutoMapper