Monthly Archives: Wrzesień 2012

Exam 70-516: Accessing Data with Microsoft .NET Framework 4

2012-09-27

W dniu dzisiejszym podszedłem do tytułowego egzaminu i udało mi się go zdać z wynikiem 953 punktów. Czy egzamin był trudny? Myślę, że dla osoby zajmującej się na co dzień tymi zagadnieniami nie. Ja na przygotowanie poświęciłem ponad dwa tygodnie, siedziałem zarówno nad teorią jak i ćwiczeniami praktycznymi. Dzięki temu do egzaminu przystąpiłem raczej spokojny. Większość pytań dotyczyła obszarów, w których czuję się mocny (ADO.NET, LINQ, Entity Framework, XML), były też takie gdzie musiałem trochę pogłówkować (POCO, Entity SQL) oraz takie gdzie odpowiadałem tylko na wyczucie (WCF Data Services). Grunt, że się udało :). Plany na przyszłość to zdobycie MCITP: Database Developer 2008 lub MCSA: SQL Server 2012.

Entity Framework – własne funkcje w zapytaniach LINQ to Entities

2012-09-23

Temat rozpocznę od przykładowego kodu będącego wstępem do omawianego zagadnienia. Zacznijmy od napisania prostej funkcji konwertującej wartość wyrażoną w milach na wartość w kilometrach:

public static int MilesToKilometers(int valueInMiles)
{
    return Convert.ToInt32(valueInMiles * 1.609344);
}

Poniżej znajduje się zapytanie LINQ wykorzystujące utworzoną funkcję do wyszukania samochodów o maksymalnej prędkości przekraczającej 200 km/h (pole TopSpeed zawiera prędkość w milach). Oczywiście lepszym rozwiązaniem byłoby bezpośrednie porównanie z wartością w milach (czyli 124) bez użycia funkcji, ale na potrzeby prezentacyjne przyjmijmy taki wariant za dopuszczalny:

var cars = from car in context.Cars
           where MilesToKilometers(car.TopSpeed) > 200
           select car;

foreach (var c in cars)
    Console.WriteLine(c.Model);

Pytanie, czy powyższy kod wykona się poprawnie? Odpowiedź brzmi: to zależy. W przypadku LINQ to Objects (gdzie kolekcja Cars byłaby np. listą) jak najbardziej tak. W przypadku LINQ to Entities mimo poprawnej kompilacji, przy próbie wykonania zapytania otrzymamy następujący komunikat (w tym miejscu warto przypomnieć, że zapytanie LINQ wykonywane jest dopiero w momencie odczytu jego wyników, a więc błąd zobaczymy w pętli foreach):

LINQ to Entities does not recognize the method ‚Int32 MilesToKilometers(Int32)’ method, and this method cannot be translated into a store expression.

Aby wyjaśnić przyczynę tego błędu, należy zrozumieć różnicę w wykonywaniu zapytań LINQ na różnych źródłach danych. W przypadku LINQ to Objects dla każdego elementu kolekcji wykonywana jest metoda MilesToKilometers i na podstawie warunku logicznego element taki trafia (bądź nie) do wyników zapytania. Mechanizm LINQ to Entities działa inaczej. Zapytanie najpierw tłumaczone jest na język SQL, po czym zostaje wysłane do bazy danych w celu wykonania. Po otrzymaniu odpowiedzi z serwera możemy korzystać z wyników. Z opisanym schematem działania wiąże się ważna zasada, w zapytaniach LINQ to Entities możemy używać jedynie takich konstrukcji, które mogą być przetłumaczone na język SQL. Metoda MilesToKilometers nie spełnia tego warunku i stąd pojawienie się powyższego błędu. Nie oznacza to jednak, że jesteśmy pozbawieni możliwości stosowania własnych funkcji w zapytaniach LINQ to Entities. Rozwiązaniem problemu jest zdefiniowanie takiej funkcji w modelu koncepcyjnym Entity Framework. Aby to zrobić wystarczy otworzyć plik *.edmx za pomocą wbudowanego w Visual Studio edytora XML i w elemencie Schema sekcji ConceptualModels umieścić definicję własnej funkcji:

<Function Name="MilesToKilometers" ReturnType="Edm.Int32">
  <Parameter Name="valueInMiles" Type="Edm.Int32" />
  <DefiningExpression>
    Cast(Round(valueInMiles * 1.609344) as Edm.Int32)
  </DefiningExpression>
</Function>

Definicja funkcji składa się z elementu Function zawierającego atrybuty z jej nazwą oraz typem zwracanego wyniku. Element ten zawiera także elementy Parameter określające nazwy i typy przekazywanych do funkcji parametrów oraz element DefiningExpression zawierający implementację funkcji w języku Entity SQL.

Po umieszczeniu definicji funkcji w modelu koncepcyjnym należy dodać do aplikacji metodę zmapowaną do utworzonej funkcji. Mapowanie realizowane jest poprzez atrybut EdmFunction (przestrzeń System.Data.Objects.DataClasses), w którym podajemy namespace modelu (atrybut Namespace elementu Schema zawierającego definicję funkcji) oraz nazwę funkcji:

[EdmFunction("TestModel", "MilesToKilometers")]
public static int MilesToKilometers(int valueInMiles)
{
    throw new NotSupportedException("Direct calls are not supported.");
}

W tym przypadku metoda dostępna będzie jedynie z poziomu zapytań, przy bezpośrednim wywołaniu zgłoszony zostanie wyjątek. Nic nie stoi jednak na przeszkodzie aby metoda zwierała zarówno atrybut mapowania jak i implementację logiki.

Po wykonaniu opisanych czynności możemy korzystać z własnej funkcji w zapytaniach LINQ to Entities:

using (TestEntities context = new TestEntities())
{
    var cars = from car in context.Cars
               where MilesToKilometers(car.TopSpeed) > 200
               select car;

    foreach (var c in cars)
        Console.WriteLine(c.Model);
}

Wykorzystując narzędzie SQL Server Profiler możemy sprawdzić jakie zapytanie wysyłane jest do bazy danych po uruchomieniu powyższego kodu. Oto wynik:

SELECT
[Extent1].[Id] AS [Id],
[Extent1].[Model] AS [Model],
[Extent1].[Engine] AS [Engine],
[Extent1].[MaxPower] AS [MaxPower],
[Extent1].[TopSpeed] AS [TopSpeed]
FROM [dbo].[Car] AS [Extent1]
WHERE  CAST( ROUND([Extent1].[TopSpeed] * cast(1.609344 as float(53)), 0) AS int) > 200

Jak widać, zmiana implementacji funkcji MilesToKilometers z języka C# na Entity SQL umożliwiła przetłumaczenie jej na język SQL i wykonanie po stronie bazy danych.

Więcej informacji o definiowaniu własnych funkcji: Define Custom Functions in the Conceptual Model, Call Model-Defined Functions in QueriesModel Defined Functions.

Entity Framework – wywoływanie procedur składowanych

2012-09-14

W dzisiejszym wpisie pokażę w jaki sposób za pośrednictwem Entity Framework możemy wywoływać procedury składowane zwracające wyniki w postaci wartości skalarnej lub poprzez parametr wyjściowy. Na początku stwórzmy w bazie danych dwie procedury składowane, pierwsza z nich zwraca wynik jako wartość skalarną, druga jako parametr wyjściowy:

create procedure Procedure1
    @number int
as
begin
    select @number * 2 as result
end

create procedure Procedure2
	@number int,
	@result int output
as
begin
	set @result = @number * 2
end

Następnie utworzone procedury należy dodać do modelu przechowywania (Storage Entity Model) poprzez wywołanie opcji Update Model from Database:

Kolejnym etapem jest dodanie powyższych procedur do modelu koncepcyjnego (Conceptual Entity Model) poprzez opcję Add -> Function Import (dla pierwszej procedury należy wskazać rodzaj zwracanego wyniku jako kolekcję wartości skalarnych):

Po tych czynnościach możemy już korzystać z utworzonych procedur składowanych.
Procedura zwracająca wartość skalarną:

