Temporal Table i Entity Framework

Wprowadzenie

W poprzednim wpisie pokazałem Ci, jak działa w SQL Server mechanizm Temporal Tables. Dzięki niemu możemy w prosty sposób bezpośrednio w samej bazie danych zapisywać historię zmian rekordów. O ile z użyciem tego w czystym SQL (np. za pomocą ADO.NET, Dapper lub innego Micro ORM) nie ma większych problemów, to już w przypadku Entity Framework są. W teorii standardowy Entity Framework nie wspiera Temporal Table. Co do wersji core – widziałem, że coś tam już jest, ale jeszcze tego nie testowałem.

W tym wpisie przedstawię Ci moje rozwiązanie, które udało mi się wypracować, aby mieć namiastkę wsparcia Temporal Tables w Entity Framework. Nie jest to rozwiązanie idealne, ale działa. Ma swoje wady, o których warto wiedzieć i pamiętać. Z drugiej strony dzięki niemu możemy wyciągać dane z historii bez konieczności pisania zapytania w czystym SQL.

Startowy stan projektu testowego

Wpis zacznę od sytuacji, w której mamy już tabelę z danymi i do niej chcemy dodać tabelę z historią. Tradycyjnie w przykładzie użyłem klasy Product do testów. Do tego dodałem klasę Category, która jest powiązana z klasą Product relacją jeden do wielu (produkt znajduje się w jakiejś kategorii). Dodatkowo w testowej aplikacji znajduje się klasa BaseModel, która zawiera standardowe właściwości.

Początkowy kod klas wygląda tak:

Dla powyższego kodu Entity Framework wygenerował taką migrację:

Do pierwszych testów użyjemy następującego kodu:

W kodzie nie dzieje się nic ciekawego. Dodajemy jeden produkt i później go dwukrotnie modyfikujemy. W bazie na końcu mamy zapisaną ostatnią wersję produktu, bez informacji o wcześniejszych stanach:

ef history products

Darmowy kurs Visual Studio

Pracując z setkami programistów, zauważyłem, że większość osób nie pracuje efektywnie w Visual Studio. W skrajnych przypadkach korzystali z kopiowania z wykorzystaniem menu Edit. Wiem, że to dziwne, ale naprawdę niektórzy tak pracują. Dlatego postanowiłem stworzyć kurs Visual Studio – aby pomóc koleżankom i kolegom w efektywniejszej pracy.

Przygotowałem 20 lekcji e-mail, w których pokażę Ci, w jaki sposób pracować efektywniej i szybciej w Visual Studio. Poznasz dodatki, bez których nie wyobrażam sobie pracy w tym IDE.

Po więcej informacji zapraszam na dedykowaną stronę kursu: Darmowy Kurs Visual Studio.

Quiz C#

Ostatnio przygotowałem również quiz C#, w którym możesz sprawdzić swoją wiedzę. Podejmiesz wyzwanie?

Dodanie tabeli z historią

Dodanie samej Temporal Table do tabeli z danymi nie jest problematyczne. Wystarczy wygenerować nową pustą migrację w Entity Framework i wykonać w metodzie Up skrypt z poprzedniego wpisu. W przypadku gdy dodajemy nową tabelę i chcemy, aby od razu miała historię, wystarczy wykonać ten sql na końcu migracji dodającej tabelę.

W projekcie dodałem klasę TemporalTableQueryBuilder, która ma zaszyty szablon komendy sql dla dodawania oraz usuwania Temporal Table, a dodatkowo dwie metody, które na podstawie nazwy tabeli zwracają komendę do tworzenia oraz usuwania tabeli z historią:

Obie metody wykonujemy w odpowiednich metodach w migracji. Poniżej znajduje się migracja, która została wygenerowana bez zmian w modelu (była ona pusta po utworzeniu) i która włącza historię danych dla tabeli Products:

Dodanie historii w ten sposób nie wpływa za bardzo na działanie Entity Framework. Poniżej znajduje się zmodyfikowany wcześniejszy kod, który dodawał i modyfikował produkt:

Główną różnicą jest dodanie Thread.Sleep między poszczególnymi wywołaniami metody SaveChanges. Jest to spowodowane tym, że SQL Server gubi się podczas zwracania historii, gdy operacja wykonała się w tej samej sekundzie:

