Temporal Table i Entity Framework

Posted on przez Daniel Plawgo

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:

public class BaseModel
{
public BaseModel()
{
IsActive = true;
}
public int Id { get; set; }
public bool IsActive { get; set; }
}
view raw BaseModel.cs hosted with ❤ by GitHub
public class Category : BaseModel
{
public string Name { get; set; }
public virtual ICollection<Product> Products { get; set; }
}
view raw Category.cs hosted with ❤ by GitHub
public class Product : BaseModel
{
public string Name { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
}
view raw Product.cs hosted with ❤ by GitHub

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

public partial class AddProductAndCategory : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Categories",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(),
IsActive = c.Boolean(nullable: false),
})
.PrimaryKey(t => t.Id);
CreateTable(
"dbo.Products",
c => new
{
Id = c.Int(nullable: false, identity: true),
Name = c.String(),
CategoryId = c.Int(nullable: false),
IsActive = c.Boolean(nullable: false),
})
.PrimaryKey(t => t.Id)
.ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true)
.Index(t => t.CategoryId);
}
public override void Down()
{
DropForeignKey("dbo.Products", "CategoryId", "dbo.Categories");
DropIndex("dbo.Products", new[] { "CategoryId" });
DropTable("dbo.Products");
DropTable("dbo.Categories");
}
}
view raw AddProductAndCategory.cs hosted with ❤ by GitHub

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

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
AddAndUpdateProductWithoutHistory();
}
private static void AddAndUpdateProductWithoutHistory()
{
using (DataContext db = new DataContext())
{
var category = db.Categories.FirstOrDefault();
var product = new Product()
{
Name = "Product",
Category = category
};
db.Products.Add(product);
db.SaveChanges();
product.Name = "New Product";
db.SaveChanges();
}
}
}
view raw Program1.cs hosted with ❤ by GitHub

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 30 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ą:

public class TemporalTableQueryBuilder
{
private const string CreateFormat =
@"
ALTER TABLE {0}
ADD ValidFrom datetime2(0)
GENERATED ALWAYS AS ROW START NOT NULL
DEFAULT SYSUTCDATETIME(),
ValidTo datetime2(0)
GENERATED ALWAYS AS ROW END NOT NULL
DEFAULT CAST('9999-12-31 23:59:59.9999999' AS datetime2),
PERIOD FOR SYSTEM_TIME (ValidFrom, ValidTo)
GO
ALTER TABLE {0}
SET (SYSTEM_VERSIONING = ON (HISTORY_TABLE = {0}History))
GO";
private const string DropFormat =
@"ALTER TABLE {0} SET ( SYSTEM_VERSIONING = OFF )
GO
ALTER TABLE {0} DROP PERIOD FOR SYSTEM_TIME
GO
DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
SELECT TOP 1 @sql = N'ALTER TABLE {0} DROP CONSTRAINT ['+dc.NAME+N']'
from sys.default_constraints dc
JOIN sys.columns c
ON c.default_object_id = dc.object_id
WHERE
dc.parent_object_id = OBJECT_ID('{0}')
AND (c.name = N'ValidFrom' OR c.name = N'ValidTo')
IF @@ROWCOUNT = 0 BREAK
EXEC (@sql)
END
ALTER TABLE {0} DROP COLUMN ValidFrom
GO
ALTER TABLE {0} DROP COLUMN ValidTo
GO
DROP TABLE {0}History
GO";
public static string GetCreateSql(string tableName)
{
return string.Format(CreateFormat, tableName);
}
public static string GetDropSql(string tableName)
{
return string.Format(DropFormat, tableName);
}
}
view raw TemporalTableQueryBuilder.cs hosted with ❤ by GitHub

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:

