Przegląd zagadnień związanych z pisaniem zapytań w Entity Framework:
Wyrażenia SQL
Do wykonywania zapytań SQL korzystać można z metody
ExecuteStoreCommand wołanej na obiekcie kontekstu. Metodzie przekazujemy przez string treść zapytania, oraz przez tablicę zestaw parametrów zapytania, zwraca ona ilość zmienionych rekordów w bazie.
using (var context = new EntityFrameworkRecipesEntities())
{
string sql =
@"insert into dbo.Payment(Amount,Vendor)
values (@Amount, @Vendor)";
var args = new DbParameter[]
{
new SqlParameter() {ParameterName = "Amount", Value = 99.9M},
new SqlParameter() {ParameterName = "Vendor", Value = "Ace Plumbing"}
};
int rowCount = context.ExecuteStoreCommand(sql, args);
}
Jeżeli chcemy zwrócić kolekcję obiektów, korzystamy z metody
ExecuteStoreQuery<>.
using (var context = new EntityFrameworkRecipesEntities1())
{
var sql = "select * from dbo.Student where Degree = @Major";
var args = new DbParameter[]
{
new SqlParameter() {ParameterName = "Major", Value = "Masters"}
};
var students = context.ExecuteStoreQuery<Student>(sql, args);
}
Wyrażenia EntitySQL
Entity SQL jest modyfikacją znanego SQL na potrzeby Entity Framework. Do tego typu zapytań wykorzystuje się metodę
CreateQuery.
using (var context = new EntityFrameworkRecipesEntities2())
{
var esql = "select value c from Customers as c";
var customers = context.CreateQuery<Customer>(esql);
foreach (var customer in customers)
{
Console.WriteLine("{0} {1}", customer.Name, customer.Email);
}
}
Kluczowe jest słowo
value, które umożliwia mapowanie rezultatu bezpośrednio do typu Customer.
Entity SQL może się okazać także przydatny w momencie, gdy modelujemy encje na zasadzie dziedziczenia typu "Tabela per Typ" i chcemy otrzymać jedynie encje pewnego typu. Przykładowo dla hierarchii encji jak poniżej
zapytanie wygląda następująco:
using (var context = new EntityFrameworkRecipesEntities3())
{
var esql = "select value p from OfType(People,Querying.Teacher) as p";
var teachers = context.CreateQuery<Teacher>(esql);
Console.WriteLine("Teachers...");
foreach (var teacher in teachers)
{
Console.WriteLine("{0}, isProfessor:{1}", teacher.Name, teacher.IsProfessor);
}
}
Jako drugi parametr podajemy typ z CLR, który ma posłużyć jako filtr. Zatem w tym przykadku Querying jest nazwą namespace. Alternatywna wersja to:
var esql = "using Querying; select value p from OfType(People,Teacher) as p";
Zwracanie więcej niż jednej kolekcji danych przez procedurę składowaną
Mamy w bazie danych dwie tabele powiązane kluczem obcym, oraz procedurę składowaną, wybierającą wszystkie rekordy z obu tabel. Po dodaniu tabel i procedury do EDM, możemy skorzystać z tej procedury do pobrania dwóch kolekcji danych przy jednym wywołaniu. Niestety mapowanie takie jest nieco bardziej skomplikowane, niż przy wywoływaniu prostych zapytań SQL. Poniżej przykład procedury zwracającej dwa zestawy danych.
CREATE PROCEDURE [dbo].[GetBidDetails]
AS
BEGIN
SELECT * FROM Job
SELECT * FROM Bid
END
oraz sposób obsługi takiej procedury z poziomu Entity Framework:
using (var context = new EntityFrameworkRecipesEntities4())
{
var cs = @"data source=PC-MKL;initial catalog=EntityFrameworkRecipes;
integrated security=True;multipleactiveresultsets=True;
App=EntityFramework";
var conn = new SqlConnection(cs);
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "dbo.GetBidDetails";
conn.Open();
var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
var jobs = context.Translate<Job>(reader, "Jobs", MergeOption.AppendOnly).ToList();
reader.NextResult();
context.Translate<Bid>(reader, "Bids", MergeOption.AppendOnly).ToList();
foreach (var job in jobs)
{
Console.WriteLine("Job: {0}", job.JobDetails);
foreach (var bid in job.Bids)
{
Console.WriteLine("\tBid: {0} from {1}", bid.Amount, bid.Bidder);
}
}
}
Aby obsłużyć wiele kolekcji jako wynik procedury musimy skorzystać z obiektu klasy
SqlCommand zwracanej przez
SqlConnection. Mapowanie kolekcji zwróconej przez procedurę następuje przy pomocy metody
Translate, której podajemy, na który obiekt z kontekstu ma zostać zmapowany wynik. Opcja
AppendOnly zapewnia śledzenie zmian wykonanych na zwróconych obiektach. Polecenie
ToList() wymusza natychmiastowe wykonanie zapytania. Dzięki metodzie
NextResult przechodzimy do następnego zbioru wynikowego.
Porównania do kolekcji w pamięci
Pisząc zapytania często potrzebujemy filtrować wyniki po tym, czy wartości występują w pewnej kolekcji danych. W takiej sytuacji nie możemy bezpośrednio skorzystać z operacji
join w LINQ, natomiast możemy wykorzystać funkcję
Contains. Zapytanie takie przetransformuje się na SQL-owy INNER JOIN.
using (var context = new EntityFrameworkRecipesEntities5())
{
var cats = new List<string>() {"Programming", "Databases"};
var books = from b in context.Books
where cats.Contains(b.Category.Name)
select b;
foreach (var book in books)
{
Console.WriteLine("{0} : {1}", book.Category.Name, book.Title);
}
}
Profiler pokazuje następujące zapytanie SQL:
SELECT
[Extent1].[BookId] AS [BookId],
[Extent1].[Title] AS [Title],
[Extent1].[CategoryId] AS [CategoryId]
FROM [dbo].[Book] AS [Extent1]
INNER JOIN [dbo].[Category] AS [Extent2] ON [Extent1].[CategoryId] = [Extent2].[CategoryId]
LEFT OUTER JOIN [dbo].[Category] AS [Extent3] ON [Extent1].[CategoryId] = [Extent3].[CategoryId]
WHERE [Extent2].[Name] = N'Programming' OR [Extent3].[Name] = N'Databases'
Grupowanie po dacie
Mamy tabelę z kolumną typu
DATE i chcemy pogrupować wyniki po dacie w Entity Framework. Korzystając z LINQ nie możemy grupować bezpośrednio po danym property, ponieważ typ
DateTime z CLR nie przetłumaczy się bezpośrednio do SQL. Należy wykorzystać funkcję
TruncateTime.
var groups = from r in context.Registrations
group r by EntityFunctions.TruncateTime(r.RegistrationDate)
into g
select g;
Dane zostaną pogrupowane po dniach.
Grupowanie po wielu properties
Do grupowania po kilku properties można skorzystać z typów anonimowych.
var results = from e in context.Events
group e by new {e.State, e.City}
into g
select new
{
State = g.Key.State,
City = g.Key.City,
Events = g
};
Join na wielu kolumnach
Przykładowo dla dwóch encji jak poniżej
możemy dokonać takiej operacji przy pomocy LINQ w następujący sposób.
var orders = from o in context.C_Order
join a in context.C_Account on
new {Id = o.AccountId, City = o.ShipCity, State = o.ShipState}
equals
new {Id = a.AccountId, a.City, a.State}
select o;
Korzystamy z faktu, że porównanie dwóch typów anonimowych jest porównaniem na zasadzie porównywania par properties i zwróci true jedynie, gdy wszystkie properties są sobie równe.
