Stored Procedure mit skalaren Rückgabewert im Entity Framework

Will man Stored Procedures über das Entity Framework aufrufen, kann man das – wenn man denn ein EntitySet erwartet – recht einfach. Erwartet man aber eine skalare Variable (Int32, String, Guid…) weiter als Response, gibt es leider ein bekanntes Problem, dass dafür kein Code generiert wird.

Um dennoch eine solche Procedure verwenden zu können, müssen einige Einträge in dem Model von Hand getätigt und im Code muss eine eigene EntityConnection implementiert werden, die für den StoredProcedure-Aufruf eine temporäre Verbindung aufbaut. Um das zu verdeutlichen, hier meine Herangehensweise in einem Testprojekt.

In der Datenbank erstelle ich mir vorerst eine Stored Procedure namens GetPersonenId. Diese Procedure erwartet zwei Parameter, zum einen eine Id, und zum anderen einen boolschen Wert. Was genau in der SP passieren soll, ist für dieses Beispiel nicht relevant.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

USE [TestDB2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetPersonenId]
	@Root uniqueidentifier ,
	@Rekursiv bit
AS
BEGIN
	SET NOCOUNT ON;
 
	If @Rekursiv = 0
	BEGIN
		select PersonenId
		from Personen
		where ChildId = @Root
	End
 
	Else
	Begin
		-- rekursive abfrage...
	End
END

Nun füge ich meinem Projekt ein ADO.NET Entity Data Model hinzu und wähle aus meine Datenbankverbindung meine Datenbank aus.

Im kommenden Dialog wähle ich hier unter den “Gespeicherten Prozeduren” meine SP aus.

Das nun erstellte Model schliesse ich und öffne das auch gleich wieder mit den integrierten XML-Editor, dazu ein Rechtsklick auf das Model im Projektmappen-Explorer und auf “Öffnen mit…” Hier wähle ich dann den XML Editor zum öffnen des Modells.

Hier findet man den generierten Code kommentiert mit den jeweiligen Bereichen (SSDL, CSDL, C-S mapping). Im Knoten edmx:StorageModels (SSDL content)  sollte bereits, eine vom EF definierte Funktion existieren, die den Namen der Procedure trägt.

1
2
3
4

<Function Name="GetPersonenId" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="false" ParameterTypeSemantics="AllowImplicitConversion" Schema="dbo">
  <Parameter Name="Root" Type="uniqueidentifier" Mode="In" />
  <Parameter Name="Rekursiv" Type="bit" Mode="In" />
</Function>

Die kann so belassen werden. Im Knoten edmx:ConceptualModels (CSDL content) fügen wir ein FunktionImport Knoten hinzu, der den Rückgabetypen und die Parameter wie diese in der StoredProcedure definiert wurden. Wenn der Knoten EntityContainer kein schliessendes Tag hat sondern /> endet, muss dieser so geändert werden.

1
2
3
4

<FunctionImport Name="GetPersonenId" ReturnType="Collection(Guid)">
  <Parameter Name="Root" Type="Guid" Mode="In" />
  <Parameter Name="Rekursiv" Type="Boolean" Mode="In" />
</FunctionImport>

Im Knoten edmx:Mappings (C-S mapping content) ergänzen wir im Knoten EntityContainerMapping den Knoten FunctionImportMapping. Der kommende FunctionName im Code  ist der im Schema definierte Namespace plus der Name von der SP. Auch hier wichtig, wenn der Knoten EntityContainerMapping kein schliessendes Tag hat sondern /> endet, muss dieser so geändert werden.

1

<FunctionImportMapping FunctionImportName="GetPersonenId" FunctionName="PersonenModel.Store.GetPersonenId" />

Das edmx-File kann nun gespeichert und geschlossen werden und wir wechseln in die Klasse wo wir die Methode bauen möchten, die die Prozedur  aufruft. Jetzt verweisen wir noch auf System.Configuration um den ConfigurationManager für die Übergabe des ConnectionStrings aus der App.Config. Der Connectionstring kann aber auch direkt in die Variable connection geschrieben werden…

Hier nun der Code, in den Kommentare werden die einzelnen Zeilen kommentiert.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54

public static IEnumerable<Guid> ReadPersonenIdStruktur(Guid root, bool recursive)
{
    //Liste instanziieren
    var personen = new List<Guid>();
 
    //connectionstrring aus der app.config holen
    string connection = ConfigurationManager.ConnectionStrings["PersonenEntities"].ConnectionString;
 
    //context verwenden
    using (var conn = new EntityConnection(connection))
    {
        try
        {
            //connection oeffnen
            conn.Open();
 
            //EntityCommand instanzieren
            EntityCommand cmd = conn.CreateCommand();
 
            /* der comandtext setzt sich aus den entities-namen der
             * bei der Erstellung des Models festgelegt wurde
             * plus den Namen der Stored Procedur                     *
             */
            cmd.CommandText = "PersonenEntities.GetPersonenId";
 
            //commandtype festlegen
            cmd.CommandType = CommandType.StoredProcedure;
 
            //die parameter die erwartet werden von der sp dem Kommando hinzufuegen
            cmd.Parameters.AddWithValue("Root", root);
            cmd.Parameters.AddWithValue("Rekursiv", recursive);
 
            //ausfuehren
            var result = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
 
            //ergebnisse in die liste schreiben
            while (result.Read())
            {
                personen.Add((Guid)result.GetValue(0));
            }
        }
        catch (Exception ex)
        {
            //Fehlerbehandlung
        }
        finally
        {
            //connection schliessen und wegschmeissen
            conn.Close();
            conn.Dispose();
        }
    }
    return personen;
}

Wenn noch Fragen offen sind, versuche ich zu helfen. Ansonsten wie immer,

viel Spass beim entwickeln : )