c# - framework - ¿Cómo escribo una a muchas consultas en Dapper.Net?
dapper.net core (5)
Aquí hay una solución aproximada
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;
}
de ninguna manera es la manera más eficiente, pero te pondrá en marcha. Trataré de optimizar esto cuando tenga oportunidad.
Úselo así:
conn.Query<Product, Store>("sql here", prod => prod.Stores);
tenga en cuenta que sus objetos necesitan implementar GetHashCode , quizás así:
public override int GetHashCode()
{
return this.Id.GetHashCode();
}
He escrito este código para proyectar una relación de uno a muchos, pero no está funcionando:
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);
}
}
¿Alguien puede detectar el error?
EDITAR:
Estas son mis entidades:
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>();
}
}
EDITAR:
Cambio la consulta a:
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");
¡y me deshago de las excepciones! Sin embargo, los empleados no están mapeados en absoluto. Todavía no estoy seguro de qué problema tenía con IEnumerable<Employee> en la primera consulta.
De acuerdo con esta respuesta, no hay soporte de mapeo de uno a muchos integrado en Dapper.Net. Las consultas siempre devolverán un objeto por fila de base de datos. Sin embargo, hay una solución alternativa incluida.
Esta publicación muestra cómo consultar una base de datos SQL altamente normalizada y mapear el resultado en un conjunto de objetos C # POCO altamente anidados.
Ingredientes:
- 8 líneas de C #.
- Un SQL razonablemente simple que usa algunas combinaciones.
- Dos geniales bibliotecas
La idea que me permitió resolver este problema es separar el MicroORM del mapping the result back to the POCO Entities . Por lo tanto, usamos dos bibliotecas separadas:
- Dapper como el MicroORM.
- Slapper.Automapper para mapeo.
Básicamente, usamos Dapper para consultar la base de datos, luego usamos Slapper.Automapper para asignar el resultado directamente a nuestras POCO.
Ventajas
- Simplicidad . Sus menos de 8 líneas de código. Encuentro esto mucho más fácil de entender, depurar y cambiar.
- Menos código . Algunas líneas de código son todas Slapper.Automapper necesita manejar cualquier cosa que le arroje, incluso si tenemos un POCO anidado complejo (es decir, POCO contiene
List<MyClass1>que a su vez contieneList<MySubClass2>, etc.). - Velocidad Ambas bibliotecas tienen una cantidad extraordinaria de optimización y almacenamiento en caché para que se ejecuten casi tan rápido como las consultas ADO.NET sintonizadas a mano.
- Separación de preocupaciones . Podemos cambiar el MicroORM por uno diferente, y el mapeo aún funciona, y viceversa.
- Flexibilidad Slapper.Automapper maneja jerarquías anidadas arbitrariamente, no está limitado a un par de niveles de anidamiento. Podemos hacer cambios rápidos fácilmente, y todo seguirá funcionando.
- Depuración . Primero podemos ver que la consulta SQL está funcionando correctamente, luego podemos verificar que el resultado de la consulta SQL esté correctamente correlacionado con las Entidades POCO objetivo.
- Facilidad de desarrollo en SQL . Encuentro que crear consultas planas con
inner joinspara devolver resultados planos es mucho más fácil que crear múltiples instrucciones de selección, con pespuntes en el lado del cliente. - Consultas optimizadas en SQL . En una base de datos altamente normalizada, la creación de una consulta plana permite que el motor SQL aplique optimizaciones avanzadas al conjunto, lo que normalmente no sería posible si se construyeran y ejecutaran muchas pequeñas consultas individuales.
- Confianza Dapper es el back-end para , y, bueno, Randy Burden es un poco una superestrella. ¿Debo decir algo más?
- Velocidad de desarrollo. Pude hacer algunas consultas extraordinariamente complejas, con muchos niveles de anidamiento, y el tiempo de desarrollo era bastante bajo.
- Menos errores. Lo escribí una vez, simplemente funcionó, y esta técnica ahora está ayudando a impulsar una empresa de FTSE. Había tan poco código que no hubo un comportamiento inesperado.
Desventajas
- Escala más allá de 1,000,000 filas devueltas. Funciona bien al devolver <100.000 filas. Sin embargo, si estamos devolviendo> 1,000,000 de filas, para reducir el tráfico entre nosotros y el servidor SQL, no debemos aplanarlo usando
inner join(que trae de vuelta los duplicados), deberíamos usar múltiples declaraciones deselecty volver a coser todo juntos en el lado del cliente (ver las otras respuestas en esta página). - Esta técnica está orientada a consultas . No utilicé esta técnica para escribir en la base de datos, pero estoy seguro de que Dapper es más que capaz de hacer esto con más trabajo extra, ya que utiliza Dapper como su capa de acceso a datos (Data Access Layer, DAL).
Pruebas de rendimiento
En mis pruebas, Slapper.Automapper agregó una pequeña sobrecarga a los resultados devueltos por Dapper, lo que significaba que todavía era 10 veces más rápido que Entity Framework, y la combinación todavía es bastante cercana a la velocidad máxima teórica de la que SQL + C # es capaz .
En la mayoría de los casos prácticos, la mayor parte de la sobrecarga estaría en una consulta SQL menos que óptima, y no con algún mapeo de los resultados en el lado C #.
Resultados de pruebas de rendimiento
Número total de iteraciones: 1000
-
Dapper by itself: 1.889 milisegundos por consulta, utilizando3 lines of code to return the dynamic. -
Dapper + Slapper.Automapper: 2,463 milisegundos por consulta, usando3 lines of code for the query + mapping from dynamic to POCO Entitiesadicionales3 lines of code for the query + mapping from dynamic to POCO Entities.
Ejemplo trabajado
En este ejemplo, tenemos una lista de Contacts , y cada Contact puede tener uno o más phone numbers .
Entidades 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
Tabla de SQL TestPhone
Tenga en cuenta que esta tabla tiene un ContactID clave ContactID que hace referencia a la tabla TestContact (esto corresponde a la List<TestPhone> en el POCO anterior).
SQL que produce resultado plano
En nuestra consulta SQL, utilizamos tantas declaraciones JOIN como necesitamos para obtener todos los datos que necesitamos, en una forma plana y desnormalizada . Sí, esto podría producir duplicados en la salida, pero estos duplicados se eliminarán automáticamente cuando usemos Slapper.Automapper para asignar automáticamente el resultado de esta consulta directamente en nuestro mapa de objetos 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 # code
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;
// 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);
}
}
}
}
Salida
Jerarquía de la entidad POCO
Buscando en Visual Studio, podemos ver que Slapper.Automapper ha llenado correctamente nuestras entidades POCO, es decir, tenemos una List<TestContact> , y cada TestContact tiene una List<TestPhone> .
Notas
Tanto Dapper como Slapper.Automapper almacenan en caché todo internamente para mayor velocidad. Si se encuentra con problemas de memoria (muy poco probable), asegúrese de limpiar ocasionalmente el caché para ambos.
Asegúrese de nombrar las columnas que regresan, usando la Slapper.Automapper para dar pistas de Slapper.Automapper sobre cómo mapear el resultado en las Entidades POCO.
Asegúrese de dar pistas de Slapper.Automapper en la clave primaria para cada entidad POCO (consulte las líneas Slapper.AutoMapper.Configuration.AddIdentifiers ). También puede usar Attributes en el POCO para esto. Si omite este paso, podría salir mal (en teoría), ya que Slapper.Automapper no sabría cómo hacer el mapeo correctamente.
Actualización 2015-06-14
Aplicó con éxito esta técnica a una gran base de datos de producción con más de 40 tablas normalizadas. Funcionó perfectamente para mapear una consulta SQL avanzada con más de 16 inner join y se left join a la jerarquía correcta de POCO (con 4 niveles de anidación). Las consultas son deslumbrantemente rápidas, casi tan rápido como codificarlo a mano en ADO.NET (normalmente era 52 milisegundos para la consulta y 50 milisegundos para la asignación del resultado plano a la jerarquía POCO). Esto realmente no es nada revolucionario, pero seguro que supera a Entity Framework por su velocidad y facilidad de uso, especialmente si todo lo que hacemos es ejecutar consultas.
Actualización 2016-02-19
El código ha estado funcionando impecablemente en producción durante 9 meses. La última versión de Slapper.Automapper tiene todos los cambios que apliqué para solucionar el problema relacionado con los nulos que se devuelven en la consulta SQL.
Actualización 2017-02-20
Code ha estado funcionando impecablemente en producción durante 21 meses y ha manejado consultas continuas de cientos de usuarios en una empresa FTSE 250.
Slapper.Automapper también es ideal para mapear un archivo .csv directamente en una lista de POCO. Lea el archivo .csv en una lista de IDictionary, y luego ubíquelo directamente en la lista de objetivos de POCO. El único truco es que debes agregar una propiedad int Id {get; set} int Id {get; set} , y asegúrese de que sea único para cada fila (de lo contrario, el Automapper no podrá distinguir entre las filas).
Ver: https://github.com/SlapperAutoMapper/Slapper.AutoMapper
Quería mantenerlo lo más simple posible, mi solución:
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();
}
Todavía hago una llamada a la base de datos, y mientras ahora ejecuto 2 consultas en lugar de una, la segunda consulta usa una combinación INNER en lugar de una combinación LEFT menos óptima.
Una ligera modificación de la respuesta de Andrew que utiliza un Func para seleccionar la clave principal en lugar de 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;
}
Ejemplo de uso
conn.QueryParentChild<Product, Store, int>("sql here", prod => prod.Id, prod => prod.Stores)