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

Question

提问by TCM

I've written this code to project one to many relation but it's not working:

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

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);
   }
}

Can anybody spot the mistake?

任何人都可以发现错误吗？

EDIT:

编辑：

These are my entities:

这些是我的实体：

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>();
    }
}

EDIT:

编辑：

I change the query to:

我将查询更改为：

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");

and I get rid of exceptions! However, Employees are not mapped at all. I am still not sure what problem it had with IEnumerable<Employee>in first query.

我摆脱了异常！但是，根本没有映射员工。我仍然不确定它IEnumerable<Employee>在第一次查询中遇到了什么问题。

Answer 1

采纳答案by Contango

This post shows how to query a highly normalised SQL database, and map the result into a set of highly nested C# POCO objects.

这篇文章展示了如何查询高度规范化的 SQL 数据库，并将结果映射到一组高度嵌套的 C# POCO 对象中。

Ingredients:

原料：

8 lines of C#.
Some reasonably simple SQL that uses some joins.
Two awesome libraries.

8 行 C#。
一些使用一些连接的相当简单的 SQL。
两个很棒的图书馆。

The insight that allowed me to solve this problem is to separate the MicroORMfrom mapping the result back to the POCO Entities. Thus, we use two separate libraries:

这让我解决这个问题的见解是分开MicroORM的mapping the result back to the POCO Entities。因此，我们使用两个独立的库：

Dapperas the MicroORM.
Slapper.Automapperfor mapping.

作为 MicroORM 的Dapper。
Slapper.Automapper用于映射。

Essentially, we use Dapperto query the database, then use Slapper.Automapperto map the result straight into our POCOs.

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

Advantages

好处

Simplicity. Its less than 8 lines of code. I find this a lot easier to understand, debug, and change.
Less code. A few lines of code is all Slapper.Automapperneeds to handle anything you throw at it, even if we have a complex nested POCO (i.e. POCO contains List<MyClass1>which in turn contains List<MySubClass2>, etc).
Speed. Both of these libraries have an extraordinary amount of optimization and caching to make them run almost as fast as hand tuned ADO.NET queries.
Separation of concerns. We can change the MicroORM for a different one, and the mapping still works, and vice-versa.
Flexibility. Slapper.Automapperhandles arbitrarily nested hierarchies, it isn't limited to a couple of levels of nesting. We can easily make rapid changes, and everything will still work.
Debugging. We can first see that the SQL query is working properly, then we can check that the SQL query result is properly mapped back to the target POCO Entities.
Ease of development in SQL. I find that creating flattened queries with inner joinsto return flat results is much easier than creating multiple select statements, with stitching on the client side.
Optimized queries in SQL. In a highly normalized database, creating a flat query allows the SQL engine to apply advanced optimizations to the whole which would not normally be possible if many small individual queries were constructed and run.
Trust. Dapper is the back end for StackOverflow, and, well, Randy Burden is a bit of a superstar. Need I say any more?
Speed of development.I was able to do some extraordinarily complex queries, with many levels of nesting, and the dev time was quite low.
Fewer bugs.I wrote it once, it just worked, and this technique is now helping to power a FTSE company. There was so little code that there was no unexpected behavior.

简单。它的代码不到 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 是一位超级巨星。还需要我多说吗？
发展速度。我能够执行一些非常复杂的查询，具有许多嵌套级别，并且开发时间非常短。
更少的错误。我写过一次，它刚刚奏效，现在这项技术正在帮助推动一家富时公司。代码很少，没有意外的行为。

Disadvantages

缺点

Scaling beyond 1,000,000 rows returned.Works well when returning < 100,000 rows. However, if we are bringing back >1,000,000 rows, in order to reduce the traffic between us and SQL server, we should not flatten it out using inner join(which brings back duplicates), we should instead use multiple selectstatements and stitch everything back together on the client side (see the other answers on this page).
This technique is query oriented. I haven't used this technique to write to the database, but I'm sure that Dapper is more than capable of doing this with some more extra work, as StackOverflow itself uses Dapper as its Data Access Layer (DAL).

