SQL Practice Blog – SQL Server, BizTalk und .NET Erfahrungen

Archive for the ‘Entity Framework’ Category

Abfragen mit dem Entity Framework nur über Stored Procedures erlauben

leave a comment »

Diesen Blog Post habe ich meinen Kollegen Marcel Felder zu verdanken, der die Lösung zu diesem Problem gefunden hat. Da ich aber glaube, dass der eine oder andere ebenfalls dieses Szenario haben könnte, habe ich mich entschlossen die Lösung zu veröffentlichen. Danke Marcel!

Szenario:

Mein Kollege hatte in einem Projekt die Anforderung, Abfragen welche über das Entity Framework auf eine Datenbank ausgeführt werden nur über Stored Procedures zu erlauben. Das bedeutet, eine direkte Abfrage über den ObjectContext mit Zugriff auf die EntitySets soll nicht möglich sein (siehe Bild unten).

image

Lösung:

Als erstes werde ich eine Stored Procedure im SQL Server anlegen, welche mir alle Kunden selektiert, die in einer bestimmten Stadt wohnen.

image

Danach werde ich mein Entity Data Model aktualisieren und die Stored Procedure hinzufügen. Damit steht die Stored Procedure jedoch noch nicht im ObjectContext zum Aufruf zur Verfügung. Die Stored Procedure muss nun vom Store Model in das Conceptual Model importiert werden. Dazu öffnet man das Model Browser Fenster, begibt sich in den Store Bereich und wählt unter Stored Procedures die gewünschte Stored Procedure aus. Mit einem Rechtsklick auf die ausgewählte Stored Procedure, öffnet sich ein Kontextmenu wo man den Eintrag Add Function Import auswählt. Jetzt muss noch ein Funktionsname und ein Rückgabewert gewählt werden.

image

Nun steht die Stored Procedure im ObjectContext zum Aufruf zur Verfügung. Jetzt müssen wir nur noch dafür sorgen, dass die EntitySets, welche nicht über den ObjectContext aufrufbar sein sollen, verschwinden. Dazu begeben wir uns wieder in den Model Browser und schauen uns den Bereich EntityContainer –> Entity Sets näher an. Hier wählen wir nun das gewünschte EntitySet aus. Nun können wir in den Eigenschaften den Getter Zugrifflevel definieren. Hier wählen wir Private aus.

image

Jetzt stehen uns im ObjectContext die EntitySets, welche wir auf Private gesetzt haben, nicht mehr für Abfragen zu Verfügung. Die Abfrage ist somit nur noch über die vorgegeben Stored Procedures möglich.

image

Fazit:

Zugegeben ist dieses Szenario eher selten in dieser extremen Form anzutreffen. Aber z.B. in besagten Projekt geht es leider nicht anders und so sollen andere Entwickler, gar nicht erst auf die Idee kommen die Abfragen auf die EntitySets direkt auszuführen. Mit dieser Lösung wird uns nur ein Weg zur Abfrage von Daten, für die Tabelle Customer, zur Verfügung gestellt.

Advertisements

Written by Robert Meyer

Dezember 9, 2010 at 20:18

Veröffentlicht in Entity Framework

Tagged with , , ,

Entity Framework: Der Einsatz von Updatable Views

with one comment

Ich stand heute vor dem Problem, das ich eine View in das Entity Framework integrieren musste. An sich ist das kein großen Problem, solang man die View nur zu Abfragezwecken benötigt. Möchte man jedoch auch ein Update, Insert oder Delete auf die View ausführen, so kommt man ohne manuelle Änderungen in der EDMX Datei schnell an seine Grenzen. Richtig problematisch wird es erst, wenn die View nicht nur eine Tabelle abfragt, sondern sich über zwei oder mehr Tabellen erstreckt.
Ich möchte an dieser Stelle eine prinzipielle Lösung zeigen wie man eine einfache View updatable macht und danach aufzeigen wie man das Problem mit einer komplexeren View über mehrere Tabellen löst.

