Wie schreibe ich eine Abfrage in Dapper.Net?

.net c# dapper

Frage

Ich habe diesen Code geschrieben, um eine Beziehung von einer in eine andere zu projizieren, aber es funktioniert nicht:

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

Kann jemand den Fehler erkennen?

BEARBEITEN:

Dies sind meine Entitäten:

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

BEARBEITEN:

Ich ändere die Abfrage zu:

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

und ich werde Ausnahmen los! Mitarbeiter werden jedoch überhaupt nicht zugeordnet. Ich bin mir immer noch nicht sicher, welches Problem es mit IEnumerable<Employee> in der ersten Abfrage hatte.

Akzeptierte Antwort

In diesem Beitrag wird gezeigt, wie Sie eine stark normalisierte SQL-Datenbank abfragen und das Ergebnis einer Gruppe stark verschachtelter C # POCO-Objekte zuordnen.

Zutaten:

  • 8 Zeilen C #.
  • Einigermaßen einfaches SQL, das einige Joins verwendet.
  • Zwei großartige Bibliotheken.

Die Erkenntnis, die es mir ermöglichte, dieses Problem zu lösen, besteht darin, das MicroORM von der mapping the result back to the POCO Entities zu trennen. Daher verwenden wir zwei separate Bibliotheken:

Im Wesentlichen verwenden wir Dapper , um die Datenbank abzufragen, und dann verwenden Sie Slapper.Automapper , um das Ergebnis direkt in unsere POCOs abzubilden.

Vorteile

  • Einfachheit Es sind weniger als 8 Zeilen Code. Ich finde das viel einfacher zu verstehen, zu debuggen und zu ändern.
  • Weniger Code . Ein paar Zeilen Code ist alles, was Slapper.Automapper muss mit allem umgehen, was Sie darauf werfen, selbst wenn wir ein komplexes verschachteltes POCO haben (dh POCO enthält List<MyClass1> die wiederum List<MySubClass2> usw. enthält).
  • Geschwindigkeit Beide Bibliotheken verfügen über ein außerordentliches Maß an Optimierung und Zwischenspeicherung, damit sie fast so schnell ausgeführt werden können wie manuell abgestimmte ADO.NET-Abfragen.
  • Trennung von Bedenken . Wir können das MicroORM für ein anderes ändern, und das Mapping funktioniert immer noch und umgekehrt.
  • Flexibilität Slapper.Automapper verarbeitet willkürlich verschachtelte Hierarchien, es ist nicht auf mehrere Verschachtelungsebenen beschränkt. Wir können schnell schnelle Änderungen vornehmen und alles wird noch funktionieren.
  • Debugging Wir können zuerst sehen, dass die SQL-Abfrage ordnungsgemäß funktioniert, und dann können wir überprüfen, ob das Ergebnis der SQL-Abfrage den Ziel-POCO-Entitäten ordnungsgemäß zugeordnet ist.
  • Einfache Entwicklung in SQL . Ich finde, das Erstellen von abgeflachten Abfragen mit inner joins , um flache Ergebnisse zurückzugeben, ist viel einfacher als das Erstellen mehrerer Select-Anweisungen mit Stitching auf der Clientseite.
  • Optimierte Abfragen in SQL . Durch das Erstellen einer flachen Abfrage kann die SQL-Engine in einer stark normalisierten Datenbank erweiterte Optimierungen auf das gesamte Objekt anwenden, was normalerweise nicht möglich wäre, wenn viele kleine individuelle Abfragen erstellt und ausgeführt wurden.
  • Vertrauen Dapper ist das Backend für StackOverflow, und Randy Burden ist ein bisschen ein Superstar. Muss ich noch mehr sagen?
  • Geschwindigkeit der Entwicklung Ich konnte einige außerordentlich komplexe Abfragen mit vielen Verschachtelungsstufen durchführen, und die Entwicklungszeit war ziemlich niedrig.
  • Weniger Fehler. Ich habe es einmal geschrieben, es hat einfach funktioniert, und diese Technik hilft jetzt, ein FTSE-Unternehmen mit Strom zu versorgen. Es gab so wenig Code, dass es kein unerwartetes Verhalten gab.

Nachteile

  • Eine Skalierung von mehr als 1.000.000 Zeilen wurde zurückgegeben. Funktioniert gut, wenn Sie <100.000 Zeilen zurückgeben. Wenn wir aber bringen zurück> 1.000.000 Zeilen, um den Verkehr zwischen uns und SQL Server zu reduzieren, sollten wir es nicht abflachen mit inner join (die Duplikate zurückzubringt), sollten wir stattdessen mehr verwenden select - Anweisungen und nähen alles zurück zusammen auf der Client-Seite (siehe die anderen Antworten auf dieser Seite).
  • Diese Technik ist abfrageorientiert . Ich habe diese Technik nicht verwendet, um in die Datenbank zu schreiben, aber ich bin sicher, dass Dapper dies mit mehr Arbeit tun kann, da StackOverflow selbst Dapper als Data Access Layer (DAL) verwendet.

Leistungstest

In meinen Tests fügte Slapper.Automapper den von Dapper zurückgegebenen Ergebnissen einen kleinen Overhead hinzu, was bedeutete, dass es immer noch zehnmal schneller war als Entity Framework, und die Kombination ist immer noch ziemlich nahe an der theoretischen Höchstgeschwindigkeit, für die SQL + C # möglich ist .

In den meisten praktischen Fällen würde der Großteil des Overheads in einer weniger als optimalen SQL-Abfrage liegen und nicht bei einer Zuordnung der Ergebnisse auf der C # -Seite.