using (TestEntities context = new TestEntities())
{
    int? result = context.Procedure1(10).FirstOrDefault();
    Console.WriteLine(result.ToString());
}

Procedura zwracająca wynik poprzez parametr wyjściowy (klasa ObjectParameter znajduje się w przestrzeni System.Data.Objects):

using (TestEntities context = new TestEntities())
{
    ObjectParameter result = new ObjectParameter("result", typeof(int));
    context.Procedure2(10, result);
    Console.WriteLine(result.Value);
}

Polecenie output – odczyt informacji o przetworzonych rekordach

2012-09-01

Od wersji SQL Server 2005 w języku T-SQL dostępne jest polecenie output. Możemy dołączyć je do instrukcji insert, update, delete lub merge w celu uzyskania informacji o rekordach przetworzonych w wyniku ich działania. Informacje te udostępniane są przez dwa zbiory danych: inserted oraz deleted – ich struktura odpowiada tabeli, na której wykonywana jest dana operacja. Zbiór inserted dostępny jest przy operacjach insert, update oraz merge i zawiera nową (zaktualizowaną) postać rekordów. Z kolei zbiór deleted udostępniany jest przy poleceniach update, delete oraz merge i przechowuje oryginalną postać rekordów. Mamy dwie możliwości wykorzystania polecenia output, możemy jedynie zwrócić wybrane informacje lub poprzez klauzulę into umieścić je w określonej tabeli lub zmiennej tabelarycznej.
Aby zaprezentować działanie omawianego polecenia stworzyłem dwie tabele: #Test, na której przeprowadzane będą określone operacje oraz #Log, w której zapisywana będzie ich historia:

create table #Test
(
	Id int Identity(1, 1),
	Name varchar(50)
)

create table #Log
(
	Id int,
	NameOld varchar(50),
	NameNew varchar(50),
	OperationType varchar(6),
	OperationTime datetime
)

Poniższy kod wstawia do tabeli #Test cztery rekordy i przy użyciu polecenia output informacje o nich umieszcza w tabeli #Log (jawne specyfikowanie pól tabeli docelowej nie jest konieczne):

insert into #Test(Name)
    output inserted.Id, inserted.Name, 'INSERT', GetDate()
    into #Log(Id, NameNew, OperationType, OperationTime)
values ('Name_1'), ('Name_2'), ('Name_3'), ('Name_4')

Oto zawartość tabel #Test oraz #Log:

Jak widać instrukcja output umożliwia odczyt wartości z pól zdefiniowanych jako identity.
Kolejny przykład dotyczy modyfikacji rekordów:

update #Test set Name = 'Name_44'
	output inserted.Id, inserted.Name, deleted.Name, 'UPDATE', GetDate()
	into #Log(Id, NameNew, NameOld, OperationType, OperationTime)
where Name = 'Name_4'

Zawartość tabel #Test oraz #Log:

Usuwanie rekordów:

delete from #Test
	output deleted.Id, deleted.Name, 'DELETE', GetDate()
	into #Log(Id, NameOld, OperationType, OperationTime)
where Name in ('Name_2', 'Name_3')

Aktualna zawartość tabel #Test oraz #Log:

W przypadku polecenia merge klauzula output dysponuje dodatkowym argumentem $action typu nvarchar(10). Argument ten udostępnia informacje o rodzaju wykonanej na danym rekordzie operacji (‚INSERT’, ‚UPDATE’ lub ‚DELETE’):

merge #Test as t
using
(
	select v.Id, v.Name
	from (values (1, 'Name_11'), (2, 'Name_55')) v(Id, Name)
) as s on s.Id = t.Id
when matched and t.Name <> s.Name then update set t.Name = s.Name
when not matched by target then insert (Name) values (s.Name)
when not matched by source then delete
output $action, deleted.Id as deletedId, deleted.Name as deteledName,
	inserted.Id as insertedId, inserted.Name as insertedName;

Oto zbiór zwrócony przez polecenie output oraz ostateczna postać tabeli #Test:

Więcej informacji na temat polecenia output: MSDN