Entity Framework Webcasts – Stored Procedures und SQL Queries

Im sechsten Teil meiner Entity Framework Webcast Serie zeige ich, wie die IUnitOfWork erweitert werden muss, um SQL Abfragen und Befehle auszuführen. Im Fokus stehen Value Functions und Stored Procedures.

 

Entity Framework – Teil 6 – Stored Procedures und SQL Queries – YouTube

 

Dafür stellt das Entity Framework 2 Schnittstellen bereit:

• SqlQuery
• ExecuteSqlCommand

Ein wichtiger Hinweis an dieser Stelle, welcher eingehender in meinem Webcast erwähnt wird: Zum Ausführen einer SQL Query existieren 2 Implementierungen:

• DbContext.Database.SqlQuery<TElement>(string sql, params Object[] parameters)
• DbSet.SqlQuery(string sql, params Object[] parameters)

Der wesentliche Unterschied besteht darin, dass im ersten Fall die zurückgegebenen Entitäten nicht getrackt werden, im zweiten hingegen schon. Das ist leicht nachvollziehbar, so muss bei DbContext.Database.SqlQuery ein generischer Parameter, der den Rückgabewert darstellt, übergeben werden. Im Falle von DbSet.SqlQuery ist dies nicht nötig, schließlich befindet man sich zum Zeitpunkt der Abfrage auf einem konkreten Set an Entitäten, die über den Context aufgelöst wurden.

Meine vorgestellte Lösung geht den gleichen Weg wie die in Teil 3 beschriebenen Queries, d.h. es werden quasi SQL Query und Command Repositories angelegt, um einer Streuung innerhalb der Anwendungslandschaft entgegenzuwirken.

Schließlich noch zwei Sicherheitshinweise:

• Die SQL Befehle werden mit den Berechtigungen des Contexts bzw. des darunterliegenden Sicherheitstoken ausgeführt
• Beim Erstellen des SQL Strings sollte aus Sicherheitsgründen gegenüber Injections mit String.Format() oder alternativ mit SqlParameters gearbeitet werden.

 

I wrote this blog post as an answer of a question from the Netherlands, so I want to complete this article with a quote:

“When you execute a SqlQuery from Database, the results are never tracked by the context, even if the query returns types that are in the model an known by the context. If you do not want the results to be changed-tracked, use DbContext.Databae.SqlQuery.

Results of a DbSet.SqlQuery will be tracked by the context. Ensuring that results are change-tracked is the primary reason you would choose to use DbSet.SqlQuery over Database.SqlQuery.” (Programming Entity Framework, Page 226)

 

Weitere Quellen:

• Using DbContext in EF – Part 10 – Raw SQL Queries
• Programming Entity Framework: DbContext
• Alle Teiler meiner Webcastserie
• Mein YouTube Channel
• Alle meine Blogeinträge zum Entity Framework
• Den Code auf Github

Werbung

T-SQL Abfrage für Tags

Gerade musste ich eine etwas schwierigere Auswertung machen. Die Aufgabenstellung dazu war:

“Ermittle alle Kunden, die weder der Branche Handel noch Verbraucher zugeordnet sind.”

Nun muss man wissen, dass wir die Branchen an einem Kunden quasi wie Tags haften. Im ERP sieht das so aus:

Dementsprechend gibt es zw. der Tabelle Kunde und Branche eine n:m Beziehung. Ein Kunde kann keinen Tag, nur einen oder mehrere haben. Ein Tag kann wiederum keinem Kunden, genau einem oder mehreren zugeordnet sein. Gemäß der Normalisierungsregeln für relationale Datenbanken ist die Tabelle Branchen so aufgebaut, dass z.B. für das oben genannte Beispiel 4 Einträge zu dem Kunden mit der ID 7001 drin stehen. Pro Tag jeweils einen Eintrag.

Das schwierige war nun, dass ich den kompletten Kundenstamm durchlaufen und für jeden gefundenen Kunden eine Unterabfrage feuern musste, die prüft, ob in irgendeinem der zugeordneten Tags der Begriff Handel oder Verbraucher vorkommt. Im Programmcode wäre das eine klassische For Each Schleife, welche im inneren nochmal eine For Each Schleife enthält (Anmerkung: Wenn man sich mit LINQ auskennt, geht es auch einfacher).

Wie macht man das nun mit T-SQL am einfachsten, wenn man zu faul ist zum Programmieren? Nachdem ich mich mit dem Wissensvermittler meines Vertrauens (Google) beraten habe, war die eleganteste und einfachste Lösung das Verwenden des Befehls

For XML Path(“)

Ein konkretes Beispiel findet ihr hier :

SELECT p1.CategoryId,
      ( SELECT ProductName + ‚,‘
          FROM Northwind.dbo.Products p2
         WHERE p2.CategoryId = p1.CategoryId
         ORDER BY ProductName
           FOR XML PATH(“) ) AS Products
  FROM Northwind.dbo.Products p1
GROUP BY CategoryId ;

In der erzeugten Spalte steht dann der XML String. Bei uns sah das so aus:

<Beschreibung>Maschinenbau </Beschreibung><Beschreibung>Anlagenbau/ Apparatebau</Beschreibung>

Nun konnte ich mit LIKE prüfen, ob in diesem z.B. Handel oder Verbraucher steht.