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