Как написать один-много запросов в Dapper.Net?

Я написал этот код, чтобы проецировать отношение «один к большому», но он не работает:

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); } } 

Кто-нибудь может заметить ошибку?

РЕДАКТИРОВАТЬ:

Это мои сущности:

 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(); } } 

РЕДАКТИРОВАТЬ:

Я меняю запрос на:

  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"); 

и я избавляюсь от исключений! Однако сотрудники не отображаются вообще. Я до сих пор не знаю, какая у него проблема с IEnumerable в первом запросе.

Это сообщение показывает, как запросить высоко нормированную базу данных SQL , и сопоставить результат с множеством сильно вложенных объектов C # POCO.

Ингредиенты:

  • 8 строк C #.
  • Некоторый разумно простой SQL, который использует некоторые объединения.
  • Две удивительные библиотеки.

Проницательность, которая позволила мне решить эту проблему, заключается в том, чтобы отделить MicroORM от mapping the result back to the POCO Entities . Таким образом, мы используем две отдельные библиотеки:

  • Dapper как MicroORM.
  • Slapper.Automapper для отображения.

По сути, мы используем Dapper для запроса базы данных, а затем используйте Slapper.Automapper для сопоставления результата прямо в наших POCOs.

преимущества

  • Простота . Его менее 8 строк кода. Я считаю, что это намного легче понять, отладить и изменить.
  • Меньше кода . Несколько строк кода – это все Slapper.Automapper нужно обрабатывать все, что вы на него набрасываете, даже если у нас есть сложный вложенный POCO (т.е. POCO содержит List который, в свою очередь, содержит List и т. Д.).
  • Скорость . Обе эти библиотеки обладают исключительной степенью оптимизации и кэширования, чтобы заставить их работать почти так же быстро, как вручную настроенные запросы ADO.NET.
  • Разделение проблем . Мы можем изменить Microorm для другого, и отображение все еще работает, и наоборот.
  • Гибкость . Slapper.Automapper обрабатывает произвольно вложенные иерархии, это не ограничивается несколькими уровнями вложенности. Мы можем легко совершать быстрые изменения, и все будет работать.
  • Отладка . Мы можем сначала увидеть, что SQL-запрос работает правильно, тогда мы можем проверить, что результат SQL-запроса правильно отображается на целевые объекты POCO.
  • Простота разработки в SQL . Я нахожу, что создание сглаженных запросов с inner joins для возврата плоских результатов намного проще, чем создание нескольких операторов select, с шитью на стороне клиента.
  • Оптимизированные запросы в SQL . В высоко нормированной базе данных создание плоского запроса позволяет SQL-процессору применять расширенную оптимизацию для всего, что обычно не было бы возможным, если бы было построено и запущено много небольших индивидуальных запросов.
  • Доверие . Dapper – это задняя часть для StackOverflow, и, ну, Randy Burden – это немного суперзвезда. Нужно ли говорить больше?
  • Скорость развития. Я смог выполнить некоторые чрезвычайно сложные запросы с множеством уровней вложенности, а время dev было довольно низким.
  • Меньше ошибок. Я написал это один раз, он просто сработал, и эта техника теперь помогает власти компании FTSE. Был так мало кода, что неожиданное поведение не было.

Недостатки

  • Масштабирование более 1000 000 строк. Хорошо работает при возврате <100 000 строк. Однако, если мы вернем> 1 000 000 строк, чтобы уменьшить трафик между нами и SQL-сервером, мы не должны сглаживать его с помощью inner join (которое возвращает дубликаты), мы должны вместо этого использовать несколько операторов select и сшить все назад вместе на стороне клиента (см. другие ответы на этой странице).
  • Этот метод ориентирован на запрос . Я не использовал эту технику для записи в базу данных, но я уверен, что Dapper более чем способен сделать это с дополнительной дополнительной работой, поскольку сам StackOverflow использует 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 : Dapper + Slapper.Automapper миллисекунды за запрос, используя дополнительные 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 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 TestPhone

Обратите внимание, что в этой таблице есть ContactID внешнего ключа, который ссылается на таблицу TestContact (это соответствует List в 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(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); } } } } 

Вывод

введите описание изображения здесь

Иерархия объектов POCO

В Visual Studio мы видим, что Slapper.Automapper правильно заполняет наши объекты POCO, т. List нас есть List , и каждый TestContact имеет List .

введите описание изображения здесь

Заметки

Оба Dapper и Slapper.Automapper кэшируют все внутренне для скорости. Если вы столкнетесь с проблемами памяти (очень маловероятно), убедитесь, что вы иногда очищаете кеш для обоих из них.

Убедитесь, что вы назовете столбцы, возвращаемые назад, используя нотацию подчеркивания ( _ ), чтобы дать Slapper.Automapper ключ к тому, как сопоставить результат с объектами POCO.

Убедитесь, что вы указали 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-файла прямо в списке POCOs. Прочтите файл .csv в список IDictionary, затем переместите его прямо в целевой список POCOs. Единственный трюк в том, что вам нужно добавить свойство int Id {get; set} int Id {get; set} и убедитесь, что он уникален для каждой строки (иначе автопарпер не сможет отличить строки).

См. https://github.com/SlapperAutoMapper/Slapper.AutoMapper

Я хотел, чтобы это было максимально просто, мое решение:

 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(); } 

Я все еще делаю один вызов в базе данных, и пока я выполняю 2 запроса вместо одного, второй запрос использует соединение INNER вместо менее оптимального LEFT-соединения.

Согласно этому ответу, в Dapper.Net не поддерживается много картографической поддержки. Запросы всегда будут возвращать один объект на строку базы данных. Однако есть альтернативное решение.

Небольшая модификация ответа Андрея, который использует Func для выбора родительского ключа вместо 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; } 

Пример использования

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

Вот грубое обходное решение

  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; } 

это отнюдь не самый эффективный способ, но он заставит вас работать. Я попытаюсь оптимизировать это, когда у меня появится шанс.

используйте его вот так:

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

помните, что вашим объектам необходимо реализовать GetHashCode , возможно, так:

  public override int GetHashCode() { return this.Id.GetHashCode(); } 
Давайте будем гением компьютера.