Mssql instead of trigger – implementacja

Powrót

Praca wre, coś się kompiluje, coś się linkuje coś się uruchamia, a coś nie. Tym czymś na „nie” była (była bo już rozwiązane) baza mssql. Problem który napotkałem wyglądał tak:

Geneza:

Chce aby oprócz śledzenia kanałów z RSSami, użytkownik śledzić mógł też poczynania innych użytkowników. Aby tego dokonać potrzebuje gdzieś w bazie danych posiadać klasę/tabelę, która będzie trzymać informację o tym kto i kogo obserwuje. Continue reading

Entity framework wspólna obsługa interfejsów modeli danych

Model

Czasami tak projektujemy naszą aplikacje, że każdy model ma jedną lub kilka cech wspólnych. Od najbardziej oczywistych, jak na przykład ID, poprzez czas i datę utworzenia, modyfikacji, czy-usunięty, czy-opublikowany i inne czy-?

W zależności od poziomu lenistwa cechy te definiowane i utrzymywane są w każdej z klas z osobna lub w jednym lub-lub w kilku interfejsach który jest implementowany przez modele.

Do momentu pisania posta byłem gościem, który posiadał jeden wspólny interface, ale właśnie teraz do mnie dotarło, że może lepiej mieć kilka interfejsów dla modeli danych, I-ID-able, I-Create-able, I-Update-able, I-cośtam-able – blogowanie rozwija!

Część wspólna

Co więcej, obsługa części wspólnej może być zaimplementowana na różne sposoby, to również zależy od poziomu lenistwa. Można to robić ręcznie w każdej metodzie repozytorium, w klasach generycznych/bazowych dla repozytoriów, można AOP, można także w klasie kontekstu. Ja aktualnie rozwiązuje to właśnie tam w samej klasie bazy danych.

Poniżej kawałek kodu:

Continue reading

Entity framework – obowiązkowa minimalna konfiguracja #dajsiepoznac

Lazy loading

Kiedy korzystamy z EF należy pamiętać o tym, aby zawsze wyłączyć leniwe ładowanie (lazy loading) w przeciwnym wypadku za każdym razem gdy będziemy sięgać po dane które leżą w innej tabeli niż ta, która została początkowo zaciągnięta z bazy danych EF zrobi to za nas. Brzmi fajnie, ale gdy pomyślicie że taka operacja może wykonać się w pętli, pomysł szybko przestaje być tak miły. N wykonań pętli N pojedynczych zapytań do bazy danych. Minusem wyłączenia lenia w EF jest to, że jeśli nie powiemy wcześniej EF że chcemy wykonać operację JOIN nie będziemy mieli dostępu do danych z innej tabeli.

Kod wygląda tak, pierwsze zapytanie bez, drugie z instrukcją JOIN

Linia (@22) wyłącza lazy loading. Linia (@12) wyciąga z bazy tylko osoby, jesli pole Properties będzie nullem. Linia (@13) wyciąga z bazy osoby oraz wykonuje operację JOIN, lista cech w osobie zostanie wypełniona pasującymi wartościami. Ostrzeżenie: ponieważ w przykładzie wywołanie jest jedno pod drugim, gdy postawi się breakpoint po obu (@14), referencja osoby zostanie zaktualizowana. Może się więc okazać, że w przypadku p1 oraz p2 pola properties będą już ustawione. Zalecam w przypadku samodzielnego sprawdzania przykładu, postawić breakpoint odpowiednio w liniach @12 oraz @13 i podejrzenie wartości property.

Logowanie

Często pojawia sie problem logowania zapytań które generuje EF, zawsze szukałem, paczek, rozwiązań czy gotowych bibliotek. Aż pewnego pięknego razu na SO znalazłem odpowiedź, która wygląda tak jak w linii 21. Najprościej jak się da, dopinam się do wewnętrznego logera oraz wypisuje na Debug.Trace informacje o zapytaniach, wyglądają one tak:

Widać tam wywołania dwóch zapytań (@12i @13) prosty oraz ten z JOIN, widoczny jest czas potrzeby na wykonywanie operacji jak i informacje o rozpoczęciu i zakończeniu połączenia. Wszystko co potrzeba aby sprawdzić czy zapytanie zostało wygenerowane w akceptowalny przez nas sposób – me gusta!

Autofac update – dynamiczna zmiana implementacji

