Question Comment puis-je écrire une à plusieurs requêtes dans Dapper.Net?


J'ai écrit ce code pour projeter une relation un à plusieurs mais cela ne fonctionne pas:

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

Quelqu'un peut-il repérer l'erreur?

MODIFIER:

Ce sont mes entités:

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

MODIFIER:

Je change la requête pour:

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

et je me débarrasse des exceptions! Cependant, les employés ne sont pas du tout mappés. Je ne suis toujours pas sûr du problème avec IEnumerable<Employee> en première requête.


58
2018-02-19 15:27


origine


Réponses:


Ce post montre comment interroger un Base de données SQL hautement normalisée, et mappez le résultat dans un ensemble d'objets C # POCO fortement imbriqués.

Ingrédients:

  • 8 lignes de C #.
  • Un SQL relativement simple qui utilise des jointures.
  • Deux bibliothèques géniales.

L’idée qui m’a permis de résoudre ce problème est de séparer les MicroORM de mapping the result back to the POCO Entities. Ainsi, nous utilisons deux bibliothèques distinctes:

Essentiellement, nous utilisons Pimpant pour interroger la base de données, puis utiliser Slapper.Automapper  pour cartographier le résultat directement dans nos POCO.

Avantages

  • Simplicité. Ses moins de 8 lignes de code. Je trouve cela beaucoup plus facile à comprendre, à déboguer et à changer.
  • Moins de code. Quelques lignes de code sont tout Slapper.Automapper doit gérer tout ce que vous lui lancez, même si nous avons un POCO imbriqué complexe (c'est-à-dire que POCO contient List<MyClass1> qui à son tour contient List<MySubClass2>, etc).
  • La vitesse. Ces deux bibliothèques ont une optimisation et une mise en cache extraordinaires pour être exécutées presque aussi rapidement que les requêtes ADO.NET optimisées manuellement.
  • Séparation des préoccupations. Nous pouvons changer le MicroORM pour un autre, et le mappage fonctionne toujours, et vice-versa.
  • La flexibilité. Slapper.Automapper gère les hiérarchies imbriquées arbitrairement, il ne se limite pas à quelques niveaux d'imbrication. Nous pouvons facilement faire des changements rapides et tout fonctionnera encore.
  • Le débogage. Nous pouvons d'abord voir que la requête SQL fonctionne correctement, puis nous pouvons vérifier que le résultat de la requête SQL est correctement redirigé vers les entités POCO cibles.
  • Facilité de développement en SQL. Je trouve que la création de requêtes aplaties avec inner joins Il est beaucoup plus facile de renvoyer des résultats plats que de créer plusieurs instructions select, avec l'assemblage côté client.
  • Requêtes optimisées en SQL. Dans une base de données hautement normalisée, la création d'une requête à plat permet au moteur SQL d'appliquer des optimisations avancées à l'ensemble, ce qui ne serait normalement pas possible si de nombreuses petites requêtes individuelles étaient construites et exécutées.
  • Confiance. Dapper est le back end de StackOverflow, et Randy Burden est un peu une superstar. Ai-je besoin d'en dire plus?
  • Vitesse de développement J'ai été capable de faire des requêtes extraordinairement complexes, avec de nombreux niveaux d'imbrication, et le temps de développement était assez faible.
  • Moins de bugs. Je l'ai écrit une fois, cela a fonctionné, et cette technique aide maintenant à alimenter une société FTSE. Il y avait si peu de code qu'il n'y avait pas de comportement inattendu.

Désavantages

  • Mise à l'échelle au-delà de 1 000 000 de lignes renvoyées. Fonctionne bien lors du retour de <100 000 lignes. Cependant, si nous rapportons plus de 1 000 000 de lignes, afin de réduire le trafic entre nous et le serveur SQL, nous ne devrions pas les aplanir en utilisant inner join (ce qui ramène les doublons), nous devrions plutôt utiliser plusieurs select déclarations et assembler tout sur le côté client (voir les autres réponses sur cette page).
  • Cette technique est orientée requête. Je n'ai pas utilisé cette technique pour écrire dans la base de données, mais je suis sûr que Dapper est plus que capable de le faire avec un travail supplémentaire, car StackOverflow utilise Dapper comme couche d'accès aux données (DAL).

Test de performance

Dans mes tests, Slapper.Automapper ajouté une petite surcharge aux résultats renvoyés par Dapper, ce qui signifie qu’il était encore 10 fois plus rapide que Entity Framework, et la combinaison est encore assez proche de la vitesse maximale théorique que SQL + C # est capable de.

Dans la plupart des cas pratiques, la plus grande partie de la surcharge se situerait dans une requête SQL moins qu'optimale, et non avec un mappage des résultats du côté C #.

Résultats des tests de performance

Nombre total d'itérations: 1000

  • Dapper by itself: 1.889 millisecondes par requête, en utilisant 3 lines of code to return the dynamic.
  • Dapper + Slapper.Automapper: 2.463 millisecondes par requête, en utilisant un autre 3 lines of code for the query + mapping from dynamic to POCO Entities.

Exemple travaillé

Dans cet exemple, nous avons la liste de Contactset chacun Contact peut avoir un ou plusieurs phone numbers.

Entités 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; }
}