Das Datenbankmodell

Ich habe mir für dieses Beispiel eine sehr einfach Datenbankstruktur geschaffen. Als Beispiel muss eine Kunden – Kundendetails 1:1 Beziehung herhalten. Die Tabellen haben auch je nur zwei Felder, was aber ausreichend ist um das Prinzip zu erklären.

image

Die einfache View

Als erstes werde ich das Beispiel mit einer einfachen View erläutern, welche alle Spalten der Tabelle Customer abfragt. Die Tabelle CustomerDetails wird vorerst nicht beachtet.

image

Als nächstes muss mit dem Visual Studio ein neues Projekt (Commandline reicht vollkommen aus) erzeugt werden. Hier wird nun ein neues Entity Framework Data Model hinzugefügt, nennen wir es CRM, welches aus der Datenbank heraus generiert wird. Wir fügen im folgenden Assistenten die View vwSimpleCustomer dem Entity Data Model hinzu.

Änderungen im EDMX

Als nächstes müssen wir ein paar Änderungen in der EDMX Datei vornehmen, damit wir später auf die View Create Update und Delete (CUD) Anweisungen absetzen können. Hierzu öffnen wir die Entity Framework Datei in dem mit Visual Studio mitgelieferten XML Editor.
In der EDMX Datei müssen wir die Bereiche des StorageModels und des ConceptualModels bearbeiten. Unsere View ist im StorageModel wie folgt definiert:

image

Nach unseren Anpassungen schaut es schon etwas anders aus.

image

  1. Wir haben den store:Type von Views auf Tables geändert. Somit behandelt er die View von nun an wie eine Tabelle.
  2. Wir haben bei store:Schema=”dbo” den prefix store: entfernt
  3. Wir haben das Attribut store:Name komplett entfernt
  4. Wir haben das Element DefinfingQuery komplett entfernt, da die View von nun an wie eine Tabelle genutzt wird

Als nächstes muss den EntityType der View anpassen. Hier werden vom Entity Framework beim anlegen der View alle Felder als PrimaryKey Felder deklariert. Dies muss geändert werden, so das nur noch die Spalte ID als PrimaryKey angegeben ist.

imageUnd danach schaut es so aus:

image 

Das gleiche muss auch noch im EntityType der View im ConceptualModel der EDMX Datei angepasst werden. Danach haben wir eine View welche mit CUD Anweisungen umgehen kann.

Die komplexere View

Die komplexere View wird sich nun über beide Tabellen erstrecken. Die Anpassungen in der EDMX Datei sind genau die gleich wie auch bei der einfachen View.
Versucht man jedoch ein Update, Insert oder Delete auf eine View, die sich über mehrere Tabellen erstreckt, auszuführen, so erhält man eine Exception mit dem Hinweis das dies nicht möglich ist.

Die Lösung ist relativ einfach. Man legt sich für jede CUD Anweisung im SQL Server eine Stored Procedure an. Diese Stored Procedures werden dann unter Function Mapping der View der jeweiligen CUD Anweisung zugewiesen.

image

Fazit

Das ganze ist leider nur eine sehr temporäre Lösung, das die EDMX Datei beim nächsten Update des Models aus der Datenbank heraus aktualisiert und überschrieben wird. Somit erkennt er zum Beispiel das die View ja gar keine Tabelle ist 🙂

Man sollte sich auf jedenfall überlegen anstatt mit Views lieber mit richtigen Tabellen zu arbeiten. Manchmal lässt es jedoch eine gewachsene Struktur noch zu, direkt mit den Tabellen zu arbeiten. In diesem Fall findet Ihr in diesem Blog Post einen Lösungsansatz.

Written by Robert Meyer

Juli 16, 2010 at 16:08

Veröffentlicht in Entity Framework

Tagged with , , , ,

Entity Framework: POCO Template mit .NET 3.5

with one comment