Wiadomo, że każdy projekcie są testy. W części z nich są testy jednostkowe, w innych są testy integracyjne, w innych testy programistyczne – programista klika i jak działa to działa, a w jeszcze innych test na produkcji u klienta razem z milionami użytkowników. Generalnie każdy jakieś test ma. Wiadomo jak jest w projektach komercyjnych, klient chce aplikację działająca, wykonaną z najnowszymi i najlepszymi technikami, najlepiej napisaną przez juniorów, bez testerów i PM – tak najczęściej planowany jest budżet na aplikacje. Programiści czasem potrafią spełnić część tych wymagań i udaje się nam stworzyć aplikację, która jest stabilna (najczęściej), wygląda przyzwoicie, została napisana tak, że można ją rozwijać – generalnie klient jest zadowolony, a przynajmniej nie pieni się i jest w stanie zaakceptować produkt.
Dzieje się tak, ponieważ Ci programiści dzielnie przeklikują swoje wytwory, piszą możliwie dobry kod, przy kiepskich wymaganiach czy ich braku. Czasem gdy trzeba wyklikać to samo tysiąc razy, człowiek robi się leniwy, albo nie uważny i czy tego chcemy czy nie nie jesteśmy w stanie wyklikać wszystkich przypadków. Najczęściej gdy projekt jest już sporych rozmiarów, posiada skomplikowaną logikę lub wiele alternatywnych do siebie ścieżek. 
W moim przypadku naszą słabą stroną były problemy w wydajnością. Na developerskich maszynach, gdzie testowaliśmy, mieliśmy po kilka kont użytkownika, kilka, kilkadziesiąt, lub trochę ponad setkę wpisów. Generowanie danych za każdym razem gdy baza się zmieni i trzeba było ją odtwarzać od zera nie należało do przyjemnych rzeczy, nie mieliśmy migracji, więc drop a potem create. Testy selenium pojawiły się później, ale też zajmowały wieczność.
Zmierzam do tego, że łatwo byłe tego uniknąć, generując sobie masę testowych danych. Jak tego dokonać? O tym właśnie dzisiaj, odrobina kodu się także pojawi a wszystko dostępne będzie na githubie. Jak zawsze minister zdrowia ostrzega, przykłady mogą być przejaskrawione, a rozwiązanie może się okazać przydatne tylko dla mnie.
Przykładowym problememe do rozwiązania jest prosta aplikacja, która umożliwia założenie konta, zalogowanie się, oraz dodanie jakiegoś prostego wpisu tekstowego, który jest widoczny dla wszystkich. Coś jak blog, czy czat czy lista ogłoszeń, to nie jest do końca ważne.
Zacznijmy od struktury aplikacji:
Dla prostoty wszystko co potrzebne umieściłem w jednym kontrolerze. A zwinięte foldery nie mają żadnego wpływu na treść edukacyjną tego posta.
Ok, to zaczynamy. Rejestracja nowego użytkownika wygląda tak:

   1:  [HttpGet]
   2:  public ActionResult Register()
   3:  {
   4:      var viewmodel = new RegisterViewModel();
   5:      return this.View("Register", viewmodel);
   6:  }
   7:   
   8:  [HttpPost]
   9:  public ActionResult Register(RegisterDto dto)
  10:  {
  11:      this.accountService.Register(dto);
  12:      return this.RedirectToAction("Index");
  13:  }
I ostatecznie prosty serwis:

   1:  public void Register(RegisterDto dto)
   2:  {
   3:      WebSecurity.CreateUserAndAccount(
   4:          dto.Email,
   5:          dto.Password);
   6:  }
Jak widać korzystam z SimpleMembershipProvider. Klasa dto do rejestracji też jest książkowo prosta:

   1:  public class RegisterDto
   2:  {
   3:      public string Email { get; set; }
   4:      public string Password { get; set; }
   5:  }
Tak więc, aby założyć konto wystarczy wpisać dwa stringi i ciach, konto dostępne. Oczywiście gdy będzie trzeba to powtórzyć wiele razy, zacznie się to robić męczące i nudne. Dlatego leniwi programiści mogą wymyślić sobie coś takiego:

.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: Consolas, „Courier New”, Courier, Monospace;
background-color: #ffffff;
/*white-space: pre;*/
}

.csharpcode pre { margin: 0em; }

