[SQL|ORM] Entity Framework : Stored Procedures

Procedury składowane to narzędzie, z którego bardzo często warto skorzystać. Przeniesienie niektórych obliczeń na serwer bazy danych może usprawnić działanie aplikacji i uporządkować ją z logicznego punktu widzenia. Ponieważ procedury "żyją" po stronie bazy danych, należy wiedzieć, jak wywoływać je z poziomu narzędzia ORM. Poniżej kilka praktycznych przykładów w EF:

Procedura zwracająca kolekcję encji

Dla tabeli Customer mamy przykładową procedurę składowaną:

CREATE PROCEDURE dbo.GetCustomers
(@Company VARCHAR(50), @ContactTitle VARCHAR(50))
AS
 BEGIN
  SELECT * FROM Customer
  WHERE (@Company IS null OR Company = @Company) AND
  (@ContactTitle is null or ContactTitle = @ContactTitle)
 END

Po dodaniu do EDM odpowiednich encji i procedury GetCustomers, przechodzimy do okna designera. Z menu dostępnego pod prawym przyciskiem myszy wybieramy opcję Add -> Function Import...
W menu należy podać nazwę procedury, oraz określić co ma zwracać ( w tym przypadku kolekcję encji typu Customer). To wszystko, teraz w kodzie możemy na obiekcie kontekstu wołać GetCustomers.

var allCustomers = context.GetCustomers("GoShopNow.com", "Sales Manager");
foreach (var customer in allCustomers)
{
    Console.WriteLine("\t{0}", customer.Name);
}

Procedura zwracająca wartości przez parametr

Przykładowa procedura przyjmująca jeden parametr wejściowy i dwa wyjściowe, zwracająca dodatkowo kolekcję encji.

CREATE PROCEDURE dbo.GetVehiclesWithRentals
(@date DATE,
@totalRentals INT OUTPUT,
@totalPayments DECIMAL(18,2) OUTPUT)
AS
 BEGIN
 SELECT @totalRentals = COUNT(*), @totalPayments = SUM(Payment)
 FROM dbo.Rental
 WHERE RentalDate = @date
 
 SELECT DISTINCT v.*
 FROM dbo.Vehicle AS v JOIN dbo.Rental AS r
 ON v.VehicleId = r.VehicleId 
END

Procedurę taką dodajemy w sposób analogiczny, jak poprzednio. Różnice pojawiają się w kodzie. Korzystamy z obiektów typu ObjectParameter

string reportDate = "2/2/2010";
var totalRentals = new ObjectParameter("totalRentals", typeof (int));
var totalPayments = new ObjectParameter("totalPayments", typeof(decimal));
var vehicles = context.GetVehiclesWIthRentals(DateTime.Parse(reportDate), 
    totalRentals, totalPayments);
foreach (var vehicle in vehicles)
{
    Console.WriteLine("{0} {1}, {2} year", vehicle.Manufacturer, vehicle.Model,
        vehicle.Year);
}
Console.WriteLine("Total Rentals: {0}", totalRentals.Value);
Console.WriteLine("Total Payments: {0}", totalPayments.Value);

Custom Functions

Custom Functions to funkcje definiowane na poziome storage model. Nie musimy zatem (być może nie mamy dostępu) tworzyć procedur na serwerze bazy danych, a możemy je dodać do SSDL. Zatem treść procedury dodajemy do schematu bazy <Schema></Schema> edytując plik.edmx jako XML. Na przykład można dodać taką procedurę:

<Function Name="MembersWithTheMostMessages" IsComposable="false">
  <CommandText>
    select m.*
    from dbo.Member m
    join
    (
    select distinct msg.MemberId
    from dbo.Message msg where DateSent = @datesent
    ) temp on m.MemberId = temp.MemberId
  </CommandText>
  <Parameter Name="datesent" Type="date" />
</Function>

Funkcję taką wykorzystuje się tam samo jak procedurę składowaną

var members = context.MemberWithTheMostMessages(today);

[SQL|ORM] Entity Framework : CSDL, MSL, SSDL

Pliki .edmx opisane językiem XML składają się z kilku części. Wszystkie zmiany wykonywane w Entity Designerze zapisywane są właśnie w plikach .edmx. Dane z CSDL, MSL i SSDL możemy zaszyć w assembly, bądź wyeksportować do wyjściowego katalogu zaznaczając opcję Metada Artifact Processing na Copy To Output Directory.

CSDL (Conceptual Schema Definition Language) - język opisujący encje relacje i funkcje, czyli schemat bazy danych na poziomie konceptualnym. Typy danych odpowiadają typom z CLR.