ef history products with the same second

Pobranie historii via SQL

Możemy również wyciągnąć dane z historii, ale niestety zapytanie musimy zapisać w czystym SQL i skorzystać z metody SqlQuery:

W efekcie otrzymamy listę zmian:

ef history products list

Powyższy kod ma kilka wad. Po pierwsze zapytanie jest zapisane w stringu, przez co bardzo łatwo o wyjątek po zmianie w schemacie bazy danych (np. poprzez zmianę nazwy tabeli). Do takiego kodu trzeba by dodać testy integracyjne, które cały czas weryfikowałyby, czy działa on poprawnie.

Dodatkowo w wyniku zastosowania powyższego kodu otrzymamy tylko same dane produktu, bez informacji o tym, kiedy one obowiązywały (klasa Product nie ma właściwości dla ValidFrom oraz ValidTo). Możemy to obejść poprzez skorzystanie z innej klasy, do której Entity Framework zmaterializuje wynik zapytania i która będzie miała obie te właściwości.

Kolejnym problemem jest to, że Entity Framework przy wywołaniu metody SqlQuery pomija wszelkie mapowania dla klasy (np. inne nazwy kolumn niż właściwości), co może być sporym ograniczeniem podczas korzystania z Temporal Table.

Możemy również spróbować innego podejścia, które rozwiązuje część powyższych problemów, ale niestety wprowadza inne. W zależności od potrzeby możemy skorzystać z jednego lub drugiego.

Table per Concrete Type

Entity Framework wspiera różne modele obsługi dziedziczenia w bazie danych. Jednym z sposobów jest skorzystanie z podejścia Table per Concrete Type. W nim każda klasa w modelu ma swoją własną tabelę w bazie danych, która zawiera kolumny dla wszystkich zmapowanych właściwości z klasy.

Możemy spróbować wykorzystać ten model dziedziczenia, aby dodać część wsparcia Temporal Table w Entity Framework. Szczególnie, że obie tabele (Products oraz ProductsHistory) mają taką samą strukturę. Skorzystanie z Table per Concrete Type wymaga trochę pracy i założeń, ale w efekcie możemy uzyskać coś działającego. 🙂

Na początku musimy zmienić nieco model danych w aplikacji. Będziemy mieli dwie klasy zawierające dane produktu: klasę Product dla aktualnych danych (która będzie zmapowana do tabeli Products) oraz ProductHistory dla danych historycznych (zmapowana do tabeli ProductsHistory). Do tego potrzebujemy dla tych dwóch klas abstrakcyjnej klasy bazowej, która będzie zawierać wszystkie właściwości dla produktów (w tym również ValidFrom oraz ValidTo) i której użyjemy w DataContext oraz w relacjach między obiektami.

Zmiany w kodzie wyglądają tak:

Musimy też wykonać jedno założenie z racji działania Entity Framework w modelu TPC. Chodzi o to, że Entity Framework zakłada, że klucze będą unikalne w obu tabelach, co oczywiście w naszym przypadku z założenia nie jest spełnione. Dlatego na poziomie modelu zmienimy klucz dla rekordu z samego Id na Id, ValidFrom oraz ValidTo. Zapisuję to w klasie konfiguracji BaseProduct:

Do tego klasy konfiguracji Product oraz ProductHistory, które określają nazwę zmapowanej tabeli oraz model dziedziczenia (wywołanie metody MapInheritedProperties określa model Table per Concrete Type):

W tym momencie możemy już wygenerować migrację w Entity Framework. Jest ona jest potrzebna, bo wykonaliśmy zmiany w kodzie. Z racji tego, że struktura bazy jest taka, jakiej potrzebujemy, w tym przypadku usunę (w samym przykładzie zakomentuję) wygenerowany kod. Migracja wygląda tak:

W normalnej sytuacji migracje AddHistoryToProduct oraz AddHistoryToProduct2 są połączone w jedną migrację. Po tej zmianie testowy kod działa tak samo i bez problemu dodaje dane.

Pobieranie danych z bazy

Po prowadzeniu modelu dziedziczenie Table per Concrete Type zmienia się trochę sposób pobierania danych. Na ogół w aplikacji warstwę dostępu do danych chowamy przed resztą systemu na przykład za pomocą repozytoriów, więc możemy dość łatwo poprawić ten kod.

