Como faço para escrever um para muitos consulta no Dapper.Net?

Eu escrevi este código para projetar uma relação para muitos, mas não está funcionando:

using (var connection = new SqlConnection(connectionString)) { connection.Open(); IEnumerable stores = connection.Query<Store, IEnumerable, 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); } } 

Alguém consegue identificar o erro?

EDITAR:

Estas são minhas entidades:

 public class Product { public int Id { get; set; } public string Name { get; set; } public double Price { get; set; } public IList Stores { get; set; } public Product() { Stores = new List(); } } public class Store { public int Id { get; set; } public string Name { get; set; } public IEnumerable Products { get; set; } public IEnumerable Employees { get; set; } public Store() { Products = new List(); Employees = new List(); } } 

EDITAR:

Eu mudo a consulta para:

  IEnumerable stores = connection.Query<Store, List, 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"); 

e eu me livrei de exceções! No entanto, os funcionários não são mapeados. Ainda não tenho certeza do problema que teve com IEnumerable na primeira consulta.

Esta postagem mostra como consultar um database SQL altamente normalizado e mapear o resultado em um conjunto de objects C # POCO altamente nesteds.

Ingredientes:

  • 8 linhas de c #.
  • Alguns SQL razoavelmente simples que usa algumas junções.
  • Duas bibliotecas impressionantes.

O insight que me permitiu resolver esse problema é separar a MicroORM de mapping the result back to the POCO Entities . Assim, usamos duas bibliotecas separadas:

  • Dapper como o MicroORM.
  • Slapper.Automapper para mapeamento.

Essencialmente, usamos o Dapper para consultar o database e, em seguida, usamos o Slapper.Automapper para mapear o resultado diretamente em nossos POCOs.

Vantagens

  • Simplicidade Suas menos de 8 linhas de código. Acho isso muito mais fácil de entender, depurar e mudar.
  • Menos código . Algumas linhas de código é todo Slapper.Automapper precisa lidar com qualquer coisa que você jogue, mesmo se nós tivermos um POCO nested complexo (isto é, POCO contém List que por sua vez contém List , etc).
  • Velocidade . Ambas as bibliotecas têm uma quantidade extraordinária de otimização e cache para fazê-las funcionar quase tão rápido quanto as consultas ADO.NET sintonizadas manualmente.
  • Separação de preocupações . Podemos mudar o MicroORM para um diferente, e o mapeamento ainda funciona, e vice-versa.
  • Flexibilidade O Slapper.Automapper manipula hierarquias arbitrariamente aninhadas, não se limitando a alguns níveis de aninhamento. Podemos facilmente fazer mudanças rápidas e tudo ainda funcionará.
  • Depuração . Podemos ver primeiro que a consulta SQL está funcionando corretamente, então podemos verificar se o resultado da consulta SQL está mapeado corretamente de volta para as Entidades POCO de destino.
  • Facilidade de desenvolvimento em SQL . Eu acho que a criação de consultas simplificadas com inner joins para retornar resultados simples é muito mais fácil do que criar várias instruções selecionadas, com pontos no lado do cliente.
  • Consultas otimizadas em SQL . Em um database altamente normalizado, a criação de uma consulta simples permite que o mecanismo SQL aplique otimizações avançadas ao todo, o que normalmente não seria possível se várias consultas individuais pequenas fossem criadas e executadas.
  • Confiança . O Dapper é o back-end do StackOverflow e, bem, o Randy Burden é um superstar. Precisa dizer mais alguma coisa?
  • Velocidade de desenvolvimento. Consegui fazer algumas consultas extraordinariamente complexas, com muitos níveis de aninhamento, e o tempo de desenvolvimento foi bastante baixo.
  • Menos bugs Eu escrevi uma vez, apenas funcionou, e essa técnica agora está ajudando a alimentar uma empresa FTSE. Havia tão pouco código que não havia comportamento inesperado.

Desvantagens

  • Dimensionamento além de 1.000.000 linhas retornadas. Funciona bem ao retornar <100.000 linhas. No entanto, se estivermos trazendo mais de 1.000.000 de linhas, para reduzir o tráfego entre nós e o SQL Server, não devemos achatá-lo usando inner join (que traz de volta duplicatas), devemos usar várias instruções select e inner join tudo de volta juntos no lado do cliente (veja as outras respostas nesta página).
  • Essa técnica é orientada por consulta . Eu não usei essa técnica para gravar no database, mas tenho certeza de que o Dapper é mais do que capaz de fazer isso com algum trabalho extra, já que o próprio StackOverflow usa o Dapper como sua Data Access Layer (DAL).

Teste de performance

Em meus testes, o Slapper.Automapper adicionou uma pequena sobrecarga aos resultados retornados pelo Dapper, o que significa que ele ainda era 10x mais rápido que o Entity Framework, e a combinação ainda é muito próxima da velocidade máxima teórica da qual o SQL + C # é capaz .

Na maioria dos casos práticos, a maior parte da sobrecarga estaria em uma consulta SQL menor do que a ideal, e não com algum mapeamento dos resultados no lado do C #.

Resultados do teste de desempenho

Número total de iterações: 1000

  • Dapper by itself : 1.889 milissegundos por consulta, usando 3 lines of code to return the dynamic .
  • Dapper + Slapper.Automapper : 2.463 milissegundos por consulta, usando um adicional de 3 lines of code for the query + mapping from dynamic to POCO Entities .

Exemplo trabalhado

Neste exemplo, temos uma lista de Contacts e cada Contact pode ter um ou mais phone numbers .

Entidades POCO

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

TestContact Tabela TestContact

insira a descrição da imagem aqui

TestPhone tabela SQL