Mit Hilfe des Visual Studio 2010 RC1 und dem T4 POCO Template ist es nun möglich auch für das Entity Framework mit der .NET Version 3.5 POCO Klassen zu erstellen. Dazu startet man Visual Studio 2010 RC1 und erstellt zuerst ein neues Projekt, in unserem Beispiel wird das eine Konsolenanwendung sein. Wichtig hierbei ist, das als Framework unbedingt .NET 3.5 ausgewählt wird.

image

Nach dem erstellen des Projekts, lege ich mir ein neues Entity Framework Objekt an, hierfür nutze ich wie immer in meinen Beispielen die Chinook Datenbank. Öffnen Sie das neu erstellte Model und klicken Sie mit der rechten Maustaste in einen freien Bereich im Designer. Im sich nun öffnenden Kontextmenu wählen Sie den Eintrag “Add code generation item …” aus. Jetzt sollte sich folgendes Fenster öffnen:

image

Leider finden wir hier keinen Eintrag, wie wir das vielleicht aus .NET 4.0 gewöhnt wären, um mit T4 POCO Klassen zu erzeugen. Klickt man nun auf der linken Seite auf Online Templates, so werden alle vorhandenen Onlinetemplates geladen. Eines davon ist der “ADO.NET C# POCO Entity Generator”, welchen wir auswählen werden.

image 

Der POCO Generator erstellt uns nun ein T4 Template aus dem heraus die POCO Klassen für unser Entity Framework erzeugt werden. Schaut man nun in die C# Codedatei welche sich hinter dem EDM Designer befindet, so sehen wir das diese leer ist. Dies ist eine sehr interessante und saubere Lösung um mit Hilfe von T4 dynamische POCO Klassen für das Entity Framework zu erzeugen. Schade ist nur, das diese Lösung nur mit dem Visual Studio 2010 funktioniert und nicht mit Visual Studio 2008.

Leider gibt es für Freunde der verteilten Anwendungen, z.B. mit WCF Services, einen Nachteil. Die generierten POCO Klassen werden nicht automatisch als DataContract deklariert und können somit nicht als Data Transfer Objects (DTO’s) eingesetzt werden. Abhilfe könnte hierfür das Anpassen der T4 Templates schaffen.

Written by Robert Meyer

März 30, 2010 at 16:07

Veröffentlicht in Entity Framework

Tagged with , , , ,

VSone 2010: ADO.NET Entity Framework Unterlagen

leave a comment »

Es hat leider einen klein wenig gedauert, aber hier stehen nun die Unterlagen zu dem ADO.NET Entity Framework Workshop zum Download bereit. In der gepackten Datei findet ihr folgendes:

  • ADO.NET Entity Framework – Präsentation
  • ADO.NET Entity Framework Workshop Mindmap
  • Beispielprojekte z.B. für
    • ADO.NET 2.0 Datenzugriff
    • LINQ to …
    • Stored Procedures mit dem Entity Framework
    • Self Tracking Entities mit dem EF 4.0
    • CRUD Anweisungen mit LINQ to SQL
    • Forward Engineering mit LINQ to SQL
    • WCF Data Service mit SQL Azure
    • Sync Framework zum synchronisieren von zwei Ordnern

Den Download findet ihr auf Microsoft SkyDrive. Wenn ihr Fragen habt, so könnt ihr euch gern an mich wenden.

Ich halte diesen Vortrag auch gern in diversen .NET User Groups, wenn ihr Interesse habt könnt ihr euch ebenfalls melden. Ganz egal wo sich eure User Group befindet.

Written by Robert Meyer

März 3, 2010 at 20:51

Veröffentlicht in Entity Framework, Sonstiges

Tagged with , , ,

LINQ: Compiled vs. Non-Compiled Queries

with one comment