.csharpcode .rem { color: #008000; }

.csharpcode .kwrd { color: #0000ff; }

.csharpcode .str { color: #a31515; }

.csharpcode .op { color: #0000c0; }

.csharpcode .preproc { color: #cc6633; }

.csharpcode .asp { background-color: #ffff00; }

.csharpcode .html { color: #800000; }

.csharpcode .attr { color: #ff0000; }

.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}

.csharpcode .lnum { color: #606060; }

   1:  [HttpGet]
   2:  public ActionResult NewUsers()
   3:  {
   4:      this.testService.CreateNewUsers();
   5:      return this.RedirectToAction("Index");
   6:  }
Gdzie serwis został zaimplementowany w taki sposób:

   1:  public void CreateNewUsers()
   2:  {
   3:      var dto = new RegisterDto
   4:      {
   5:          Email = Faker.InternetFaker.Email() + Faker.NumberFaker.Number(),
   6:          Password = Faker.NumberFaker.Number(1000000000, 2000000000).ToString()
   7:      };
   8:      this.accountService.Register(dto);
   9:  }
Fakera opisałem w poprzednim poście. Warto zwrócić uwagę, że test serwis korzysta z account service. Dwie ważne rzeczy: każdy wie, że zależności powinny iść tylko w pionie, nigdy w poziomie – istnieje ryzyko zależności cyklicznych; jest to serwis testowy, wiec korzysta z normalnego przepływu danych, nie robi nic na skróty, nie oszukuje, także ułatwia nam tworzenie użytkowników, a jednocześnie testuje działające już serwisy.
Jak do tej pory jeszcze nie ma wielkiego szału, ale się powoli rozkręcam. Kolejny etap to wpis w aplikacji, kontroler wygląda tak:

   1:  [HttpGet]
   2:  [Authorize]
   3:  public ActionResult NewBlogPost()
   4:  {
   5:      var viewmodel = new BlogPostViewModel();
   6:      return this.View("NewBlogPost", viewmodel);
   7:  }
   8:   
   9:  [Authorize]
  10:  [HttpPost]
  11:  public ActionResult NewBlogPost(NewBlogPostDto dto)
  12:  {
  13:      this.postingService.NewBlogPost(dto);
  14:      return this.RedirectToAction("Index");
  15:  }
Kod nie powinien niczym zaskakiwać, GET i POST, oraz wymagane wcześniejsze zalogowanie się użytkownika. Warstwa implementująca:

   1:  public void NewBlogPost(NewBlogPostDto dto)
   2:  {
   3:      var userId = this.accountService.GetCurrentLoggedUserId();
   4:      var model = new BlogPost(userId, dto.Text, DateTime.Now);
   5:      this.database.BlogPosts.Add(model);
   6:      this.database.SaveChanges();
   7:  }
Dto dla nowego wpisu jest niebiańsko prosta:

   1:  public class NewBlogPostDto
   2:  {
   3:      public string Text { get; set; }
   4:  }
Ale wróćmy do serwisu, w serwisie korzystając z SMP (single membership provider) pobieramy aktualne ID użytkownika. Może nie bezpośrednio z SMP, ale prostego wrapperka nad SMP. W momencie implementacji postawiłem na opcje zaciągania danych o użytkowniku z SMP.
Sam wrapper wcześniej pokazany częściowo, teraz w calej okazałości:

   1:  class WebSecurityAccountService : IAccountService
   2:  {
   3:      public void Logout()
   4:      {
   5:          WebSecurity.Logout();
   6:      }
   7:   
   8:      public bool Login(LoginDto dto)
   9:      {
  10:          var result = WebSecurity.Login(dto.Email, dto.Password);
  11:          return result;
  12:      }
  13:   
  14:      public void Register(RegisterDto dto)
  15:      {
  16:          WebSecurity.CreateUserAndAccount(
  17:              dto.Email,
  18:              dto.Password);
  19:      }
  20:   
  21:      public int GetCurrentLoggedUserId()
  22:      {
  23:          return WebSecurity.CurrentUserId;
  24:      }   
  25:  }
No dobra, fajny pomysł, informacje o użytkowniku zawsze pobierane z serwera, wydaje się bezpieczne i w ogóle. Ale teraz dochodzimy znowu do momentu, gdy chciałbym napisać kawałek kodu który pozwoli mi jednym kliknięciem utworzyć nowy wpis. Najlepiej za każdym razem z losowym tekstem (tutaj Faker daje radę) ale jeszcze z losowego istniejącego konta. W tym momencie na chwilę zrobiło się trudno, ale skoro mamy IAccountService i z niego pobieramy informacje o użytkowniku, czemu tego nie wykorzystać, wystarczy go tylko podmienić na inny, mój, i losować z niego użytkowników:

   1:  [HttpGet]
   2:  public ActionResult NewPosts()
   3:  {
   4:      this.testService.CreateNewPosts();
   5:      return this.RedirectToAction("Index");
   6:  }
Potem:

   1:  public void CreateNewPosts()
   2:  {
   3:      var users = this.database.Users.ToList();
   4:      var random = users.ElementAt(Faker.NumberFaker.Number(users.Count - 1));
   5:      var dto = new NewBlogPostDto
   6:      {
   7:          Text = Faker.TextFaker.Sentences(3)
   8:      };
   9:   
  10:      SpecialAccountService.CurrentLoggedUserId = random.Id;
  11:   
  12:      this.postingService.NewBlogPost(dto);
  13:  }
Docieramy powoli do mięska. Z bazy danych wyciągamy wszystko użytkowników, losujemy jednego i zapamiętujemy. Następnie linijka 10 dzięki SpecialAccountService ustawia Id „aktualnie” zalogowanego użytkownika, a następnie zwróci to Id, gdy ktoś będzie pytać o aktualnie zalogowanego użytkownika. Na końcu znowu, wykorzystujemy wcześniej napisany serwis, który zachowuje się normalnie i jest dodatkowo testowany przez nasze generowanie testowych danych. Dlaczego SpecialAccountService działa tak soczyście?

   1:  public class SpecialAccountService : IAccountService
   2:  {
   3:      private readonly WebSecurityAccountService realAccountService;
   4:   
   5:      public SpecialAccountService()
   6:      {
   7:          this.realAccountService = new WebSecurityAccountService();
   8:      }
   9:   
  10:      public void Logout()
  11:      {
  12:          this.realAccountService.Logout();
  13:      }
  14:   
  15:      public bool Login(LoginDto dto)
  16:      {
  17:          return this.realAccountService.Login(dto);
  18:      }
  19:   
  20:      public void Register(RegisterDto dto)
  21:      {
  22:          this.realAccountService.Register(dto);
  23:      }
  24:   
  25:      static public int CurrentLoggedUserId { get; set; }
  26:   
  27:      public int GetCurrentLoggedUserId()
  28:      {
  29:          return CurrentLoggedUserId;
  30:      }
  31:  }
Klasa ta implementuje IAccountService i jest ona używana w zarejestrowana w autofacu. Natomiast gdy jest potrzeba prawdziwej rejestracji użytkownika, całą robotę deleguje do prawdziwego serwisu. Dzięki temu można rejestrować,logować czy wylogować użytkownika. A bajer z aktualnym użytkownikiem ułatwia nam tworzenie losowych wpisów dla losowych użytkowników.
Wiadomo, że na produkcje nie powinno się wypuszczać takiego kodu, dlatego normalnie trzeba by przygotować odpowiednią konfigurację IOC gdzie IAccountService będzie implementowany przez WebSecurityAccountService, a na potrzeby developerki wystarczy SpecialAccountService – można tak zrobić. Można też poprosić autofaca, aby zmienił swoje dotychczasowe zachowanie i nadpisać wcześniejsze reguły:

   1:  public class HijackService : IHijackService
   2:  {
   3:      public void RestoreIOC()
   4:      {
   5:          var builder = new ContainerBuilder();
   6:          builder.RegisterType<WebSecurityAccountService>().As<IAccountService>();
   7:          builder.Update(ApplicationIocContainer.Container);
   8:      }
   9:   
  10:      public void HijackIOC()
  11:      {
  12:          var builder = new ContainerBuilder();
  13:          builder.RegisterType<SpecialAccountService>().As<IAccountService>().SingleInstance();
  14:          builder.Update(ApplicationIocContainer.Container);
  15:      }
  16:   
  17:      public IOCStatus GetStatus()
  18:      {
  19:          var isHijacked = ApplicationIocContainer.Container.Resolve<IAccountService>() is SpecialAccountService;
  20:          return isHijacked ? IOCStatus.Hijacked : IOCStatus.Normal;
  21:      }
  22:  }
Od tej pory możemy dynamicznie rejestrować swoje klasy, które ułatwią nam udawanie kogoś kim nie jesteśmy, a po skończonej generacji danych przywrócić aplikację do oryginalnego stanu i zacząć właściwe testowanie. Największą gwiazdą tego postu jest metoda Update z IOC, która umożliwia aktualizację powiązać interfejsu z klasą implementującą bez potrzeby zmiany konfiguracji, bez potrzeby restartu aplikacji, żeby autofac skonfigurował się od nowa, etc.
A dzięki temu wszystkie możemy stworzyć sobie taki prosty interfejs aplikacji:
Gdzie istnieje możliwość normalnego działa z aplikacją, ale gdy zajdzie potrzeba, można na chwilę pożyczyć kontener, wygenerować sobie niezwykle szybko sporo danych, i ponownie jednym kliknięciem przywrócić normalny stan aplikacji. Tutaj tworzę tylko jednego użytkownika i jeden wpis, ale kto bogatemu zabronić tworzyć ich tysiące?
Uwagi, pomysły chętnie przyjmę.
Kod dostępny jest na bitbucket: https://bitbucket.org/jstadnicki/jstadnicki-blog
Zaczynam od jednego, z czasem gdy będzie więcej dużych wpisów, kolejne rzeczy związane z blogiem będę wrzucane tam.
Milej niedzieli!

Linq oraz Entity Framework – podstawowe polecenia i przykłady

Muszę się przyznać, że nigdy nie przepadałem za bazami danych. Zawsze uważałem je za nudny temat i nie odczuwałem większej potrzeby zajmowania się nimi. Prawdę mówiąc podobne zdanie miałem o C#, a teraz piszę w C# i uczę się Entity Framework, a w dodatku swoje doświadczenia spisuje i czasem się nimi dzielę.
Dzisiaj trochę o LINQ i Entity Frameworki, będą to raczej suche przykłady jak korzystać z linq, plus moje bardzo osobiste komentarze do nich. Przykłady pochodzą z książki, którą czytam i jeszcze nie wiem czy mogę ją polecić. Zła nie jest, ale jest w niej spora ilość błędów, które potrafią zdenerwować. Nadal jednak ją czytam, więc nie jest aż tak źle.
Entity Framework 4 in action” – bo o niej mowa, jest tworem trzech panów:
– Stefano Mostarda, – Marco De Sanctis
– Daniele Bochicchio

Książka ukazała się nakładem wydawnictwa Manning. Można ją zamówić w polskich księgarniach w cenie od 150 do 200 polskich złotych.Nie będę podawać przykładów na najprostsze zapytanie linq, zacznę od tych ciut trudniejszych, przykłady będą bazować na tych z książki
Przykłady będą się opierać na następującej bazie danych:

Oraz na takim mapowaniu jej do EDM:

Do rzeczy, czyli to co developerzy lubią najbardziej kod:

  • Grupowanie wyników na podstawie więcej niż jednego klucza jest możliwe przy pomocy nowego typu anonimowego, który posłuży za klucz do grupowania:
    1. using (var context = new OrderITEntities())
    2. {
    3.     var result = from o in context.Orders
    4.                  group o by new { o.ShippingAddress.City, o.ShippingAddress.ZipCode };
    5.  
    6.     foreach (var key in result)
    7.     {
    8.         Trace.WriteLine(string.Format(„{0}-{1}”, key.Key.City, key.Key.ZipCode));
    9.         foreach (var item in key)
    10.         {
    11.             Trace.WriteLine(item.OrderId);
    12.         }
    13.     }
    14. }

    Komentarz:
    @04 – Wyniki są grupowane na podstawie klucza {City, ZipCode}
    @06 – Najpierw trzeba przejść się po wszystkich kluczach
    @10 – Teraz można odczytać wyniki zgrupowane dla danego klucza.
    Wynikiem takiej operacji jest:

    Boston-87329
    17
    Chicago-ZipCode
    18
    city-zipc
    21
    City-ZipCode
    24
    fzxcb-xcbc
    46
    Los Angeles-34243
    31
    Los Angeles-54564
    14
    Miami-24323
    15
    Miami-34631
    16
    New York-0000001
    34
    35
    36
    New York-7777
    49
    New York-98765
    29
    30
    Rome-00100
    22
    23
    sc-sz
    32
    sdf-sdf
    47
    Seattle-24332
    13
    Seattle-98765
    12
    v<-cxv 44 Washington-98765 11 xc-dzvcx 45
  • Gdy nie potrzebna jest cała tabela, można skorzystać z projekcji wyników na nowy anonimowy typ, wybierając tylko to czego aktualnie potrzebujemy. Projekcja na przykładzie grupowania
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = from o in context.Orders
    4.             group o by o.ShippingAddress.City
    5.             into oGroup
    6.             select new { CityName = oGroup.Key, Items = oGroup };
    7.     foreach (var key in r)
    8.     {
    9.         Trace.WriteLine(string.Format(„{0}: {1}”, key.CityName, key.Items.Count()));
    10.         foreach (var item in key.Items)
    11.         {
    12.             Trace.WriteLine(item.OrderId);
    13.         }
    14.     }
    15. }

    Komentarz:
    @05 – wynik grupowania będzie w oGroup
    @06 – projekcja na nowy anonimowy typ
    @08 – pierwsza iteracja po kluczach
    @11 – kolejna iteracja po zgrupowanych obiektach
    Wyniki operacji:

    Boston: 1
    17
    Chicago: 1
    18
    city: 2
    21
    24
    fzxcb: 1
    46
    Los Angeles: 2
    14
    31
    Miami: 2
    15
    16
    New York: 6
    29
    30
    34
    35
    36
    49
    Rome: 2
    22
    23
    sc: 1
    32
    sdf: 1
    47
    Seattle: 2
    12
    13
    v<: 1 44 Washington: 1 11 xc: 1 45
  • Wybranie krotek spełniających nasze wymagania, a następnie zgrupowanie według adresu wysyłki:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from o in context.Orders
    4.                  group o by o.ShippingAddress.City
    5.                      into g
    6.                      where g.Count() > 1
    7.                  select g;
    8.  
    9.     foreach (var key in result)
    10.     {
    11.         Trace.WriteLine(string.Format(„{0}: {1}”, key.Key, key.Count()));
    12.         foreach (var order in key)
    13.         {
    14.             Trace.WriteLine(order.OrderId);
    15.         }
    16.     }
    17. }

    Komentarz
    @04 – Wyniki zostaną zgrupowane na podstawia miasta
    @06 – Interesują nas tylko takie krotki, które posiadają więcej niż jedno zamówienie
    Wynik operacji

    city: 2
    21
    24
    Los Angeles: 2
    14
    31
    Miami: 2
    15
    16
    New York: 6
    29
    30
    34
    35
    36
    49
    Rome: 2
    22
    23
    Seattle: 2
    12
    13
  • Sortowanie według zadanego klucza, wykonane przez silnik DB:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from o in context.Orders
    4.                  orderby o.ShippingAddress.City
    5.                  select o;
    6.  
    7.     foreach (var order in result)
    8.     {
    9.         Trace.WriteLine(string.Format(„{0}-{1}”, order.ShippingAddress.City, order.OrderId));
    10.     }
    11. }

    Komentarze
    @04 – Kluczem sortujący jest miasto
    Wynik operacji:

    Boston-17
    Chicago-18
    city-21
    City-24
    fzxcb-46
    Los Angeles-31
    Los Angeles-14
    Miami-15
    Miami-16
    New York-29
    New York-30
    New York-34
    New York-35
    New York-36
    New York-49
    Rome-22
    Rome-23
    sc-32
    sdf-47
    Seattle-12
    Seattle-13
    v<-44 Washington-11 xc-45
  • Posortowanie wyników według dynamicznie obliczanej wartości, wybranie tylko części do wyniku przy pomocy projekcji:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from o in context.Orders
    4.                  where o.OrderDetails.Count > 0
    5.                  orderby o.OrderDetails.Sum(d => d.Quantity * (d.UnitPrice – d.Discount))
    6.                  select new
    7.                      {
    8.                          o.OrderId,
    9.                          o.OrderDate,
    10.                          o.ShippingAddress,
    11.                          Total = o.OrderDetails.Sum(d => d.Quantity * (d.UnitPrice – d.Discount))
    12.                      };
    13.     foreach (var item in result)
    14.     {
    15.         Trace.WriteLine(item);
    16.     }
    17. }

    Komentarze:
    @04 – Ta linia odrzuca zamówienia, które nie posiadają żadnych dodatkowych informacji, DB z którą pracuje najwyraźnie pozwala na złożenie zamówienia i nie przypisanie do niego szczegółów
    @05 – Wynik ma być posortowany, wg. nowej cechy zamówienia. Jest ona obliczania dynamicznie
    @06 – Projekcja – do wyniku zostaje dołączona informacja obliczana dynamicznie
    Wynik operacji:

    { OrderId = 16, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 1,0000 }
    { OrderId = 15, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 2,0000 }
    { OrderId = 46, OrderDate = 2010-04-20 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 4,0000 }
    { OrderId = 47, OrderDate = 2010-04-20 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 4,0000 }
    { OrderId = 21, OrderDate = 2009-02-03 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 18,0000 }
    { OrderId = 18, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 48,0000 }
    { OrderId = 34, OrderDate = 2009-10-28 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 50,0000 }
    { OrderId = 35, OrderDate = 2009-10-28 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 50,0000 }
    { OrderId = 36, OrderDate = 2009-10-28 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 50,0000 }
    { OrderId = 22, OrderDate = 2009-02-03 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 50,0000 }
    { OrderId = 23, OrderDate = 2009-02-04 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 50,0000 }
    { OrderId = 49, OrderDate = 2010-04-20 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 56,0000 }
    { OrderId = 30, OrderDate = 2009-08-09 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 90,0000 }
    { OrderId = 31, OrderDate = 2009-08-09 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 100,0000 }
    { OrderId = 12, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 100,0000 }
    { OrderId = 29, OrderDate = 2009-08-09 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 135,0000 }
    { OrderId = 13, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 152,0000 }
    { OrderId = 32, OrderDate = 2009-08-10 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 201,0000 }
    { OrderId = 17, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 254,0000 }
    { OrderId = 11, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 255,0000 }
    { OrderId = 24, OrderDate = 2010-05-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 270,2000 }
    { OrderId = 14, OrderDate = 2008-11-27 00:00:00, ShippingAddress = OrderIT.Model.AddressInfo, Total = 556,0000 }
  • Sortować można nie tylko główną tabele wyników, ale także wszystkie właściwości do których posiada ona referencje. Jest to możliwe tylko dzięki projekcji wyniku:
    1. using (var context = new Model.OrderITEntities())
    2.             {
    3.                 var result = from o in context.Orders
    4.                              select new
    5.                                         {
    6.                                             o.OrderId,
    7.                                             o.OrderDate,
    8.                                             Details = o.OrderDetails.OrderBy(d => d.Quantity)
    9.                                         };
    10.                 foreach (var item in result)
    11.                 {
    12.                     Trace.WriteLine(„oid: ” + item.OrderId + „, date: ” + item.OrderDate);
    13.                     foreach (var d in item.Details)
    14.                     {
    15.                         Trace.WriteLine(string.Format(„q:{0}, id:{1}”, d.Quantity, d.OrderDetailId));
    16.                     }
    17.                 }
    18.             }

    Komentarz: @06,07 – Wynik ma zawierać wybrane przez nas pola @08 – Details ma zawierać szczegóły dotyczące zamówienia, dodatkowo mają one zostać posortowane według ilości.
    Wynik operacji:

    oid: 11, date: 2008-11-27 00:00:00
    q:5, id:22
    q:5, id:23
    oid: 12, date: 2008-11-27 00:00:00
    q:2, id:24
    oid: 13, date: 2008-11-27 00:00:00
    q:2, id:25
    q:3, id:26
    oid: 14, date: 2008-11-27 00:00:00
    q:2, id:27
    q:5, id:28
    q:8, id:29
    oid: 15, date: 2008-11-27 00:00:00
    q:2, id:30
    oid: 16, date: 2008-11-27 00:00:00
    q:1, id:31
    oid: 17, date: 2008-11-27 00:00:00
    q:2, id:32
    q:2, id:34
    q:5, id:33
    oid: 18, date: 2008-11-27 00:00:00
    q:4, id:35
    oid: 21, date: 2009-02-03 00:00:00
    q:1, id:36
    q:2, id:37
    oid: 22, date: 2009-02-03 00:00:00
    q:5, id:38
    oid: 23, date: 2009-02-04 00:00:00
    q:5, id:39
    oid: 24, date: 2010-05-27 00:00:00
    q:1, id:69
    q:3, id:40
    q:5, id:41
    oid: 29, date: 2009-08-09 00:00:00
    q:45, id:44
    oid: 30, date: 2009-08-09 00:00:00
    q:2, id:45
    oid: 31, date: 2009-08-09 00:00:00
    q:2, id:46
    oid: 32, date: 2009-08-10 00:00:00
    q:13, id:47
    q:45, id:48
    oid: 34, date: 2009-10-28 00:00:00
    q:5, id:50
    oid: 35, date: 2009-10-28 00:00:00
    q:5, id:52
    oid: 36, date: 2009-10-28 00:00:00
    q:5, id:54
    oid: 44, date: 2010-04-20 00:00:00
    oid: 45, date: 2010-04-20 00:00:00
    oid: 46, date: 2010-04-20 00:00:00
    q:2, id:63
    oid: 47, date: 2010-04-20 00:00:00
    q:2, id:64
    oid: 49, date: 2010-04-20 00:00:00
    q:3, id:66
    q:5, id:68
  • Zwykły JOIN jest nudny 🙂 od razu przykład dla tej operacji, na 2 polach, które posłużą za podstawe łączenia tabel:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from o in context.Orders
    4.                  join c in context.Companies.OfType<Customer>()
    5.                      on new { o.ShippingAddress.City, o.Customer.CompanyId }
    6.                      equals new { c.ShippingAddress.City, c.CompanyId }
    7.                  select o;
    8.  
    9.     foreach (var order in result)
    10.     {
    11.         Trace.WriteLine(string.Format(„oid:{0} date:{1} customer:{2} address:{3}”, order.OrderId,
    12.                         order.OrderDate, order.CustomerId,
    13.                         order.ShippingAddress.Address));
    14.     }
    15. }

    Komentarze
    @04 – Połączenie korzystać z klasy Order oraz Customer. Customer dziedziczy po Companies, trzeba jasno zaznaczyć, że z Companies interesują nas tylko Customer.
    @05 – Połączenia bazować będzie na nowym typie anonimowym, składającym się z City oraz ProductId
    Wynik operacji:

    oid:29 date:2009-08-09 00:00:00 customer:1 address:7th Avenue
    oid:30 date:2009-08-09 00:00:00 customer:1 address:7th Avenue
    oid:32 date:2009-08-10 00:00:00 customer:2 address:sa
    oid:34 date:2009-10-28 00:00:00 customer:1 address:2th street
    oid:35 date:2009-10-28 00:00:00 customer:1 address:2th street
    oid:36 date:2009-10-28 00:00:00 customer:1 address:2th street
  • Chciałem napisać też przykład dla OUTER JOIN, ale coś nie do końca go rozumiem. Nie chce pisać strasznych głupot. Wszystko rozbija się o użycie metody DefaultIfEmpty, nie będę niepotrzebnie wprowadzał fermentu i chaosu. Doczytam i zaktualizuje temat – obiecuje 🙂
  • W przykładach przyjęto metody TPC (Table Per Class) dla obiektów product, show oraz shirt. Przekłada się to na odpowiednie mapowanie w EF, klasy shoe oraz shirt dziedziczą po klasie product. Oczywiście tylko w C#, w sql jest to rozwiązane przy pomocy kluczy obcych. Pisząc zapytanie, które ma pobrać informację o jednym z wybranych produktów, korzystamy z dwóch tabel w sql; product oraz shoe/shirt. Linq nie jest w stanie tego samodzielnie wywnioskować, w związku z czymy należy mu w tym lekko pomóc:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from p in context.Products.OfType<Shoe>()
    4.                  select p;
    5.     foreach (var product in result)
    6.     {
    7.         Trace.WriteLine(product.GetType().ToString());
    8.     }
    9. }

    Komentarz
    @03 – wybierz tylko obiekty, który są typu Shoe (będą zwracane poprzez proxy, ale dla użytkownika są one przezroczyste i korzysta się jak z normalnej klasy)
    Wynik operacji:

    System.Data.Entity.DynamicProxies.Shoe_7AA4169E6949EE79A10F953F29A44C8C07E207BD569BE65D49A2BA0AAA0A98EB
    System.Data.Entity.DynamicProxies.Shoe_7AA4169E6949EE79A10F953F29A44C8C07E207BD569BE65D49A2BA0AAA0A98EB

    Istnieje także alternatywna metoda, jest ona mniej wydajna, spełnia jednak swoje zadanie:

    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var result = from p in context.Products
    4.                  where p is Shirt
    5.                  select p;
    6.     foreach (var product in result)
    7.     {
    8.         Trace.WriteLine(product.GetType().ToString());
    9.     }
    10. }

    Komentarz
    @03 – Wybierz wszystkie obiekty Product
    @04 – Odfiltruj ze wszystkich, tylko te spełniające założenie.
    Wynik (jest taki sam jak poprzednio, znowu widać klasę proxy, jednak tym razem są to Shirt):

    System.Data.Entity.DynamicProxies.Shirt_9781A3B17068763ADD1262E9DF89ABE0B9179FD9113AA7C50B50B8F1AEE66B55
    System.Data.Entity.DynamicProxies.Shirt_9781A3B17068763ADD1262E9DF89ABE0B9179FD9113AA7C50B50B8F1AEE66B55
    System.Data.Entity.DynamicProxies.Shirt_9781A3B17068763ADD1262E9DF89ABE0B9179FD9113AA7C50B50B8F1AEE66B55
    System.Data.Entity.DynamicProxies.Shirt_9781A3B17068763ADD1262E9DF89ABE0B9179FD9113AA7C50B50B8F1AEE66B55
    System.Data.Entity.DynamicProxies.Shirt_9781A3B17068763ADD1262E9DF89ABE0B9179FD9113AA7C50B50B8F1AEE66B55
  • Może zaistnieć także sytuacja gdy potrzeba będzie znaleźć produkty danego typu, ale zwrócić jest w postaci kolekcji obiektów klasy podstawowej, na to także znajdą się dwa sposoby:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = from p in context.Products
    4.             where (p as Shoe).Sport == „Basket”
    5.             select p;
    6.  
    7.     foreach (var product in r)
    8.     {
    9.         Trace.WriteLine(string.Format(„{0}: {1}”, product.Name, product.Description));
    10.     }
    11. }

    Komentarz
    @03 – Wybierz wszystko
    @04 – Rzutuj na Shoe i spradź czy pole Sport jest równe Basket
    Wynik:

    Basket shoes: Basket shoes

    Wersja alternatywna:

    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = (from p in context.Products.OfType<Shoe>()
    4.              where p.Sport == „Basket”
    5.              select p).Cast<Product>();
    6.  
    7.     foreach (var product in r)
    8.     {
    9.         Trace.WriteLine(string.Format(„{0}: {1}”, product.Name, product.Description));
    10.     }
    11. }

    Komentarz
    @03 – Wybierz tylko Shoe
    @04 – Sprawdź warunek
    @05 – Wynik rzutuj na klasą bazową Product
    Wynik:

    Basket shoes: Basket shoes