public partial class AddHistoryToProduct : DbMigration
{
public override void Up()
{
Sql(TemporalTableQueryBuilder.GetCreateSql("dbo.Products"));
}
public override void Down()
{
Sql(TemporalTableQueryBuilder.GetDropSql("dbo.Products"));
}
}
view raw AddHistoryToProduct.cs hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
}
private static int AddAndUpdateWithHistory()
{
int productId;
using (DataContext db = new DataContext())
{
var category = db.Categories.FirstOrDefault();
var product = new Product()
{
Name = "Product",
Category = category
};
db.Products.Add(product);
db.SaveChanges();
Thread.Sleep(1 * 1000);
productId = product.Id;
product.Name = "New Product";
db.SaveChanges();
}
return productId;
}
}
view raw Program2.cs hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
ShowHistoryUsingSql(productId);
}
private static void ShowHistoryUsingSql(int productId)
{
Console.WriteLine("ShowHistoryUsingSql:");
using (DataContext db = new DataContext())
{
var query = $"SELECT * FROM dbo.Products FOR SYSTEM_TIME ALL WHERE Id = {productId}";
var products =
db.Database.SqlQuery<Product>(query)
.ToList();
foreach (var item in products)
{
Console.WriteLine($"{item.Name}");
}
}
}
}
view raw Program3.cs hosted with ❤ by GitHub

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:

public abstract class BaseProduct : BaseModel
{
public string Name { get; set; }
public int CategoryId { get; set; }
public virtual Category Category { get; set; }
public DateTime ValidFrom { get; set; }
public DateTime ValidTo { get; set; }
}
view raw BaseProduct.cs hosted with ❤ by GitHub
public class Product : BaseProduct
{
}
view raw Product2.cs hosted with ❤ by GitHub
public class ProductHistory : BaseProduct
{
}
view raw ProductHistory.cs hosted with ❤ by GitHub

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:

public class BaseProductConfiguration : EntityTypeConfiguration<BaseProduct>
{
public BaseProductConfiguration()
{
HasKey(p => new { p.Id, p.ValidFrom, p.ValidTo });
Property(p => p.Id).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(p => p.ValidFrom).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
Property(p => p.ValidTo).HasDatabaseGeneratedOption(DatabaseGeneratedOption.Identity);
}
}
view raw BaseProductConfiguration.cs hosted with ❤ by GitHub

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):

public class ProductConfiguration : EntityTypeConfiguration<Product>
{
public ProductConfiguration()
{
Map(m => { m.ToTable("dbo.Products"); m.MapInheritedProperties(); });
}
}
view raw ProductConfiguration.cs hosted with ❤ by GitHub

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:

public partial class AddHistoryToProduct2 : DbMigration
{
public override void Up()
{
//DropPrimaryKey("dbo.Products");
//CreateTable(
// "dbo.ProductsHistory",
// c => new
// {
// Id = c.Int(nullable: false, identity: true),
// ValidFrom = c.DateTime(nullable: false),
// ValidTo = c.DateTime(nullable: false),
// Name = c.String(),
// CategoryId = c.Int(nullable: false),
// IsActive = c.Boolean(nullable: false),
// })
// .PrimaryKey(t => new { t.Id, t.ValidFrom, t.ValidTo })
// .ForeignKey("dbo.Categories", t => t.CategoryId, cascadeDelete: true)
// .Index(t => t.CategoryId);
//AddColumn("dbo.Products", "ValidFrom", c => c.DateTime(nullable: false));
//AddColumn("dbo.Products", "ValidTo", c => c.DateTime(nullable: false));
//AddPrimaryKey("dbo.Products", new[] { "Id", "ValidFrom", "ValidTo" });
}
public override void Down()
{
//DropForeignKey("dbo.ProductsHistory", "CategoryId", "dbo.Categories");
//DropIndex("dbo.ProductsHistory", new[] { "CategoryId" });
//DropPrimaryKey("dbo.Products");
//DropColumn("dbo.Products", "ValidTo");
//DropColumn("dbo.Products", "ValidFrom");
//DropTable("dbo.ProductsHistory");
//AddPrimaryKey("dbo.Products", "Id");
}
}
view raw AddHistoryToProduct2.cs hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
ShowHistoryUsingSql(productId);
GetProductById(productId);
}
private static void GetProductById(int productId)
{
Console.WriteLine("GetProductById:");
using (DataContext db = new DataContext())
{
BaseProduct product = db.Products.OfType<Product>().FirstOrDefault(p => p.Id == productId);
Console.WriteLine($"{product.Name}, {product.ValidFrom}, {product.ValidTo}");
}
}
}
view raw Program4.cs hosted with ❤ by GitHub

