我怎样写一个在Dapper.Net许多查询？ [英] How do I write one to many query in Dapper.Net?

问题描述

我已经写了这个code项目一对多的关系，但它不工作：

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?

编辑：

这些是我的实体：

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＆LT有什么问题;员工＆GT; 在第一次查询

and I get rid of exceptions! However, Employees are not mapped at all. Can anybody help me with that? I am still not sure what problem it had with IEnumerable<Employee> in 1st query.

推荐答案

这篇文章展示了如何查询一个高度规范化的SQL数据库，并将结果绘制成一组高度嵌套C＃POCO对象。

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

主料：

• 在8行C＃。
• 在一些使用了一些连接相当简单的SQL。
• 在两个真棒库。

这让我解决这个问题的方法是分离 MicroORM 从映射的结果返回给POCO实体。因此，我们使用两个独立的库：

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

• 小巧玲珑作为MicroORM。
• Slapper.Automapper 的映射。

• Dapper as the MicroORM.
• Slapper.Automapper for mapping.

从本质上讲，我们使用小巧玲珑查询数据库，然后用的 Slapper.Automapper 将结果直接映射到我们的波苏斯。

Essentially, we use Dapper to query the database, then use Slapper.Automapper to map the result straight into our POCOs.

• 简单。我觉得这是一个更容易理解，调试和变化。
• 少code 。的code几行是所有 Slapper.Automapper 需要处理任何你在它扔即使我们有一个复杂的POCO的东西可笑像6级嵌套，并且父/子和父/子关系。
• 速度。这两个库具有优化和缓存的数额特别巨大，使它们运行几乎一样快，手调ADO.NET查询。
• 分离关注。我们可以改变MicroORM的不同之一，映射仍然有效，反之亦然。
• 灵活性。 Slapper.Automapper 的处理任意嵌套层次结构，它并不限于几个嵌套级别。我们可以很容易地做出快速的变化，一切都将仍然有效。
• 调试。我们可以先看到动态的，现在正常返回，那么我们可以看到，动态正确映射回目标POCO实体。
• 易于开发的SQL 。我发现，创建扁平化查询与内部联接返回平的结果比创建多个SELECT语句，用缝线在客户端更加容易。
• 优化SQL查询。在一个高度规范化的数据库，创建一台查询允许SQL应用先进优化的整体，它通常不会是可能的，如果很多小个人查询，构建和运行。
• 信托。小巧精致的后端计算器，和好，兰迪负担是有点巨星。需要我多说什么？

• Simplicity. I find this a lot easier to understand, debug, and change.
• Less code. A few lines of code is all Slapper.Automapper needs to handle anything you throw at it, even if we have a complex POCO with something ridiculous like 6 levels of nesting, and Parent/Child and Child/Parent relationships.
• 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.Automapper handles 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 dynamic is returned properly, then we can see that the dynamic is properly mapped back to the target POCO Entities.
• Ease of development in SQL. I find that creating flattened queries with inner joins to 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 SQL 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?

• 缩放超过1,000,000行返回作品以及返回＆LT时。 100,000行。但是，如果我们带回> 1,000,000行，以减少美国和SQL服务器之间的流量，我们不应该用压平内连接（其带回重复），我们应该使用多个选择语句，并在客户端上都缝到一起（见本页上的其他答案）。
• ：该技术是面向查询。我没有带用该技术来存储到数据库中，但我敢肯定，精致小巧超过能够与一些额外的工作，这样做的，因为计算器本身采用小巧精致的它的数据访问层（DAL）。

• 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 select statements and stitch everything back together on the client side (see the other answers on this page).
• This technique is query oriented. I havn'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).

在我的测试中， Slapper.Automapper 增加了一个小的开销由小巧玲珑返回的结果，这意味着它仍然是10倍快于实体框架，和的组合仍然是pretty的织补接近理论最大速度SQL + C＃能够

In my tests, Slapper.Automapper added 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.

在大多数实际情况下，大部分的开销将是一个低于最优的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.

迭代总数：1000

• 小巧玲珑本身： 1.889 每次查询毫秒，使用 3号线的code返回动态。
• 小巧玲珑+ Slapper.Automapper ： 2.463 每次查询毫秒，使用附加 3号线的code对于查询+映射从动态到POCO实体。

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

在这个例子中，我们有联系人，每个联系方式可以拥有一个或多个<$ C列表$ C>电话号码。

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

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

请注意，此表有一个外键的ContactID 这是指 TestContact 表（此相对应的名单，其中，TestPhone＆GT; 在POCO上图）

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

在我们的SQL查询中，我们使用尽可能多的加入语句，我们需要得到我们需要的所有数据，在的持平，非规范化的形式。是的，这可能会产生重复的输出，但这些副本会自动当我们使用被淘汰 Slapper.Automapper 此查询的结果自动映射直接进入我们的POCO对象映射。

In our SQL query, we use as many JOIN statements 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.Automapper to automatically map the result of this query straight into our POCO object map.

USE [Tomahawk];
    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

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();    
    {
        // 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;
        //   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

展望在Visual Studio中，我们可以看到，Slapper.Automapper已经正确地填写我们的POCO实体，比如我们有一个名单，其中，TestContact＆GT; ，每个 TestContact 有一个名单，其中，TestPhone＆GT;

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 TestContact has a List<TestPhone>.

这两个小巧玲珑和Slapper.Automapper缓存内部一切为了速度。如果您遇到内存问题（可能性很小），确保你偶尔清除缓存对他们俩的。

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.

请确保你的名字列回来，使用下划线（ _ ）符号就如何将结果映射到POCO实体Slapper.Automapper线索。

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

请确保您给主键为每个POCO实体Slapper.Automapper线索（见行 Slapper.AutoMapper.Configuration.AddIdentifiers ）。您也可以使用属性的POCO这一点。如果你跳过这一步，那么它可能会出错（理论上），因为Slapper.Automapper不知道该怎么办映射正常。

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 Attributes on 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.

成功应用这个技术，庞大的生产数据库，有超过40规范化的表。它的工作完美地映射了先进的SQL查询有超过16 内加入和左加入到正确的POCO层次结构（带4层嵌套）。该查询是瞬息万变，几乎一样快，手编码它在ADO.NET（这是典型的52毫秒为单位查询，50毫秒的映射从平面结果到POCO的层次结构）。这实在是没有什么革命性的，但它肯定比实体框架的速度和易用性，特别是当所有我们正在做的是运行的查询。

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 join and left join into 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.

这篇关于我怎样写一个在Dapper.Net许多查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！