缩放超过 1,000,000 行返回。返回 < 100,000 行时效果很好。但是，如果我们要带回 > 1,000,000 行，为了减少我们和 SQL 服务器之间的流量，我们不应该使用inner join（这会带回重复项）将其展平，而是应该使用多个select语句并将所有内容重新拼接在一起客户端（请参阅本页上的其他答案）。
这种技术是面向查询的。我还没有使用这种技术来写入数据库，但我确信 Dapper 完全有能力通过一些额外的工作来做到这一点，因为 StackOverflow 本身使用 Dapper 作为其数据访问层 (DAL)。

Performance Testing

性能测试

In my tests, Slapper.Automapperadded a small overhead to the results returned by Dapper, which meant that it was still 10x faster than Entity Framework, and the combination is still pretty darn close to the theoretical maximum speed SQL + C# is capable of.

在我的测试中，Slapper.Automapper为 Dapper 返回的结果增加了一个小开销，这意味着它仍然比 Entity Framework 快 10 倍，并且组合仍然非常接近 SQL + C# 能够达到的理论最大速度。

In most practical cases, most of the overhead would be in a less-than-optimum SQL query, and not with some mapping of the results on the C# side.

在大多数实际情况下，大部分开销都将出现在不太理想的 SQL 查询中，而不是在 C# 端进行某些结果映射。

Performance Testing Results

性能测试结果

Total number of iterations: 1000

总迭代次数：1000

Dapper by itself: 1.889milliseconds per query, using 3 lines of code to return the dynamic.
Dapper + Slapper.Automapper: 2.463milliseconds per query, using an additional 3 lines of code for the query + mapping from dynamic to POCO Entities.

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.

Worked Example

工作示例

In this example, we have list of Contacts, and each Contactcan have one or more phone numbers.

在这个例子中，我们有的列表Contacts，每个列表Contact可以有一个或多个phone numbers。

POCO Entities

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 Table `TestContact`

SQL表 `TestContact`

enter image description here

在此处输入图片说明

SQL Table `TestPhone`

SQL表 `TestPhone`

Note that this table has a foreign key ContactIDwhich refers to the TestContacttable (this corresponds to the List<TestPhone>in the POCO above).

请注意，该表有一个ContactID引用该表的外键TestContact（这对应于List<TestPhone>上面的 POCO 中的）。

enter image description here

在此处输入图片说明

SQL Which Produces Flat Result

产生扁平结果的 SQL

In our SQL query, we use as many JOINstatements as we need to get all of the data we need, in a flat, denormalized form. Yes, this might produce duplicates in the output, but these duplicates will be eliminated automatically when we use Slapper.Automapperto automatically map the result of this query straight into our POCO object map.

在我们的 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

enter image description here

在此处输入图片说明

C# code

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);   
            }
        }
    }
}

Output

输出

enter image description here

在此处输入图片说明

POCO Entity Hierarchy

POCO 实体层次结构

Looking in Visual Studio, We can see that Slapper.Automapper has properly populated our POCO Entities, i.e. we have a List<TestContact>, and each TestContacthas a List<TestPhone>.

在 Visual Studio 中查看，我们可以看到 Slapper.Automapper 已经正确填充了我们的 POCO 实体，即我们有一个List<TestContact>，每个TestContact都有一个List<TestPhone>.

enter image description here

在此处输入图片说明

Notes

笔记

Both Dapper and Slapper.Automapper cache everything internally for speed. If you run into memory issues (very unlikely), ensure that you occasionally clear the cache for both of them.

Dapper 和 Slapper.Automapper 都在内部缓存所有内容以提高速度。如果遇到内存问题（不太可能），请确保偶尔清除它们的缓存。

Ensure that you name the columns coming back, using the underscore (_) notationto give Slapper.Automapper clues on how to map the result into the POCO Entities.

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

Ensure that you give Slapper.Automapper clues on the primary key for each POCO Entity (see the lines Slapper.AutoMapper.Configuration.AddIdentifiers). You can also use Attributeson the POCO for this. If you skip this step, then it could go wrong (in theory), as Slapper.Automapper would not know how to do the mapping properly.

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

Update 2015-06-14

更新 2015-06-14

Successfully applied this technique to a huge production database with over 40 normalized tables. It worked perfectly to map an advanced SQL query with over 16 inner joinand left joininto the proper POCO hierarchy (with 4 levels of nesting). The queries are blindingly fast, almost as fast as hand coding it in ADO.NET (it was typically 52 milliseconds for the query, and 50 milliseconds for the mapping from the flat result into the POCO hierarchy). This is really nothing revolutionary, but it sure beats Entity Framework for speed and ease of use, especially if all we are doing is running queries.

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

Update 2016-02-19

更新 2016-02-19

Code has been running flawlessly in production for 9 months. The latest version of Slapper.Automapperhas all of the changes that I applied to fix the issue related to nulls being returned in the SQL query.

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

Update 2017-02-20

更新 2017-02-20

Code has been running flawlessly in production for 21 months, and has handled continuous queries from hundreds of users in a FTSE 250 company.

代码在生产环境中完美运行了 21 个月，并处理了来自 FTSE 250 公司的数百名用户的持续查询。

Slapper.Automapperis also great for mapping a .csv file straight into a list of POCOs. Read the .csv file into a list of IDictionary, then map it straight into the target list of POCOs. The only trick is that you have to add a propery int Id {get; set}, and make sure it's unique for every row (or else the automapper won't be able to distinguish between the rows).

Slapper.Automapper也非常适合将 .csv 文件直接映射到 POCO 列表中。将 .csv 文件读入 IDictionary 列表，然后将其直接映射到 POCO 的目标列表中。唯一的技巧是您必须添加一个属性int Id {get; set}，并确保它对每一行都是唯一的（否则自动映射器将无法区分行）。

Update 2019-01-29

更新 2019-01-29

Minor update to add more code comments.

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

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

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

Answer 2

回答by Damir Arh

According to this answerthere is no one to many mapping support built into Dapper.Net. Queries will always return one object per database row. There is an alternative solution included, though.

根据这个答案，Dapper.Net 中没有内置的一对多映射支持。查询将始终为每个数据库行返回一个对象。不过，有一个替代解决方案。

Answer 3

回答by Andrew Bullock

Here is a crude workaround

这是一个粗略的解决方法

    public static IEnumerable<TOne> Query<TOne, TMany>(this IDbConnection cnn, string sql, Func<TOne, IList<TMany>> property, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
    {
        var cache = new Dictionary<int, TOne>();
        cnn.Query<TOne, TMany, TOne>(sql, (one, many) =>
                                            {
                                                if (!cache.ContainsKey(one.GetHashCode()))
                                                    cache.Add(one.GetHashCode(), one);

                                                var localOne = cache[one.GetHashCode()];
                                                var list = property(localOne);
                                                list.Add(many);
                                                return localOne;
                                            }, param as object, transaction, buffered, splitOn, commandTimeout, commandType);
        return cache.Values;
    }

its by no means the most efficient way, but it will get you up and running. I'll try and optimise this when i get a chance.

它绝不是最有效的方式，但它会让您启动并运行。当我有机会时，我会尝试优化它。

use it like this:

像这样使用它：

conn.Query<Product, Store>("sql here", prod => prod.Stores);

bear in mind your objects need to implement GetHashCode, perhaps like this:

请记住，您的对象需要实现GetHashCode，可能是这样的：

    public override int GetHashCode()
    {
        return this.Id.GetHashCode();
    }

Answer 4

回答by Clay

A slight modification of Andrew's answer that utilizes a Func to select the parent key instead of GetHashCode.

对 Andrew 的答案稍作修改，使用 Func 来选择父键而不是GetHashCode。

public static IEnumerable<TParent> QueryParentChild<TParent, TChild, TParentKey>(
    this IDbConnection connection,
    string sql,
    Func<TParent, TParentKey> parentKeySelector,
    Func<TParent, IList<TChild>> childSelector,
    dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null)
{
    Dictionary<TParentKey, TParent> cache = new Dictionary<TParentKey, TParent>();

    connection.Query<TParent, TChild, TParent>(
        sql,
        (parent, child) =>
            {
                if (!cache.ContainsKey(parentKeySelector(parent)))
                {
                    cache.Add(parentKeySelector(parent), parent);
                }

                TParent cachedParent = cache[parentKeySelector(parent)];
                IList<TChild> children = childSelector(cachedParent);
                children.Add(child);
                return cachedParent;
            },
        param as object, transaction, buffered, splitOn, commandTimeout, commandType);

    return cache.Values;
}

Example usage

示例用法

conn.QueryParentChild<Product, Store, int>("sql here", prod => prod.Id, prod => prod.Stores)

Answer 5

回答by Davy

I wanted to keep it as simple as possible, my solution:

我想让它尽可能简单，我的解决方案：

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();
}