W efekcie na bazie wykonuje się takie zapytanie:

SELECT [Limit1].[C1] AS [C1],
[Limit1].[Id] AS [Id],
[Limit1].[ValidFrom] AS [ValidFrom],
[Limit1].[ValidTo] AS [ValidTo],
[Limit1].[Name] AS [Name],
[Limit1].[CategoryId] AS [CategoryId],
[Limit1].[Description] AS [Description],
[Limit1].[IsActive] AS [IsActive]
FROM (SELECT TOP (1) [Extent1].[Id] AS [Id],
[Extent1].[ValidFrom] AS [ValidFrom],
[Extent1].[ValidTo] AS [ValidTo],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[Description] AS [Description],
[Extent1].[IsActive] AS [IsActive],
'0X0X' AS [C1]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[Id] = 1003 /* @p__linq__0 */) AS [Limit1]
view raw query1.sql hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
ShowHistoryUsingSql(productId);
GetProductById(productId);
ShowHistoryUsingLinq(productId);
}
private static void ShowHistoryUsingLinq(int productId)
{
Console.WriteLine("ShowHistoryUsingLinq:");
using (DataContext db = new DataContext())
{
var products = db.Products.Where(p => p.Id == productId)
.OrderByDescending(p => p.ValidFrom);
foreach (var item in products)
{
Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
}
}
}
}
view raw Program5.cs hosted with ❤ by GitHub

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:

SELECT [Project3].[C8] AS [C1],
[Project3].[C1] AS [C2],
[Project3].[C2] AS [C3],
[Project3].[C3] AS [C4],
[Project3].[C4] AS [C5],
[Project3].[C5] AS [C6],
[Project3].[C6] AS [C7],
[Project3].[C7] AS [C8]
FROM (SELECT [UnionAll1].[Id] AS [C1],
[UnionAll1].[ValidFrom] AS [C2],
[UnionAll1].[ValidTo] AS [C3],
[UnionAll1].[Name] AS [C4],
[UnionAll1].[CategoryId] AS [C5],
[UnionAll1].[Description] AS [C6],
[UnionAll1].[IsActive] AS [C7],
CASE
WHEN ([UnionAll1].[C1] = 1) THEN '0X0X'
ELSE '0X1X'
END AS [C8]
FROM (SELECT [Extent1].[Id] AS [Id],
[Extent1].[ValidFrom] AS [ValidFrom],
[Extent1].[ValidTo] AS [ValidTo],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[Description] AS [Description],
[Extent1].[IsActive] AS [IsActive],
cast(0 as bit) AS [C1]
FROM [dbo].[ProductsHistory] AS [Extent1]
UNION ALL
SELECT [Extent2].[Id] AS [Id],
[Extent2].[ValidFrom] AS [ValidFrom],
[Extent2].[ValidTo] AS [ValidTo],
[Extent2].[Name] AS [Name],
[Extent2].[CategoryId] AS [CategoryId],
[Extent2].[Description] AS [Description],
[Extent2].[IsActive] AS [IsActive],
cast(1 as bit) AS [C1]
FROM [dbo].[Products] AS [Extent2]) AS [UnionAll1]
WHERE [UnionAll1].[Id] = 1003 /* @p__linq__0 */) AS [Project3]
ORDER BY [Project3].[C2] DESC
view raw query2.sql hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
ShowHistoryUsingSql(productId);
GetProductById(productId);
ShowHistoryUsingLinq(productId);
ShowProductInCategoryLazyLoadingProblem();
}
private static void ShowProductInCategoryLazyLoadingProblem()
{
Console.WriteLine("ShowProductInCategoryLazyLoadingProblem:");
using (DataContext db = new DataContext())
{
var category = db.Categories.FirstOrDefault();
foreach (var item in category.Products)
{
Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
}
}
}
}
view raw Program6.cs hosted with ❤ by GitHub

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