Przykładowy plik .csdl

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="EntityFrameworkRecipesModel" Alias="Self"
 xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation" 
 xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
  <EntityContainer Name="EntityFrameworkRecipesEntities1"
   annotation:LazyLoadingEnabled="true">
    <EntitySet Name="Poem" EntityType="EntityFrameworkRecipesModel.Poem" />
    <EntitySet Name="Poet" EntityType="EntityFrameworkRecipesModel.Poet" />
    <AssociationSet Name="FK_Poem_Poet" Association="EntityFrameworkRecipesModel.FK_Poem_Poet">
      <End Role="Poet" EntitySet="Poet" />
      <End Role="Poem" EntitySet="Poem" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="Poem">
    <Key>
      <PropertyRef Name="PoemId" />
    </Key>
    <Property Name="PoemId" Type="Int32" Nullable="false" />
    <Property Name="PoetId" Type="Int32" />
    <Property Name="Title" Type="String" MaxLength="Max" Unicode="false" FixedLength="false" />
    <Property Name="MeterId" Type="Int32" />
    <NavigationProperty Name="Poet" Relationship="EntityFrameworkRecipesModel.FK_Poem_Poet" 
      FromRole="Poem" ToRole="Poet" />
  </EntityType>
  <EntityType Name="Poet">
    <Key>
      <PropertyRef Name="PoetId" />
    </Key>
    <Property Name="PoetId" Type="Int32" Nullable="false" />
    <Property Name="FirstName" Type="String" MaxLength="50" Unicode="false" FixedLength="false" />
    <Property Name="MiddleName" Type="String" MaxLength="50" Unicode="false" FixedLength="false" />
    <Property Name="LastName" Type="String" MaxLength="50" Unicode="false" FixedLength="false" />
    <NavigationProperty Name="Poem" Relationship="EntityFrameworkRecipesModel.FK_Poem_Poet" 
      FromRole="Poet" ToRole="Poem" />
  </EntityType>
  <Association Name="FK_Poem_Poet">
    <End Role="Poet" Type="EntityFrameworkRecipesModel.Poet" Multiplicity="0..1" />
    <End Role="Poem" Type="EntityFrameworkRecipesModel.Poem" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Poet">
        <PropertyRef Name="PoetId" />
      </Principal>
      <Dependent Role="Poem">
        <PropertyRef Name="PoetId" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>

SSDL (Store Schema Definition Language) - język opisujący schemat bazy danych na poziomie zasobu, w którym dane są przechowywane (bazy danych). Typy propercji są typami z SQL.

Przykładowy plik .ssdl

<?xml version="1.0" encoding="utf-8"?>
<Schema Namespace="EntityFrameworkRecipesModel.Store" Alias="Self" 
  Provider="System.Data.SqlClient" ProviderManifestToken="2008" 
  xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
  xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <EntityContainer Name="EntityFrameworkRecipesModelStoreContainer">
    <EntitySet Name="Poem" EntityType="EntityFrameworkRecipesModel.Store.Poem" 
      store:Type="Tables" Schema="dbo" />
    <EntitySet Name="Poet" EntityType="EntityFrameworkRecipesModel.Store.Poet" 
      store:Type="Tables" Schema="dbo" />
    <AssociationSet Name="FK_Poem_Poet" Association="EntityFrameworkRecipesModel.Store.FK_Poem_Poet">
      <End Role="Poet" EntitySet="Poet" />
      <End Role="Poem" EntitySet="Poem" />
    </AssociationSet>
  </EntityContainer>
  <EntityType Name="Poem">
    <Key>
      <PropertyRef Name="PoemId" />
    </Key>
    <Property Name="PoemId" Type="int" Nullable="false" />
    <Property Name="PoetId" Type="int" />
    <Property Name="Title" Type="varchar(max)" />
    <Property Name="MeterId" Type="int" />
  </EntityType>
  <EntityType Name="Poet">
    <Key>
      <PropertyRef Name="PoetId" />
    </Key>
    <Property Name="PoetId" Type="int" Nullable="false" />
    <Property Name="FirstName" Type="varchar" MaxLength="50" />
    <Property Name="MiddleName" Type="varchar" MaxLength="50" />
    <Property Name="LastName" Type="varchar" MaxLength="50" />
  </EntityType>
  <Association Name="FK_Poem_Poet">
    <End Role="Poet" Type="EntityFrameworkRecipesModel.Store.Poet" Multiplicity="0..1" />
    <End Role="Poem" Type="EntityFrameworkRecipesModel.Store.Poem" Multiplicity="*" />
    <ReferentialConstraint>
      <Principal Role="Poet">
        <PropertyRef Name="PoetId" />
      </Principal>
      <Dependent Role="Poem">
        <PropertyRef Name="PoetId" />
      </Dependent>
    </ReferentialConstraint>
  </Association>
</Schema>

MSL(Mapping Specification Language) - język mapowania pomiędzy typami z SSDL i CSDL

Przykładowy plik .msl

<?xml version="1.0" encoding="utf-8"?>
<Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
  <EntityContainerMapping StorageEntityContainer="EntityFrameworkRecipesModelStoreContainer" 
    CdmEntityContainer="EntityFrameworkRecipesEntities1">
    <EntitySetMapping Name="Poem">
      <EntityTypeMapping TypeName="EntityFrameworkRecipesModel.Poem">
        <MappingFragment StoreEntitySet="Poem">
          <ScalarProperty Name="PoemId" ColumnName="PoemId" />
          <ScalarProperty Name="PoetId" ColumnName="PoetId" />
          <ScalarProperty Name="Title" ColumnName="Title" />
          <ScalarProperty Name="MeterId" ColumnName="MeterId" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
    <EntitySetMapping Name="Poet">
      <EntityTypeMapping TypeName="EntityFrameworkRecipesModel.Poet">
        <MappingFragment StoreEntitySet="Poet">
          <ScalarProperty Name="PoetId" ColumnName="PoetId" />
          <ScalarProperty Name="FirstName" ColumnName="FirstName" />
          <ScalarProperty Name="MiddleName" ColumnName="MiddleName" />
          <ScalarProperty Name="LastName" ColumnName="LastName" />
        </MappingFragment>
      </EntityTypeMapping>
    </EntitySetMapping>
  </EntityContainerMapping>
</Mapping>

Cała struktura CSDL, MSL i SSDL jest generowana przez Entity Designer, jednak nic nie stoi na przeszkodzie, by ręczenie edytyować fragmenty pliku .edmx do naszych potrzeb.