Observe que essa tabela tem uma chave estrangeira ContactID que se refere à tabela TestContact (isso corresponde à List no POCO acima).

insira a descrição da imagem aqui

SQL que produz resultado plano

Em nossa consulta SQL, usamos tantas instruções JOIN quanto precisamos para obter todos os dados de que precisamos, em um formulário simples e desordenado . Sim, isso pode produzir duplicatas na saída, mas essas duplicatas serão eliminadas automaticamente quando usamos o Slapper.Automapper para mapear automaticamente o resultado dessa consulta diretamente em nosso mapa de objects 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 

insira a descrição da imagem aqui

Código 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(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 { "ContactID" }); Slapper.AutoMapper.Configuration.AddIdentifiers(typeof(TestPhone), new List { "PhoneID" }); var testContact = (Slapper.AutoMapper.MapDynamic(test) as IEnumerable).ToList(); foreach (var c in testContact) { foreach (var p in c.TestPhones) { Console.Write("ContactName: {0}: Phone: {1}\n", c.ContactName, p.Number); } } } } 

Saída

insira a descrição da imagem aqui

Hierarquia de Entidades POCO

Olhando no Visual Studio, podemos ver que o Slapper.Automapper preencheu corretamente nossas Entidades POCO, ou seja, temos uma List e cada TestContact tem uma List .

insira a descrição da imagem aqui

Notas

O Dapper e o Slapper.Automapper armazenam tudo internamente para velocidade. Se você se deparar com problemas de memory (muito improvável), assegure-se de limpar ocasionalmente o cache para os dois.

Certifique-se de nomear as colunas retornando, usando a notação de sublinhado ( _ ) para fornecer pistas ao Slapper.Automapper sobre como mapear o resultado para as Entidades POCO.

Certifique-se de fornecer dicas do Slapper.Automapper na chave primária para cada Entidade POCO (consulte as linhas Slapper.AutoMapper.Configuration.AddIdentifiers ). Você também pode usar Attributes no POCO para isso. Se você pular este passo, então pode dar errado (em teoria), já que o Slapper.Automapper não saberia como fazer o mapeamento corretamente.

Atualizar 2015-06-14

Apliquei essa técnica com sucesso a um enorme database de produção com mais de 40 tabelas normalizadas. Ele funcionou perfeitamente para mapear uma consulta SQL avançada com mais de 16 inner join e left join à left join na hierarquia POCO adequada (com 4 níveis de aninhamento). As consultas são incrivelmente rápidas, quase tão rápidas quanto a codificação manual no ADO.NET (eram tipicamente 52 milissegundos para a consulta e 50 milissegundos para o mapeamento do resultado plano para a hierarquia POCO). Isso realmente não é nada revolucionário, mas certamente supera o Entity Framework em termos de velocidade e facilidade de uso, especialmente se tudo o que estamos fazendo é executar consultas.

Atualização 2016-02-19

O código tem funcionado sem falhas em produção por 9 meses. A última versão do Slapper.Automapper tem todas as alterações que eu apliquei para corrigir o problema relacionado a nulos sendo retornados na consulta SQL.

Atualização 2017-02-20

O código tem funcionado sem falhas na produção há 21 meses, e lidou com consultas contínuas de centenas de usuários em uma empresa FTSE 250.

Slapper.Automapper também é ótimo para mapear um arquivo .csv diretamente para uma lista de POCOs. Leia o arquivo .csv em uma lista de IDictionary e mapeie-o diretamente na lista de destino de POCOs. O único truque é que você tem que adicionar uma propriedade int Id {get; set} int Id {get; set} , e certifique-se que é único para cada linha (ou então o automapper não será capaz de distinguir entre as linhas).

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

Eu queria mantê-lo o mais simples possível, minha solução:

 public List 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().ToDictionary(k => k.Id, v => v); var images = mapper.Read().ToList(); foreach(var imageGroup in images.GroupBy(g => g.DataId)) { messages[imageGroup.Key].Images = imageGroup.ToList(); } return messages.Values.ToList(); } 

Eu ainda faço uma chamada para o database, e enquanto eu agora executar 2 consultas em vez de uma, a segunda consulta está usando uma associação interna em vez de uma associação esquerda menos ideal.

De acordo com essa resposta, não há um para muitos suporte de mapeamento embutido no Dapper.Net. As consultas sempre retornarão um object por linha do database. Existe uma solução alternativa incluída, no entanto.

Uma ligeira modificação da resposta de Andrew que utiliza um Func para selecionar a chave pai em vez de GetHashCode .

 public static IEnumerable QueryParentChild( this IDbConnection connection, string sql, Func parentKeySelector, Func> childSelector, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null) { Dictionary cache = new Dictionary(); connection.Query( sql, (parent, child) => { if (!cache.ContainsKey(parentKeySelector(parent))) { cache.Add(parentKeySelector(parent), parent); } TParent cachedParent = cache[parentKeySelector(parent)]; IList children = childSelector(cachedParent); children.Add(child); return cachedParent; }, param as object, transaction, buffered, splitOn, commandTimeout, commandType); return cache.Values; } 

Exemplo de uso

 conn.QueryParentChild("sql here", prod => prod.Id, prod => prod.Stores) 

Aqui está uma solução bruta

  public static IEnumerable Query(this IDbConnection cnn, string sql, Func> property, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, string splitOn = "Id", int? commandTimeout = null, CommandType? commandType = null) { var cache = new Dictionary(); cnn.Query(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; } 

Não é de forma alguma a maneira mais eficiente, mas vai colocá-lo em funcionamento. Vou tentar otimizar isso quando tiver uma chance.

use-o assim:

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

tenha em mente que seus objects precisam implementar GetHashCode , talvez assim:

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