Bazy danych

Wszystko na temat baz danych: zarówno relacyjnych, wraz z narzędziami ORM, jak i nowego trendu NoSQL. Spis postów na temat ORM i SQL

NoSQL

MongoDB

• Wprowadzenie
• MongoDB + Node.js: mongoose
• Klient C#
• Queries

RavenDB

• Wprowadzenie
• CRUD
• Queries

Redis

• Wprowadzenie
• Typy danych i funkcjonalności
• Klient C#

ORM

Entity Framework

• Entity Data Model - podstawy
• Database First
• Queries
• Loading Entities
• CSDL, MSL, SSDL
• Plain Old CLR Objects
• Stored Procedures
• Obiekty Entity Framework
• Code First

SQL

SQL Server i T-SQL

• Common Table Expression
• Import / eksport danych z Excela

[SQL|ORM] Entity Framework : Code First

Code First to rozwiązanie zaproponowane w EF 4.1, wydanym w kwietniu 2011. Nie jest zatem częścią .NETa 4.0 i VS 2010. Aby skorzystać z tej wersji EF w Visual Studio należy doinstalować najnowszą wersję EF (obecnie 5.0), najlepiej przy użyciu menadżera pakietów NuGet.

W podejściu CodeFirst, modelem danych stają się klasy użytkownika, które można przetransformować na tabele relacyjnej bazy danych. Główna różnica polega na tym, że nie mamy już do czynienia z plikami .edmx. Metadane dla klas tworzone są w pamięci, a nie w plikach xmlowych. Tabele, które zostaną stworzone możemy konfigurować przy użyciu atrybutów nad propercjami lub całymi klasami. Drugie podejście to tzw. Fluent API, czyli konfiguracja poprzez wywołania specjalnych funkcji.

Poniżej prosty przykład jak stworzyć tabelę w CodeFirst. Domyślnie, jeżeli nie skonfigurujemy connectionString, utworzy nam się baza danych w SQL Express o nazwie złożonej z nazwy namespace i nazwy klasy kontekstu.

public class Blog
    {
        public Blog()
        {
            Posts = new List<Post>();
        }

        public int Id { get; set; }
        public string Title { get; set; }
        public string BloggerName { get; set; }
        public List<Post> Posts { get; set; }
        public string BlogCode
        {
            get { return Title.Substring(0, 1) + ":" + BloggerName.Substring(0, 1); }
        }
    }

public class Post
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public string Content { get; set; }
        public virtual List<Blog> Blogs { get; set; }
    }

public class Context : DbContext
    {
        public DbSet<Blog> Blogs { get; set; }
        public DbSet<Post> Posts { get; set; }
    }

var db = new Context();
var blog = new Blog() {BloggerName = "Julie", Title = "EFBlog"};
db.Blogs.Add(blog);
db.SaveChanges();

Klasę kontekstu tworzymy dziedzicząc po typie DbContext wprowadzonym również w EF4.1. Klucze główne dla encji ustawiane są przez konwencję, tak więc np. dla klasy Blog, kluczem głównym będzie property o nazwie Id lub BlogId. Dla tabeli Post utworzony zostanie również domyślnie klucz obcy do tabeli Blog. Ponadto warto skonfigurować program tak, aby przy każdej zmianie modelu baza tworzyła się na nowo. Przy starcie aplikacji dodać należy następujące wyrażenie.

Database.SetInitializer(new DropCreateDatabaseIfModelChanges<Context>());

Gdzie Context to nazwa klasy dziedziczącej po DbContext.

Data Annotations

Data Annotations daje możliwość konfiguracji klas domenowych. W tym podejściu konfiguracja odbywa się poprzez atrybuty. Poniżej lista ciekawszych atrybutów

