如何在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合法吗? 是的,了解原因