SELECT CASE
WHEN ([UnionAll1].[C1] = 1) THEN '0X0X'
ELSE '0X1X'
END AS [C1],
[UnionAll1].[Id] AS [C2],
[UnionAll1].[ValidFrom] AS [C3],
[UnionAll1].[ValidTo] AS [C4],
[UnionAll1].[Name] AS [C5],
[UnionAll1].[CategoryId] AS [C6],
[UnionAll1].[Description] AS [C7],
[UnionAll1].[IsActive] AS [C8]
FROM (SELECT [Extent1].[Id] AS [Id],
[Extent1].[ValidFrom] AS [ValidFrom],
[Extent1].[ValidTo] AS [ValidTo],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[Description] AS [Description],
[Extent1].[IsActive] AS [IsActive],
cast(0 as bit) AS [C1]
FROM [dbo].[ProductsHistory] AS [Extent1]
UNION ALL
SELECT [Extent2].[Id] AS [Id],
[Extent2].[ValidFrom] AS [ValidFrom],
[Extent2].[ValidTo] AS [ValidTo],
[Extent2].[Name] AS [Name],
[Extent2].[CategoryId] AS [CategoryId],
[Extent2].[Description] AS [Description],
[Extent2].[IsActive] AS [IsActive],
cast(1 as bit) AS [C1]
FROM [dbo].[Products] AS [Extent2]) AS [UnionAll1]
WHERE [UnionAll1].[CategoryId] = 1 /* @EntityKeyValue1 */
view raw query3.sql hosted with ❤ by GitHub

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:

class Program
{
static void Main(string[] args)
{
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
//AddAndUpdateProductWithoutHistory();
int productId = 0;
productId = AddAndUpdateWithHistory();
ShowHistoryUsingSql(productId);
GetProductById(productId);
ShowHistoryUsingLinq(productId);
ShowProductInCategoryLazyLoadingProblem();
ShowProductInCategoryLinq();
}
private static void ShowProductInCategoryLinq()
{
Console.WriteLine("ShowProductInCategoryLinq:");
using (DataContext db = new DataContext())
{
var category = db.Categories.FirstOrDefault();
foreach (var item in db.Products.OfType<Product>().Where(p => p.CategoryId == category.Id))
{
Console.WriteLine($"{item.Name}, {item.ValidFrom}, {item.ValidTo}");
}
}
}
}
view raw Program7.cs hosted with ❤ by GitHub

Zapytanie, które wykona się na bazie:

SELECT '0X0X' AS [C1],
[Extent1].[Id] AS [Id],
[Extent1].[ValidFrom] AS [ValidFrom],
[Extent1].[ValidTo] AS [ValidTo],
[Extent1].[Name] AS [Name],
[Extent1].[CategoryId] AS [CategoryId],
[Extent1].[Description] AS [Description],
[Extent1].[IsActive] AS [IsActive]
FROM [dbo].[Products] AS [Extent1]
WHERE [Extent1].[CategoryId] = 1 /* @p__linq__0 - [CategoryId] */
view raw query4.sql hosted with ❤ by GitHub

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:

public partial class AddDescriptionToProduct : DbMigration
{
public override void Up()
{
AddColumn("dbo.Products", "Description", c => c.String());
//AddColumn("dbo.ProductsHistory", "Description", c => c.String());
}
public override void Down()
{
//DropColumn("dbo.ProductsHistory", "Description");
DropColumn("dbo.Products", "Description");
}
}
view raw AddDescriptionToProduct.cs hosted with ❤ by GitHub

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ć. 🙂

Szkolenie Entity Framework Core

Szkolenie Entity Framework Core

Zainteresował Ciebie ten temat? A może chcesz więcej? Jak tak to zapraszam na moje autorskie szkolenie z Entity Framework Core.

3 thoughts on “Temporal Table i Entity Framework”

Dodaj komentarz

Twój adres e-mail nie zostanie opublikowany.