Ответ 1

В этом посте показано, как выполнить запрос к сильно нормализованной базе данных SQL и отобразить результат в набор сильно вложенных объектов С# POCO.

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

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

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

• Dapper в качестве 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 применять расширенные оптимизации ко всему, что, как правило, было бы невозможно, если бы было создано и выполнено много небольших отдельных запросов.
• Доверие. Даппер - это бэк-энд для Кару, и, ну, Рэнди Бурден - немного суперзвезда. Должен ли я сказать больше?
• Скорость разработки. Мне удалось выполнить несколько чрезвычайно сложных запросов со многими уровнями вложенности, а время разработки было довольно низким.
• Меньше ошибок. Я написал это однажды, это просто сработало, и теперь этот метод помогает компании FTSE. Там было так мало кода, что не было неожиданного поведения.

Недостатки

• Масштабирование превышает 1000000 возвращенных строк. Хорошо работает при возврате & lt; 100 000 строк Однако, если мы возвращаем> 1 000 000 строк, чтобы уменьшить трафик между нами и сервером SQL, мы не должны выравнивать его с помощью inner join (который возвращает дубликаты), вместо этого мы должны использовать несколько операторов select и сшить все вместе на стороне клиента (см. другие ответы на этой странице).
• Этот метод ориентирован на запросы. Я не использовал эту технику для записи в базу данных, но я уверен, что Dapper более чем способен сделать это с некоторой дополнительной работой, поскольку сам Qaru использует Dapper в качестве своего уровня доступа к данным (DAL).

Тестирование производительности

В моих тестах Slapper.Automapper добавил небольшие издержки к результатам, возвращаемым Dapper, что означало, что он все еще был в 10 раз быстрее, чем Entity Framework, и комбинация все еще довольно близка к теоретической Максимальная скорость SQL + С# может.

В большинстве практических случаев большая часть накладных расходов будет приходиться на неоптимальный SQL-запрос, а не на отображение результатов на стороне С#.

Результаты тестирования производительности

Общее количество итераций: 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; }
}

Таблица SQL TestContact

Таблица SQL TestPhone

Обратите внимание, что в этой таблице есть внешний ключ 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

Код С#

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 Entity Hierarchy

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

Примечания

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

Обновление 2019-01-29

Незначительное обновление, чтобы добавить больше комментариев кода.

Видеть: https://github.com/SlapperAutoMapper/Slapper.AutoMapper

Ответ 2

Я хотел сохранить его как можно проще, мое решение:

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-соединения.

Ответ 3

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

Ответ 4

Небольшая модификация ответа Andrew, которая использует 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)

Ответ 5

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

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

Ответ 6

Вот еще один метод:

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

Источник: http://dapper-tutorial.net/result-multi-mapping#example---query-multi-mapping-one-to-many

Ответ 7

using (IDbConnection cn = Connection)
        {
            cn.Open();
            var cache = new Dictionary<int, Account>();
            cn.Query<Account, Book, Account>("Select a.*,b.* from T_Account a left outer  join  T_Book b on a.Id=b.AccountId",
                    (A, B) =>
                    {
                        if (!cache.ContainsKey(A.GetHashCode()))
                            cache.Add(A.GetHashCode(), A);
                        var localOne = cache[A.GetHashCode()];
                        var list = localOne.BookList;
                        Console.WriteLine("B is null ?：---" + (B == null));
                        if (B != null)
                            list.Add(B);
                        return localOne;
                    }, null, null, true, "BookId");
            return cache.Values;
        }