Leistungstestergebnisse

Gesamtzahl der Iterationen: 1000

  • Dapper by itself : 1.889 Millisekunden pro Abfrage, wobei 3 lines of code to return the dynamic .
  • Dapper + Slapper.Automapper : 2,446 Millisekunden pro Abfrage, wobei zusätzliche 3 lines of code for the query + mapping from dynamic to POCO Entities .

Arbeitete Beispiel

In diesem Beispiel haben wir eine Liste mit Contacts , und jeder Contact kann eine oder mehrere phone numbers .

POCO-Entitäten

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-Tabelle TestContact

Geben Sie hier die Bildbeschreibung ein

SQL-Tabelle TestPhone

Beachten Sie, dass diese Tabelle eine Fremdschlüssel- ContactID die auf die TestContact Tabelle verweist (dies entspricht der List<TestPhone> im obigen POCO).

Geben Sie hier die Bildbeschreibung ein

SQL, das ein flaches Ergebnis liefert

In unserer SQL-Abfrage verwenden wir so viele JOIN Anweisungen, wie wir benötigen, um alle benötigten Daten in einer flachen, denormalisierten Form zu erhalten . Ja, dies kann zu Duplikaten in der Ausgabe führen. Diese Duplikate werden jedoch automatisch entfernt, wenn wir Slapper.Automapper verwenden , um das Ergebnis dieser Abfrage automatisch direkt in unsere POCO-Objektzuordnung abzubilden.

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

Geben Sie hier die Bildbeschreibung ein

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

Ausgabe

Geben Sie hier die Bildbeschreibung ein

POCO-Entitätshierarchie

In Visual Studio können wir sehen, dass Slapper.Automapper unsere POCO-Entitäten ordnungsgemäß gefüllt hat, dh wir haben eine List<TestContact> und jeder TestContact hat eine List<TestPhone> .

Geben Sie hier die Bildbeschreibung ein

Anmerkungen

Sowohl Dapper als auch Slapper.Automapper speichern alles intern, um die Geschwindigkeit zu erhöhen. Wenn Sie Probleme mit dem Speicher haben (sehr unwahrscheinlich), stellen Sie sicher, dass Sie gelegentlich den Cache für beide löschen.

Stellen Sie sicher, dass Sie die zurückkommenden Spalten benennen. Verwenden Sie die Unterstreichungszeichen ( _ ) , um Slapper.Automapper Hinweise zu geben, wie Sie das Ergebnis in die POCO-Entitäten abbilden.

Stellen Sie sicher, dass Sie für jede POCO-Entität Slapper.Automapper-Hinweise zum Primärschlüssel geben (siehe die Zeilen Slapper.AutoMapper.Configuration.AddIdentifiers ). Sie können dazu auch Attributes auf dem POCO verwenden. Wenn Sie diesen Schritt überspringen, könnte es (theoretisch) schief gehen, da Slapper.Automapper nicht wissen kann, wie das Mapping ordnungsgemäß ausgeführt wird.

Update 2015-06-14

Diese Technik wurde erfolgreich auf eine große Produktionsdatenbank mit über 40 normalisierten Tabellen angewendet. Es funktionierte perfekt, um eine erweiterte SQL-Abfrage mit über 16 inner join und left join in der richtigen POCO-Hierarchie abzubilden (mit 4 Schachtelungsebenen). Die Abfragen sind unglaublich schnell und fast so schnell wie die manuelle Codierung in ADO.NET (normalerweise betrug die Abfrage 52 Millisekunden und für die Zuordnung vom flachen Ergebnis in die POCO-Hierarchie 50 Millisekunden. Dies ist wirklich nichts Revolutionäres, aber es übertrifft das Entity Framework auf Geschwindigkeit und Benutzerfreundlichkeit, vor allem, wenn wir nur Abfragen ausführen.

Update 2016-02-19

Code läuft seit 9 Monaten einwandfrei in der Produktion. Die neueste Version von Slapper.Automapper enthält alle Änderungen, die ich vorgenommen habe, um das Problem zu beheben, das mit den in der SQL-Abfrage zurückgegebenen Nullen zusammenhängt.

Update 2017-02-20

Code läuft seit 21 Monaten fehlerfrei in der Produktion und hat fortlaufende Anfragen von Hunderten von Benutzern in einem FTSE 250-Unternehmen bearbeitet.

Slapper.Automapper eignet sich auch hervorragend zum Slapper.Automapper einer CSV-Datei in eine Liste von POCOs. Lesen Sie die CSV-Datei in eine Liste von IDictionary und ordnen Sie sie direkt der Zielliste der POCOs zu. Der einzige Trick ist, dass Sie eine Eigenschaft hinzufügen müssen int Id {get; set} , und stellen Sie sicher, dass es für jede Zeile eindeutig ist (andernfalls kann der Automapper nicht zwischen den Zeilen unterscheiden).

Aktualisieren Sie 2019-01-29

Kleines Update, um weitere Codekommentare hinzuzufügen.

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


Beliebte Antwort

Ich wollte es so einfach wie möglich halten, meine Lösung:

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

Ich mache immer noch einen Aufruf an die Datenbank, und während ich jetzt 2 Abfragen anstelle von einem ausführen, verwendet die zweite Abfrage einen INNER-Join anstelle eines weniger optimalen LEFT-Joins.



Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow
Ist diese KB legal? Ja, lerne warum
Lizenziert unter: CC-BY-SA with attribution
Nicht verbunden mit Stack Overflow
Ist diese KB legal? Ja, lerne warum