I still do one call to the database, and while i now execute 2 queries instead of one, the second query is using a INNER join instead of a less optimal LEFT join.

我仍然对数据库进行一次调用，虽然我现在执行 2 个查询而不是一个查询，但第二个查询使用的是 INNER 连接而不是不太理想的 LEFT 连接。

Answer 6

回答by Exocomp

Here is another method:

这是另一种方法：

Order (one) - OrderDetail (many)

订单（一） - 订单明细（多）

using (var connection = new SqlCeConnection(connectionString))
{           
    var orderDictionary = new Dictionary<int, Order>();

    var list = connection.Query<Order, OrderDetail, Order>(
        sql,
        (order, orderDetail) =>
        {
            Order orderEntry;

            if (!orderDictionary.TryGetValue(order.OrderID, out orderEntry))
            {
                orderEntry = order;
                orderEntry.OrderDetails = new List<OrderDetail>();
                orderDictionary.Add(orderEntry.OrderID, orderEntry);
            }

            orderEntry.OrderDetails.Add(orderDetail);
            return orderEntry;
        },
        splitOn: "OrderDetailID")
    .Distinct()
    .ToList();
}

Source: http://dapper-tutorial.net/result-multi-mapping#example---query-multi-mapping-one-to-many

来源：http: //dapper-tutorial.net/result-multi-mapping#example---query-multi-mapping-one-to-many

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

提问by TCM

采纳答案by Contango

Advantages

好处

Disadvantages

缺点

Performance Testing

性能测试

Performance Testing Results

性能测试结果

Worked Example

工作示例

POCO Entities

POCO实体

SQL Table TestContact

SQL表 TestContact

SQL Table TestPhone

SQL表 TestPhone

SQL Which Produces Flat Result

产生扁平结果的 SQL

C# code

C# 代码

Output

输出

POCO Entity Hierarchy

POCO 实体层次结构

Notes

笔记

Update 2015-06-14

更新 2015-06-14

Update 2016-02-19

更新 2016-02-19

Update 2017-02-20

更新 2017-02-20

Update 2019-01-29

更新 2019-01-29

回答by Damir Arh

回答by Andrew Bullock

回答by Clay

回答by Davy

回答by Exocomp

相关推荐

无法在 C# 中加载 DLL

C# 创建和连接到 SQL Server 数据库的代码：它有什么问题？

C# 将 OPC UA 服务器添加到 .NET 应用程序

C# 如何清除组合框？

相关推荐

最近更新

标签

SQL Table `TestContact`

SQL表 `TestContact`

SQL Table `TestPhone`

SQL表 `TestPhone`