• [Key] -  klucz główny na kolumnie nie obejmowanej konwencją
• [MaxLength(int)] - ustawienie dotyczące ilości znaków dla danej kolumny
• [Required] - mapowane na NOT NULL w SQL
• [Column(String)] - mapowanie na kolumnę o danej nazwie
• [Table(String)] - klasa mapowana na tabelę o podanej nazwie
• [DatabaseGenerated(DatabaseGeneratedOption)] - można tutaj ustawić np Identity dla klucza głównego
• [NotMapped] - nie transformowane do bazy danych
• [ComplexType] - typy złożone jako propercje, poszczególne properties takiego typu zostaną przeklejone do tabeli odpowiadającej encji, w której taki typ się znajduje.
• [ForeignKey(String)] - nazwa klucza obcego umieszczana nad referencją

public class Blog
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required]
    public string Title { get; set; }
    [MaxLength(30)]
    public string BloggerName { get; set; }
    public List<Post> Posts { get; set; }
    [NotMapped]
    public string BlogCode
    {
        get { return Title.Substring(0, 1) + ":" + BloggerName.Substring(0, 1); }
    }
}

Fluent API

Wszystkie opcję dostępne z poziomu DataAnnotations  można konfigurować także przy użyciu tzw. FluentAPI, czyli po prostu korzystając z odpowiednich klas i metod.Dzięki takiemu podejściu nasze "Domain Objects" pozostaną niezależne od konfiguracji bazy danych.

public class Context : DbContext
{
    public DbSet<Blog> Blogs { get; set; }
    public DbSet<Post> Posts { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        var blog = modelBuilder.Entity<Blog>();
        blog.Property(b => b.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
        blog.Property(b => b.Title).IsRequired();
        blog.Property(b => b.BloggerName).HasMaxLength(30);
        blog.Ignore(b => b.BlogCode);
        base.OnModelCreating(modelBuilder);
    }
}

FluentAPI udostępnia także kilka dodatkowych opcji niedostępnych z poziomu DataAnnotations, na przykład możliwość dzielenia encji na wiele tabel lub łączenia wielu encji w jedną tabelę.

[SQL|ORM] Entity Framework : Obiekty Entity Framework

Obiekty EF można dostosować do potrzeb aplikacji na bardzo wiele sposobów. W tym poście kilka użytecznych przykładów w tym temacie.

Wykonywanie własnego kodu, podczas wywołania metody SaveChanges

Wystarczy nadpisać metodę SaveChanges z obiektu kontekstu. Stan obiektów dostępny jest w ObjectStateManager.

public override int SaveChanges(SaveOptions options)
        {
            Console.WriteLine("Saving Changes");
            var applicants = this.ObjectStateManager
                                .GetObjectStateEntries(EntityState.Deleted)
                                .Select(e => e.Entity)
                                .OfType<Applicant>().ToList();
            int changes = base.SaveChanges(options);
            Console.WriteLine("\n{0} applicants deleted",
                applicants.Count().ToString());

            foreach (var applicant in applicants)
            {
                File.Delete(applicant.ResumePath);
                Console.WriteLine("\n{0}File deleted at {1}", applicant.Name,
                    applicant.ResumePath);
            }
            return changes;
        }

Reguły walidacyjne dla propercji 

Aby dodać prostą regułę walidacyjną należy na obiekcie encji nadpisać metody zgodne z nazwą property. Metody te generowane są przez EF. Na przykład dla property UserName mamy możliwe OnUserNameChanging oraz OnUserNameChanged. W tych metodach mamy dostęp do encji, więc możemy modyfikować jej pola. Metody takie wołane są nie tylko przy zmianie wartości property ale także przy materializowaniu encji podczas jej pobierania z bazy danych.

public partial class User
{
    partial void OnUserNameChanging(string value)
    {
        if (value.Length > 5)
            Console.WriteLine("{0}'s UserName changing to {1}, OK!",
                this.FullName, value);
        else
            Console.WriteLine("{0}'s UserName changing to {1}, TooShort!",
                this.FullName, value);
    }