Ich hatte auf der VSone in München, eine interessante Diskussion bei meiner Session auf der Business Bühne zum Thema “Zukunft und Entwicklung des Entity Frameworks”. Ein Teilnehmer wollte wissen wie man Abfragen mit LINQ to Entities optimieren kann. Eine der Antworten von mir war: Compiled Queries. Jedoch stellte es sich als komplizierter heraus, als es sich zuerst anhörte, da man einige Vorgehensweisen beachten muss. Was ist eigentlich der Unterschied zwischen vorkompilierten und nicht vorkompilierten Queries? Man muss sich beim Einsatz von LINQ to Entities bewusst sein, das der Weg vom LINQ Statement zum eigentlich ausgeführten Query (z.B. T-SQL) sehr lang ist.

Wer sich nun ein wenig mit dem SQL Server auskennt, der weiß das T-SQL Statements wie das folgende relativ langsam sind, wenn man sie mehrfach ausführt.

SELECT Customer.CustomerID, FirstName, LastName, Quantity, Price, Quantity * Price AS [TotalPrice]
FROM SalesDetails
JOIN Customer
ON SalesDetails.CustomerID = Customer.CustomerID
WHERE Quantity > 100

Wieso ist das so? Da der Ausdruck in der WHERE Bedingung kein Parameter ist, erstellt der SQL Server Abfragenoptimierer bei jedem Aufruf dieses Statements einen neuen Abfrageplan. Würde ich an dieser Stelle mit Parametern arbeiten, so würde er beim ersten mal ausführen das Statement kompilieren und den Abfrageplan speichern. Diesen würde der SQL Server bei diesem Statement immer wieder benutzen. Stellt man sich vor das diese Abfrage vielleicht mehrere hundertmal benutzt wird, so dürfte sofort klar sein das sich daraus ein Vorteil in der Abfragegeschwindigkeit ergibt.

Der QueryCompiler des Entity Frameworks verhält sich im Prinzip ganz genauso. Ich habe aber die Möglichkeit über die statische Klasse CompiledQuery mit der Funktion Compile() eine LINQ Abfrage kompilieren. CompiledQuery.Compile() gibt ein Delegate in Form einer Func zurück. Dieser Delegate kann dann zu einem beliebigen Zeitpunkt ausgeführt werden.

Wichtig! Es führt jedoch zu Einbrüchen in der Performance wenn man diese LINQ Abfrage bei jedem Aufruf kompiliert. Das ist ein Fehler, der leider sehr häufig gemacht wird.

Ich werde nun Anhand eines Beispiels erklären, wie man mit dem Entity Framework und LINQ to Entities kompilierte LINQ Abfragen erstellen und nutzen kann. Als erstes legen wir uns dazu ein private Delegate an, wo wir die kompilierte LINQ Abfrage speichern werden.

private_delegate

Func hat hier drei Parameter. Die ersten beiden Parameter stehen für die Inputparameter. Hierbei steht der erste Parameter für den ObjectContext des Entity Frameworks und der zweite Parameter ist der Ausdruck für meine WHERE Bedingung. Der dritte Parameter hingegen ist der Typ des Rückgabewertes.

Jetzt benötige ich eine Funktion in der ich meine LINQ Abfrage kompiliere und dem Delegate zuweise. Danach kann ich den Delegate mit einem Invoke(param1, param2) ausführen.

compiledQueryFunction

Hier wird als erstes geprüft ob das LINQ Statement bereits kompiliert ist, wenn nicht wird es kompiliert und dem Delegate zugewiesen. Danach kann es beliebig oft im kompilierten Zustand ausgeführt werden.

Wer nun eine Stoppuhr zwischen seine Abfragen baut, wird merken das es beim ersten Abfragevorgang länger dauert als mit der unkompilierten Abfrage. Wieso? Bei dem ersten Ausführen der Abfrage muss die Abfrage erst kompiliert werden und braucht somit etwas mehr Zeit. Vergleicht man die Zeiten bei nachfolgenden Abfragen und die Gesamtzeit, bei z.B. 20 000 Abfragen, so wird auffallen das mit einer kompilieren Abfrage deutlich bessere Zeiten erreicht werden können.

Es sollte jedoch beachtet werden, das kompilierte Abfragen nicht immer sinnvoll sind. Führe ich eine Abfrage nur einmal oder wenige Male aus, so habe ich evtl. deutlich schlechtere Zeiten. Vor allem sollte aber beachtet werden, das kompilierte Abfragen irgendwo gespeichert werden müssen. Hier wird der Arbeitsspeicher belastet, was bei sehr vielen großen Abfragen relativ schnell zu einem deutlichen Speicherverlust führen kann.

Für Beispielprojekte oder weitere Infos, könnt ihr gerne Kontakt mit mir aufnehmen.

Written by Robert Meyer

Februar 25, 2010 at 21:09

Neue Features des Entity Frameworks 4.0

leave a comment »

Der Zeitpunkt rückt immer näher an dem Visual Studio 2010 und damit .NET 4.0 erscheinen wird. Inzwischen sind wir schon beim Release Candidate 1 des Visual Studios 2010, welcher frei zum Download bereit steht. Deshalb ist es an der Zeit eine Übersicht über die wichtigsten neuen Features des Entity Framework 4.0 zu geben.

Model First Design

Das Entity Framework 1.0 unterstützte nur Reverse Engineering. Das bedeutet man kann aus einer bestehenden Datenbank ein Entity Data Model erzeugen. Nun gibt es mit dem Entity Framework 4.0 auch die Möglichkeit des Forward Engineerings. Ich erzeuge mir zuerst mein Domain Model und kann dann zu einem beliebigen Zeitpunkt meine Datenbank erzeugen. Hierbei wird eine DDL Skript erzeugt, welches dann auf dem jeweiligen SQL Server (oder ein anderen Datenbankserver) ausgeführt werden muss. Ein was gibt es jedoch zu beachten: Das Entity Framework 4.0 unterstützt kein Round Trip Engineering. Dadurch wird die Datenbank bei jeder Änderung im Domain Model und der darauffolgenden DDL Skripterstellung komplett gelöscht und anschließend neu erstellt.

Foreign Keys

Mit dem Entity Framework 4.0 ist es möglich einen Datensatz weitere abhängige Datensätze anzuhängen ohne den Quelldatensatz laden zu müssen. Hierbei wird nur über den ForeignKey gearbeitet. Deshalb gibt es bei jeder im Entity Framework 4.0 erstellen Entität auch den Fremdschlüssel zu Referenztabellen. Zum Beispiel hat eine Tabelle OrderDetails eine Abhängigkeit zu der Tabelle Orders. Möchte ich der Tabelle OrderDetails einen Datensatz hinzufügen, so muss ich nicht den kompletten Datensatz aus der Orders Tabelle laden. Hier reicht es wenn ich bei dem OrderDetails Datensatz einfach die eindeutige ID für den Orders Datensatz mit angebe. Somit erspare ich mir unnötiges das unnötige Laden der Abhängigkeiten.

Lazy Loading

Wer mit dem Entity Framework 1.0 gearbeitet hat kennt das Problem sicherlich. Zum Nachladen von Daten musste man immer explizit die Load() Funktion aufrufen. Hier war LINQ to SQL dem Entity Framework um einiges voraus. LINQ to SQL lädt die Daten dynamisch bei Aufruf nach. Jedoch hat LINQ to SQL das Problem das sich dieses dynamische nachladen nicht deaktivieren lässt.
Das ändert sich jedoch mit der Entity Framework Version 4.0, hier kann der Entwickler ein dynamisches Nachladen explizit aktivieren und deaktivieren. Nun muss nicht mehr die Load() Funktion aufgerufen werden. Es reicht wenn man auf die jeweilige Entität zugreift um die Daten nachzuladen.

Self Tracking Entities für bessere N-Tier Unterstützung mit POCO Klassen