Table SQL TestContact

enter image description here

Table SQL TestPhone

Notez que cette table a une clé étrangère ContactID qui se réfère à la TestContact table (ceci correspond à la List<TestPhone> dans le POCO ci-dessus).

enter image description here

SQL qui produit un résultat plat

Dans notre requête SQL, nous utilisons autant de JOIN déclarations que nous devons obtenir toutes les données dont nous avons besoin, dans un forme plate et dénormalisée. Oui, cela peut produire des doublons dans la sortie, mais ces doublons seront automatiquement éliminés lorsque nous utilisons Slapper.Automapper pour mapper automatiquement le résultat de cette requête directement dans notre mappage d'objet 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

enter image description here

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

Sortie

enter image description here

Hiérarchie d'entités POCO

En regardant dans Visual Studio, nous pouvons voir que Slapper.Automapper a correctement rempli nos entités POCO, c’est-à-dire que nous avons un List<TestContact>et chacun TestContact a un List<TestPhone>.

enter image description here

Remarques

Dapper et Slapper.Automapper mettent tous les deux en cache pour la vitesse. Si vous rencontrez des problèmes de mémoire (très improbable), veillez à effacer occasionnellement le cache pour les deux.

Assurez-vous de nommer les colonnes qui reviennent, en utilisant le souligner (_) notation donner à Slapper.Automapper des indices sur la manière de mapper le résultat dans les entités POCO.

Assurez-vous de donner des indices Slapper.Automapper sur la clé primaire pour chaque entité POCO (voir les lignes Slapper.AutoMapper.Configuration.AddIdentifiers). Vous pouvez aussi utiliser Attributes sur le POCO pour cela. Si vous ignorez cette étape, cela pourrait mal se passer (en théorie), car Slapper.Automapper ne saura pas comment faire correctement le mappage.

Mise à jour 2015-06-14

Appliqué cette technique avec succès à une énorme base de données de production avec plus de 40 tables normalisées. Il a parfaitement fonctionné pour mapper une requête SQL avancée avec plus de 16 inner join et left join dans la hiérarchie POCO appropriée (avec 4 niveaux d'imbrication). Les requêtes sont incroyablement rapides, presque aussi rapides que le codage manuel dans ADO.NET (il s'agissait généralement de 52 millisecondes pour la requête et de 50 millisecondes pour le mappage du résultat plat dans la hiérarchie POCO). Ce n'est vraiment rien de révolutionnaire, mais il surpasse Entity Framework pour sa rapidité et sa facilité d'utilisation, surtout si nous ne faisons que lancer des requêtes.

Mise à jour 2016-02-19

Code fonctionne parfaitement depuis 9 mois. La dernière version de Slapper.Automapper contient toutes les modifications que j'ai appliquées pour résoudre le problème lié aux valeurs NULL renvoyées dans la requête SQL.

Mise à jour 2017-02-20

Code fonctionne parfaitement depuis 21 mois et a traité en continu des requêtes de centaines d'utilisateurs dans une société FTSE 250.

Slapper.Automapper est également idéal pour mapper un fichier .csv directement dans une liste de POCO. Lisez le fichier .csv dans une liste d'IDictionary, puis mappez-le directement dans la liste cible des objets POCO. Le seul truc est que vous devez ajouter une propriété int Id {get; set}, et assurez-vous qu'il est unique pour chaque ligne (sinon l'automapper ne pourra pas distinguer les lignes).

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


122
2018-05-06 15:19



Je voulais rester aussi simple que possible, ma solution:

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

Je continue à faire un appel à la base de données, et alors que j'exécute maintenant 2 requêtes au lieu d'une, la deuxième requête utilise une jointure INNER au lieu d'une jointure LEFT moins optimale.


8
2017-10-24 06:18



Selon cette réponse Dapper.Net ne prend pas en charge un seul et même support de cartographie. Les requêtes renverront toujours un objet par ligne de base de données. Il existe cependant une solution alternative.


6
2018-02-19 16:08



Une légère modification de la réponse d'Andrew qui utilise un Func pour sélectionner la clé parente au lieu 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;
}

Exemple d'utilisation

conn.QueryParentChild<Product, Store, int>("sql here", prod => prod.Id, prod => prod.Stores)

5
2018-03-14 20:49



Voici une solution de rechange brute

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

Ce n'est en aucun cas le moyen le plus efficace, mais cela vous permettra d'être opérationnel. Je vais essayer d'optimiser cela quand j'ai une chance.

utilisez-le comme ceci:

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

Gardez à l'esprit que vos objets doivent être mis en œuvre GetHashCode, peut-être comme ça:

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

2
2017-07-29 22:35