    partial void OnUserNameChanged()
    {
        this.IsActive = this.UserName.Length > 5;
    }
}

Logowanie połączeń do bazy danych

Aby logować połączenia możemy zasubskrybować się na zdarzenie StateChange z property Connection dostępnej w obiekcie kontekstu.

this.Connection.StateChange += (s, e) =>
    {
        var conn = ((EntityConnection) s).StoreConnection;
        Console.WriteLine("{0}: Database: {1}, State: {2} was {3}",
                            DateTime.Now.ToShortTimeString(), conn.Database,
                            e.CurrentState, e.OriginalState);
    };

Walidacja przy SaveChanges

Czasami do walidacji potrzebujemy dwie wartości encji: nową i poprzednią (np. procentowy przyrost płac). Odpowiednim miejscem, w którym mamy dostęp do obu wartości jest zdarzenie SavingChanges.

public partial class EntityFrameworkRecipesEntities3
{
    partial void OnContextCreated()
    {
     this.SavingChanges += new EventHandler(EntityFrameworkRecipesEntities3_SavingChanges);
    }

    void EntityFrameworkRecipesEntities3_SavingChanges(object sender, EventArgs e)
    {
     var entries = this.ObjectStateManager
         .GetObjectStateEntries(EntityState.Modified)
         .Where(entry => entry.Entity is Employee);
     foreach(var entry in entries)
     {
      var salaryProp = entry.GetModifiedProperties()
           .FirstOrDefault(p => p == "Salary");
      if(salaryProp != null)
      {
       var originalSalary = Convert.ToDecimal(
            entry.OriginalValues[salaryProp]);
       var currentSalary = Convert.ToDecimal(
            entry.CurrentValues[salaryProp]);
       if(originalSalary != currentSalary)
       {
        if(currentSalary > 1.1M*originalSalary)
        {
         throw new ApplicationException("Can't increase salary more than 10%");
        }
       }
      }

     }
    }
}

[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 : Plain Old CLR Objects

Klasy generowane przez EF mają jedną zasadniczą wadę: są mocno związane z konkretnym EDM. Fakt ten ogranicza możliwości testowania i praktycznie eliminuje reużywalność. Jeżeli  chcemy skorzystać z własnych klas jako encji, mamy do dyspozycji mechanizm POCO. Nazwa Plain Old CLR oznacza mniej więcej tyle, że obiekty nie mają referencji do zewnętrznych frameworków, nie muszą dziedziczyć po żadnych specyficznych klasach.

Przykładowo, mamy zestaw tabel, mapowany przez EF na poniższy zestaw encji:

W pierwszej kolejności należy wyłączyć generowanie klas ustawiając w Designerze opcję Code Generation Strategy na None. Następnie tworzymy klasy dla encji. Każda nazwa oraz typ property (również navigation property) musi zgadzać się z nazwą z designera. Kolekcje reprezentujemy przez ISet<T>.

 public class Customer
    {
        public int CustomerId { get; set; }
        public string CustomerName { get; set; }
        public ISet<Order> Orders { get; set; }
        public Customer()
        {
            Orders = new HashSet<Order>();
        }
    }

    public class Order
    {
        public int OrderId { get; set; }
        public int CustomerId { get; set; }
        public DateTime OrderDate { get; set; }
        public Customer Customer { get; set; }
        public ISet<OrderDetail> OrderDetails { get; set; }
        public Order()
        {
            OrderDetails = new HashSet<OrderDetail>();
        }
    }

    public class OrderDetail
    {
        public int OrderId { get; set; }
        public int ProductId { get; set; }
        public decimal UnitPrice { get; set; }
        public int Quantitiy { get; set; }
        public Order Order { get; set; }
        public Product Product { get; set; }
    }

    public class Product
    {
        public int ProductId { get; set; }
        public string ProductName { get; set; }
        public decimal UnitPrice { get; set; }
    }

Ostatni krok to konieczność stworzenia klasy kontekstu.

public class EntityFrameworkRecipes : ObjectContext
{
    private ObjectSet<Customer> _customers;
    private ObjectSet<Order> _orders;
    private ObjectSet<OrderDetail> _orderdetails;
    private ObjectSet<Product> _products;

    public EntityFrameworkRecipes()
        : base("name=EntityFrameworkRecipesEntities2", "EntityFrameworkRecipesEntities2")
    {
        _orders = CreateObjectSet<Order>();
        _orderdetails = CreateObjectSet<OrderDetail>();
        _products = CreateObjectSet<Product>();
    }

    public ObjectSet<Customer> Customers
    {
        get { return _customers ?? (_customers = CreateObjectSet<Customer>()); }
    }

    public ObjectSet<Order> Orders
    {
        get { return _orders; }
    }

    public ObjectSet<OrderDetail> OrderDetails
    {
        get { return _orderdetails; }
    }

    public ObjectSet<Product> Products
    {
        get { return _products; }
    }
}

Do konstruktora bazowego przekazujemy ścieżkę do connectionstring z pliku konfiguracyjnego, oraz nazwę kontenera.

Wczytywanie encji powiązanych

Klasa ObjectContext udostępnie metodę LoadProperty i to z niej należy skorzystać, aby wczytać powiązane encje

context.LoadProperty(venue, v => v.Events);

Lazy Loading

Aby zapewnić Lazy Loading należy odpowiednie propercje w obiektach POCO oznaczyć słowem kluczowym virtual.  Dzięki temu stworzone zostaną obiekty proxy umożliwiające Lazy Loading. Dodatkowo na obiekcie kontekstu, w property ContextOptions należy ustawić opcję LazyLoadingEnabled na true.

[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.

[SQL|ORM] Entity Framework : Loading Entities

Entity Framework udostępnia szereg mechanizmów umożliwiających sterowanie sposobem ładowania encji powiązanych w zapytaniach.Domyślnie ładowane są jedynie encje bezpośrednio wołane w zapytaniu. Czasami jednak potrzebujemy zredukować ilość zapytań do bazy danych i pobrać zarówno bezpośrednio interesujące nas encje, jak i encje powiązane za pomocą jednego zapytania. Wiąże się to jednak ze znacznym zwiększeniem grafu obiektów w pamięci. Nie ma jednoznacznej odpowiedzi na to, jak powinno się konstruować zapytania, wiele zależy od ilości danych i częstości korzystania z danych powiązanych.

Ładowanie encji powiązanych

W tym celu należy skorzystać z funkcji Include(), gdzie przez string podaje się nazwę navigation property..

var customers = context.Customers.Include("CustomerType").
                    Include("CustomerEmails");
foreach (var customer in customers)
{
    Console.WriteLine("Name : {0}, Email : {1}", customer.Name, 
        customer.CustomerType.Description);
    foreach (var customerEmail in customer.CustomerEmails)
    {
        Console.WriteLine(customerEmail.Email);
    }
}

Wykorzystując funkcję Include powyższy kod przetransformuje się do jednego dużego zapytania do bazy danych. Jeśli usuniemy Include, dostejemy po dwa dodatkowe zapytania do bazy danych dla każdej encji z Customers.

Pobieranie całego grafu encji

W bardziej skomplikowanym przypadku, przedstawionym na poniższym diagramie, poprzez odpowiedni dobór kolejności wywołań funkcji Include(), również możemy w jednym zapytaniu załadować do pamięci pełny graf.

var graph = context.Courses.Include("Sections.Students")
                    .Include("Sections.Instructor");

A zatem korzystając z funkcji Include możemy podawać nazwy encji z całego grafu poprzez ścieżkę do takiej nazwy budowaną przy użyciu navigation properties.

W przypadku dziedziczenia encji, gdy tworzymy zapytanie o encje dziedziczące, możemy bez problemu korzystać z navigation properties encji bazowych.

Korzystanie z Include w dowolnych zapytaniach LINQ

Jeżeli chcemy wykorzystać metodę Include() w przypadku dowolnych zapytań, np. joinów, group by czy where, warto pamiętać o kilku faktach:

• Niezmaterializowane wyrażenie LINQ musimy rzutować do typu ObjectQuery<T>, aby móc skorzystać z metody Include()
• Include() jest stosowane jedynie do końcowych rezultatów zapytania, w podzapytaniach będzie ignorowane
• Include() będzie ignorowane, gdy kolekcja zawiera cokolwiek innego niż encje

W poniższym przykładzie zmienna events przechowuje zapytanie typu IQueryable<T> , dopiero podczas materializacji możemy skorzystać z Include().

var events = from ev in context.Events
             where ev.Club.City == "New York"
             group ev by ev.Club
             into g
             select g.FirstOrDefault(e1 => e1.EventDate == g
                 .Min(s => s.EventDate));
var e = ((ObjectQuery<Event>) events).Include("Club").First();

Opóźnione ładowanie encji powiązanych

Sytuacja przedstawia się następująco: mamy jeden obiekt w pamięci i chcemy pobrać dla niego dane z wielu encji powiązanych. Nie chcemy drugi raz pobierać instancji obiektu zmaterializowanego. Możemy skorzystać z metody CreateSourceQuery(), a na zwróconym przez nią obiekcie wywołać dopiero Include(). Tak zbudowane zapytanie należy dołożyć do kontekstu za pomocą funkcji Attach(). Jest to rozwiązanie bardzo wydajne, ponieważ nie musimy po raz drugi pozyskiwać kolumn z encji, którą mamy już w pamięci.

var jill = context.Employees.Where(e => e.Name == "Jill Carpenter").First();
var moreResults = jill.DepartmentReference.CreateSourceQuery()
    .Include("Company").First();
context.Attach(moreResults);
Console.WriteLine("{0} works in {1}", jill.Name, jill.Department.Company.Name);

CreateSourceQuery() zwraca obiekt zapytania, który w momencie wykonania zwraca ten sam zestaw obiektów, który istnieje w obecnej kolekcji.
Attach() dołącza obiekt lub graf obiektów do kontekstu.

Filtrowanie i sortowanie encji powiązanych

Mamy encję w pamięci i chcemy przefiltrować oraz posortować encje z nią powiązane. Kolejny raz skorzystać można z metody CreateSourceQuery(), która pozwoli uzyskać dostęp do zapytania używanego przy pobieraniu kolekcji przez navigation property. Funkcję Include() musimy wywołać w momecie, gdy mamy do czynienia z odpowiednim typem (ObjectQuery<T>). Metoda Attach() łączy przefiltrowane encje z pierwszą encją w pamięci.

var hotel = context.Hotels.First();
var rooms = hotel.Rooms.CreateSourceQuery()
            .Include("Reservations")
            .Where(r => r is ExecutiveSuite && r.Reservations.Any())
            .OrderBy(r => r.Rate);

hotel.Rooms.Attach(rooms);

Sprawdzenie, czy referencja została już załadowana do pamięci 

Chcemy sprawdzić, czy dana referencja do encji powiązanej (lub powiązanej kolekcji) została już załadowana do kontekstu. EF udostępnie property IsLoaded. Różnica polega na tym, że dla kolekcji sprawdzamy IsLoaded bezpośrednio na navigation property, natomiast dla pojedynczych referencji przez nazwa_propertyReference.

var project = context.Projects.Include("Manager").First();
if(project.ManagerReference.IsLoaded)
    Console.WriteLine("Manager reference is loaded");
else
    Console.WriteLine("Manager reference is NOT loaded");
if(project.Contractors.IsLoaded)
    Console.WriteLine("Contractors are loaded");

Aby doczytać kolekcje w sposób jawny, można skorzystać z funkcji Load(), przed jej wywołaniem warto sprawdzić, czy referencja jest już w pamięci za pomocą IsLoaded. Metoda Load jest przeładowana tak, że jako parametr przyjmuje flagę MergeOption, dostępne opcje:

• AppendOnly dołącza instancje, których nie ma obecnie w kontekście
• OverwriteChanges przywraca do kontekstu stan z bazy danych
• NoTracking wyłącza śledzenie stanu
• PreserveChanges przeciwieństwo dla OverwriteChanges

[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.