Durch die Unterstützung von Self Tracking Entities können nun mit dem Entity Framework 4.0 POCO Klassen erstellt werden. Dies geschieht über eine Codeerstellungsvorlage mit dem Text Template Transformation Toolkit (T4). Hier werden zwei Vorlagen erstellt, eine für die Entitätsklassen und eine für den Kontext. Dadurch ist es möglich den Kontext und die Entitätsklassen in separate Assemblies zu verlagern. Self Tracking Entities erlauben es im Gegensatz zu den Entitätsklassen im Entity Framework 1.0 auch im losgelösten Zustand (Detached) Änderungen an den Daten zu protokollieren. Somit kann man im Entity Framework 4.0 auf Data Transfer Objects (DTO) verzichten, da die Entitätsklassen in keiner Abhängigkeit mehr zu dem Entity Framework stehen.

Erweiterte Stored Procedure Unterstützung

Endlich kann man auch Stored Procedure mit einem Rückgabewert von Typ Void oder Skalar ohne Einschränkungen benutzen! Hinzugekommen ist ein neuer Rückgabewert, der Complex Type. Der Complex Type erlaubt es für einen Rückgabewert der nicht Skalar ist, aber auch auf keine Entitätsklasse passt, einen komplexen Datentyp anzulegen. Dieser komplexe Datentyp kann dann im Entity Framework genutzt werden und z.B. als Attribut in eine Entitätsklasse eingefügt werden. Mehr Infos dazu findet ihr in einem extra Blogeintrag.

Written by Robert Meyer

Februar 16, 2010 at 10:20

Entity Framework 4.0: Automatisch generierter Return Type

with one comment

Im Entity Framework 4.0 welches mit dem .NET Framework 4.0 ausgeliefert wird, kommt eine sehr spannende Neuerung. Es ist ja allgemein bekannt, das die Funktionalität von Stored Procedures im Entity Framework 1.0 nur sehr rudimentär umgesetzt wurde. Stored Procedures ohne bzw. mit Scalar Rückgabewerten wurden standardmäßig nicht unterstützt und waren erst nach einigen Modifikationen im XML Code möglich.

Diese Funktionalitäten sind im Entity Framework 4.0 nun voll integriert und desweiteren kommt noch ein neuer Rückgabewert hinzu, der sogenannte Complex Type. Complex Types erlauben das Gruppieren von Properties welche man in verschiedenen Entities / Klassen wiederverwenden kann. Somit ermöglichen Complex Types eine saubere Kapselung. Ebenso werden Complex Types genutzt um Rückgabewerte einer Stored Procedure in Objekten abbilden zu können, falls der Rückgabewert nicht auf das Muster einer bereits existierenden Klasse (Entity) zutrifft.

Ich möchte das ganze an einem Beispiel mit der Chinook Datenbank erläutern. Was die Chinook Datenbank ist, kann man hier finden und downloaden kann man diese auf Codeplex.

In unserem Beispiel werden wir nun mit der folgenden Stored Procedure arbeiten.

 stored_procedure

Diese Stored Procedure gibt die Details einer Bestellung zurück, welche vom Muster auf keine bestehende Klasse (Entity) passt. Deshalb werden wir hier mit einem Complex Type arbeiten.

execute_function_import

Als nächstes importieren wir die Stored Procedure in unser Entity Data Model.

Über den Eintrag “Add Function Import…” im Contextmenu wird die Stored Procedure dem Entity Data Model hinzugefügt. In dem nun erscheinenden Fenster kann man die Stored Procedure im SQL Server, den Namen der Storen Procedure im Entity Data Model und den jeweiligen Rückgabetyp auswählen.

Wir erwarten in unserem Beispiel einen Complex Type.

 

add_function_import

Klickt man auf den Button “Get Column Information” im Bereich “Stored Procedure Column Information”, so bekommt man eine Liste mit allen Rückgabespalten angezeigt. Über den Button “Create Complex Type” erstellt das Entity Framework einen Complex Type mit den Feldern:

  • TrackName
  • Quantity
  • UnitPrice
  • ExtendedPrice (Quantity * UnitPrice)

Dieser Complex Type kann nun im Code ganz normal instanziert und genutzt werden wie jede andere Klasse (Entity) des Entity Frameworks.

Written by Robert Meyer

Dezember 31, 2009 at 13:20