Как мне написать один ко многим запрос в Dapper.Net?
Я написал этот код для проекции отношения один ко многим, но он не работает:
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> в первом запросе.
6 ответов
В этом посте показано, как выполнить запрос к сильно нормализованной базе данных SQL и отобразить результат в набор сильно вложенных объектов C# POCO.
Ингредиенты:
- 8 строк C#.
- Некоторый достаточно простой SQL, который использует некоторые объединения.
- Две потрясающие библиотеки.
Понимание, которое позволило мне решить эту проблему, состоит в том, чтобы отделить MicroORM от mapping the result back to the POCO Entities, Таким образом, мы используем две отдельные библиотеки:
- Дэппер как MicroORM.
- Slapper.Automapper для картографирования.
По сути, мы используем Dapper для запроса к базе данных, затем используем Slapper.Automapper для отображения результата прямо в наши POCO.
преимущества
- Простота Его менее 8 строк кода. Я нахожу это намного проще для понимания, отладки и изменения.
- Меньше кода. Несколько строк кода - это все Slapper.Automapper должен обрабатывать все, что вы на него бросаете, даже если у нас есть комплексное вложенное POCO (т.е. POCO содержит
List<MyClass1>который в свою очередь содержитList<MySubClass2>, так далее). - Скорость Обе эти библиотеки обладают невероятным объемом оптимизации и кэширования, благодаря чему они выполняются почти так же быстро, как и настраиваемые вручную запросы ADO.NET.
- Разделение проблем. Мы можем изменить MicroORM на другой, и сопоставление все еще работает, и наоборот.
- Гибкость Slapper.Automapper обрабатывает произвольно вложенные иерархии, он не ограничен парой уровней вложенности. Мы можем легко внести быстрые изменения, и все будет работать.
- Отладка Сначала мы видим, что SQL-запрос работает правильно, затем мы можем проверить, что результат SQL-запроса правильно сопоставлен с целевыми объектами POCO.
- Легкость разработки на SQL. Я считаю, что создание плоских запросов с
inner joinsвернуть плоские результаты гораздо проще, чем создать несколько операторов выбора с прошивкой на стороне клиента. - Оптимизированные запросы в SQL. В сильно нормализованной базе данных создание плоского запроса позволяет механизму SQL применять расширенные оптимизации ко всему, что, как правило, было бы невозможно, если бы было создано и выполнено много небольших отдельных запросов.
- Доверие Dapper - это серверная часть Stackru, и Рэнди Берден - суперзвезда. Должен ли я сказать больше?
- Скорость разработки. Мне удалось выполнить несколько чрезвычайно сложных запросов со многими уровнями вложенности, и время разработки было довольно низким.
- Меньше ошибок. Я написал это однажды, это просто сработало, и теперь этот метод помогает компании FTSE. Там было так мало кода, что не было неожиданного поведения.
Недостатки
- Масштабирование более 1000000 строк возвращается. Хорошо работает при возврате< 100 000 строк. Однако, если мы возвращаем>1 000 000 строк, чтобы уменьшить трафик между нами и сервером SQL, мы не должны выравнивать его, используя
inner join(который возвращает дубликаты), мы должны вместо этого использовать несколькоselectзаявления и сшить все обратно на стороне клиента (см. другие ответы на этой странице). - Этот метод ориентирован на запросы. Я не использовал эту технику для записи в базу данных, но я уверен, что Dapper более чем способен сделать это с некоторой дополнительной работой, поскольку сам Stackru использует Dapper в качестве своего уровня доступа к данным (DAL).
Тестирование производительности
В моих тестах Slapper.Automapper добавил небольшие издержки к результатам, возвращаемым Dapper, что означало, что он все еще был в 10 раз быстрее, чем Entity Framework, и комбинация все еще довольно близка к теоретической максимальной скорости, на которую способен 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 Entities
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; }
}
Таблица SQLTestContact
Таблица SQLTestPhone
Обратите внимание, что эта таблица имеет внешний ключContactIDчто относится кTestContactтаблица (это соответствует List<TestPhone> в POCO выше).
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;
// 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 Entity Hierarchy
Глядя в Visual Studio, мы видим, что Slapper.Automapper правильно заполнил наши объекты POCO, то есть у нас естьList<TestContact>и каждыйTestContactимеет List<TestPhone>,
Заметки
И Dapper, и Slapper.Automapper кешируют все внутренне для скорости. Если у вас возникают проблемы с памятью (очень маловероятно), убедитесь, что вы время от времени очищаете кеш для них обоих.
Убедитесь, что вы назвали возвращающиеся столбцы, используя подчеркивание (_ ) примечание, чтобы дать Slapper.Automapper подсказки о том, как отобразить результат в POCO Entities.
Убедитесь, что вы указываете Slapper.Automapper ключи на первичный ключ для каждого объекта POCO (см. Строки Slapper.AutoMapper.Configuration.AddIdentifiers). Вы также можете использовать Attributes на POCO для этого. Если вы пропустите этот шаг, то он может пойти не так (теоретически), так как Slapper.Automapper не будет знать, как правильно выполнить сопоставление.
Обновление 2015-06-14
Успешно применил эту технику к огромной производственной базе данных с более чем 40 нормализованными таблицами. Он отлично работал для отображения расширенного запроса SQL с более чем 16 inner join а также left join в правильную иерархию POCO (с 4 уровнями вложенности). Запросы выполняются ослепительно быстро, почти так же быстро, как и ручное кодирование в ADO.NET (обычно это было 52 миллисекунды для запроса и 50 миллисекунд для отображения из плоского результата в иерархию POCO). В этом нет ничего революционного, но он наверняка превосходит Entity Framework по скорости и простоте использования, особенно если все, что мы делаем, это выполняем запросы.
Обновление 2016-02-19
Код работает безупречно в течение 9 месяцев. Последняя версия Slapper.Automapper содержит все изменения, которые я применил для исправления проблемы, связанной с возвращением нулей в запросе SQL.
Обновление 2017-02-20
Код работает безупречно в течение 21 месяца и обрабатывает непрерывные запросы от сотен пользователей в компании FTSE 250.
Slapper.Automapper также отлично подходит для отображения файла.csv прямо в список POCO. Считайте файл.csv в список IDictionary, затем отобразите его прямо в целевой список POCO. Единственный трюк в том, что вы должны добавить свойства int Id {get; set}и убедитесь, что он уникален для каждой строки (иначе автомат не сможет различить строки).
Смотрите: 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.
Небольшая модификация ответа Эндрю, которая использует 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;
}
Пример использования
conn.QueryParentChild<Product, Store, int>("sql here", prod => prod.Id, prod => prod.Stores)
Согласно этому ответу, в Dapper.Net встроена поддержка картирования не для многих. Запросы всегда возвращают один объект на строку базы данных. Однако есть альтернативное решение.
Вот еще один метод:
Order (один) - OrderDetail (много)
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();
}
Вот грубый обходной путь
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;
}
Это ни в коем случае не самый эффективный способ, но он поможет вам начать работу. Я постараюсь оптимизировать это, когда у меня будет шанс.
используйте это так:
conn.Query<Product, Store>("sql here", prod => prod.Stores);
имейте в виду, ваши объекты должны быть реализованы GetHashCodeвозможно, вот так:
public override int GetHashCode()
{
return this.Id.GetHashCode();
}