Doszliśmy do końca zapytań i selekcji. W moich notatkach mam jeszcze małe co nie co na temat funkcji i ich wykorzystania. Dzielą się one na dwa rodzaje, te dostarczone przez EF (funkcje kanoniczne) oraz te dostarczone przez silnik bazodanowy.
Funkcje z EF:
+ działać będą z każdą bazą, która jest wspierana przez EF
– działać będą po stronie klienta
Funkcje z silnika
+ działają po stronie serwera
– silnie uzależniają od implementacji na którą się zdecydowaliśmy
Dwa przykłady:

  • Funkcja działająca w oparciu o EF
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = from o in context.Orders
    4.             where EntityFunctions.DiffDays(o.OrderDate, o.ActualShippingDate) > 5
    5.             select o;
    6.  
    7.     foreach (var order in r)
    8.     {
    9.         Trace.WriteLine(string.Format(„OrderId: {0} missing the date”, order.OrderId));
    10.     }
    11. }

    Skromy komentarz
    @04 – Jasno sprecyzowane jest, że sprawdzamy różnicę w dniach.
    Wynik operacji:

    OrderId: 11 missing the date
    OrderId: 49 missing the date
  • Identyczna funkcja, ale działająca w oparciu o DB
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = from o in context.Orders
    4.             where SqlFunctions.DateDiff(„d”, o.OrderDate, o.ActualShippingDate) > 5
    5.             select o;
    6.     foreach (var order in r)
    7.     {
    8.         Trace.WriteLine(string.Format(„OrderId: {0} missing the date”, order.OrderId));
    9.     }
    10. }

    Skromy komentarz
    @04 – Należy dodać informację o którą część daty
    Wynik:

    OrderId: 11 missing the date
    OrderId: 49 missing the date