Właściwość typu DbSet w DataContext jako parametr generyczny w tym momencie ma typ BaseProduct. Gdy chcemy pobrać aktualny stan projektu, musimy w pierwszej kolejności w zapytaniu LINQ wykonać metodę OfType, w której określamy, że interesuje nas tylko tabela Products:

W efekcie na bazie wykonuje się takie zapytanie:

Wygenerowane przez Entity Framework zapytanie jest bardzo podobne do tego, co generowało się wcześniej. Tak naprawdę dodana jest dodatkowa kolumna C1 określająca typ obiektu (w tym przypadku Product).

W przypadku gdy chcemy pobrać tylko historyczne dane, zmieniamy typ w wywołaniu metody OfType na ProductHistory.

Możemy również pobrać dane z obu tabel na raz i uzyskać efekt taki, jak we wcześniejszej metodzie ShowHistoryUsingSql:

Aby to osiągnąć, usuwamy wywołanie metody OfType z zapytania. Warto dodać jeszcze sortowanie po ValidFrom, aby mieć pewność, że pierwszy rekord to aktualne dane. W efekcie na bazie wykonuje się zapytanie:

Jest ono inne i mniej efektywne niż skorzystanie z dedykowanej składni, ale działa, jest zapisane w LINQ i umożliwia rozbudowę tego zapytania o kolejne warunki itp.

Problemy

Niestety użycie Table per Concrete Type ma też swoje problemy, o których warto pamiętać. W przykładzie znajduje się relacja jeden do wielu między produktem i kategorią. W momencie gdy chcielibyśmy wyświetlić wszystkie produkty z kategorii za pomocą właściwości Products z klasy Category,  otrzymamy dane z obu tabel, czyli również i historyczne rekordy:

Ponieważ i w tym przypadku Entity Framework generuje w zapytaniu UNION:

Aby rozwiązać ten problem, trzeba w trochę inny sposób pobrać dane. Skorzystanie z metody OfType z parametrem Product na poziomie właściwości Products rozwiąże problem, ale nie w taki sposób, jakiego potrzebujemy. Zwrócona kolekcja będzie zawierała tylko aktualne produkty, ale po stronie bazy wykona się zapytanie z UNION i po stronie aplikacji nastąpi filtrowanie obiektów.

Rozwiązaniem jest zbudowanie zapytanie bezpośrednio na właściwości Products z DataContext, dodanie OfType i dodanie warunku dla CategoryId:

Zapytanie, które wykona się na bazie:

Problemów jest jeszcze więcej. W kolejnym wpisie pozostaniemy w temacie dodania obsługi Temporal Tables w Entity Framework  i zobaczymy kilka innych problemów.

Migracje

W przypadku zmian w definicji klasy (np. dodawanie lub usunięcie właściwości w BaseProduct) musimy pamiętać, że SQL Server automatycznie zmieni schemat tabeli z historią. Dlatego po wygenerowaniu migracji należy usunąć wygenerowane zmiany. Poniżej migracja, która została wygenerowania po dodaniu właściwości Description w klasie BaseProduct:

Zakomentowany kod trzeba usunąć. Po wykonaniu tej migracji schemat bazy wygląda tak:

ef history migration

Kolumna Description pojawiła się również w tabeli ProductsHistory.

Przykład

Na githubie (https://github.com/danielplawgo/SqlHistory) znajduje się przykład do tego wpisu. Przykład zawiera również zmiany dodane w kolejnym wpisie o interceptorach w Entity Framework. Po jego pobraniu należy w app.config ustawić connection string do bazy testowej.

Podsumowanie

Temporal Table jest bardzo fajnym mechanizmem. Szkoda, że Entity Framework nie ma do niego wsparcia i trzeba kombinować. W zależności od potrzeby możesz tylko włączyć mechanizm na poziomie bazy danych i zapisywać historię. Jeśli pojawi się konieczność pobierania danych, to myślę, że podejście z Table per Concrete Type może być ciekawym rozwiązaniem, które niestety ma też swoje problemy.

W kolejnym wpisie pozostaniemy w tym temacie. Zobaczymy jeszcze kilka innych problemów, które postaramy się rozwiązać. 🙂

1 thought on “Temporal Table i Entity Framework

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *