[SQL|ORM] Entity Framework : Queries

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.