I ostatnie czym chciałem się podzielić, to możliwość wykonania własnej kwerendy. Bez pomocy ze strony LINQ. Może się zdarzyć, że zapytanie stworzone przez LINQ będzie zbyt wolne, a samodzielnie będziemy w stanie napisać je w prostszy i bardziej wydajny sposób. Dodatkowo możliwe jest także dynamicznie zdefiniowanie parametów zapytania. Są dwa podejścia, pierwsze prostsze drugie odrobinę bardziej skomplikowane. Należy pamiętać, że nie wolno ich łączyć. Tak samo jak nie wolno kąpać gremlinów i karmić ich po północy .

  • Pierwszy przykład, to prosta kwerenda, bez szału:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = context.ExecuteStoreQuery<OrderDetailProjection>;
    4.             („SELECT quantity, unitprice, discount FROM OrderDetail”);
    5.     foreach (var odp in r)
    6.     {
    7.         Trace.WriteLine(string.Format(„quantity:{0},discount:{1},unitprice:{2}”, odp.Quantity, odp.Discount,
    8.                         odp.UnitPrice));
    9.     }
    10. }

    Wynik:

    quantity:5,discount:0,0000,unitprice:1,0000
    quantity:5,discount:0,0000,unitprice:50,0000
    quantity:2,discount:0,0000,unitprice:50,0000
    quantity:2,discount:0,0000,unitprice:1,0000
    quantity:3,discount:0,0000,unitprice:50,0000
    quantity:2,discount:0,0000,unitprice:1,0000
    quantity:5,discount:0,0000,unitprice:50,0000
    quantity:8,discount:12,0000,unitprice:50,0000
    quantity:2,discount:0,0000,unitprice:1,0000
    quantity:1,discount:0,0000,unitprice:1,0000
    quantity:2,discount:0,0000,unitprice:1,0000
    quantity:5,discount:0,0000,unitprice:50,0000
    quantity:2,discount:2,0000,unitprice:3,0000
    quantity:4,discount:0,0000,unitprice:12,0000
    quantity:1,discount:0,0000,unitprice:10,0000
    quantity:2,discount:0,0000,unitprice:4,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:3,discount:0,0000,unitprice:3,4000
    quantity:5,discount:0,0000,unitprice:50,0000
    quantity:45,discount:0,0000,unitprice:3,0000
    quantity:2,discount:0,0000,unitprice:45,0000
    quantity:2,discount:0,0000,unitprice:50,0000
    quantity:13,discount:0,0000,unitprice:12,0000
    quantity:45,discount:0,0000,unitprice:1,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:2,discount:1,0000,unitprice:3,0000
    quantity:2,discount:1,0000,unitprice:3,0000
    quantity:3,discount:0,0000,unitprice:2,0000
    quantity:5,discount:0,0000,unitprice:10,0000
    quantity:1,discount:0,0000,unitprice:10,0000
  • Teraz kwerenda z możliwością parametryzacji zapytania, wersja prostsza dynamicznego dodawania wartości dla zapytania. Jest identyczna jak w wywołaniu WriteLine(…):
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var r = context.ExecuteStoreQuery<string>
    4.             („SELECT Name FROM company WHERE ShippingCity={0} AND billingcity={1}”, „New York”, „paris”);
    5.  
    6.     foreach (var name in r)
    7.     {
    8.         Trace.WriteLine(name);
    9.     }
    10. }

    Wynik:

    Billl Clay
  • Oraz wersja ciut trudniejsza, z wykorzystaniem paramentów ściślej związanych z bazą danych:
    1. using (var context = new Model.OrderITEntities())
    2. {
    3.     var a = new SqlParameter(„p0”, DbType.String) { Value = „New York” };
    4.     var b = new SqlParameter(„p1”, DbType.String) { Value = „Paris” };
    5.     var r = context.ExecuteStoreQuery<string>
    6.            („SELECT name FROM company WHERE shippingcity= @p0 AND billingcity= @p1”, b, a);
    7.     foreach (var name in r)
    8.     {
    9.         Trace.WriteLine(name);
    10.     }
    11. }

    Wynik:

    Billl Clay

To na razie tyle. Myślę, że wyskrobię jeszcze kilka wpisów dotyczących DB i EF. Przypominam także, że moje wpisy nie są idealne i mogą zawierać błędy. Także nie trzymałbym się tak bardzo kurczowo tego co napisałem. Najlepiej będzie, jeśli sami spróbujecie czy to co napisałem działa oraz popróbujecie zmodyfikować kod, który jest tutaj przedstawiony.
Jak zawsze chętnie przyjmę na klatę wszystkie komentarze, którymi tak chętnie się ze mną dzielicie 😉
Jarek