Tabele czasowe programu SQL Server/Azure SQL

Tabele czasowe programu SQL Server automatycznie śledzą wszystkie dane przechowywane w tabeli nawet po zaktualizowaniu lub usunięciu tych danych. Jest to osiągane przez utworzenie równoległej "tabeli historii", w której przechowywane są dane historyczne ze znacznikami czasu za każdym razem, gdy zostanie wprowadzona zmiana w tabeli głównej. Umożliwia to wykonywanie zapytań dotyczących danych historycznych, takich jak audyt, lub odtworzenie, na przykład po przypadkowej mutacji lub usunięciu.

Program EF Core obsługuje następujące funkcje:

• Tworzenie tabel czasowych przy użyciu migracji platformy EF Core
• Przekształcanie istniejących tabel w tabele czasowe, ponownie przy użyciu funkcji Migracje
• Wykonywanie zapytań dotyczących danych historycznych
• Przywracanie danych z jakiegoś punktu w przeszłości

Konfigurowanie tabeli czasowej

Konstruktor modelu może służyć do konfigurowania tabeli jako tabeli czasowej. Przykład:

modelBuilder
    .Entity<Employee>()
    .ToTable("Employees", b => b.IsTemporal());

Wskazówka

Pokazany tutaj kod pochodzi z TemporalTablesSample.cs.

W przypadku tworzenia bazy danych przy użyciu programu EF Core nowa tabela jest skonfigurowana jako tabela czasowa z domyślnymi wartościami programu SQL Server dla sygnatur czasowych i tabeli historii. Rozważmy na przykład Employee typ jednostki:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
}

Utworzona tabela czasowa wygląda następująco:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [PeriodEnd] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    [PeriodStart] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([PeriodStart], [PeriodEnd])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistory]))');

Zwróć uwagę, że program SQL Server tworzy dwie ukryte datetime2 kolumny o nazwie PeriodEnd i PeriodStart. Te "kolumny okresu" reprezentują zakres czasu, w którym istniały dane w wierszu. Domyślnie te kolumny są mapowane na właściwości w tle w modelu EF Core, co pozwala na ich stosowanie w zapytaniach, jak pokazano później. Począwszy od programu EF Core 11, kolumny okresów można również mapować na właściwości CLR w typie jednostki.

Ważne

Czasy w tych kolumnach są zawsze czasem UTC generowanym przez program SQL Server. Czasy UTC są używane dla wszystkich operacji obejmujących tabele czasowe, takie jak w zapytaniach przedstawionych poniżej.

Zauważ również, że skojarzona tabela historii o nazwie EmployeeHistory jest tworzona automatycznie. Nazwy kolumn okresów i tabeli historii można zmienić przy użyciu dodatkowej konfiguracji konstruktora modelu. Przykład:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart("ValidFrom");
                b.HasPeriodEnd("ValidTo");
                b.UseHistoryTable("EmployeeHistoricalData");
            }));

Jest to odzwierciedlone w tabeli utworzonej przez program SQL Server:

DECLARE @historyTableSchema sysname = SCHEMA_NAME()
EXEC(N'CREATE TABLE [Employees] (
    [EmployeeId] uniqueidentifier NOT NULL,
    [Name] nvarchar(100) NULL,
    [Position] nvarchar(100) NULL,
    [Department] nvarchar(100) NULL,
    [Address] nvarchar(1024) NULL,
    [AnnualSalary] decimal(10,2) NOT NULL,
    [ValidFrom] datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidTo] datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
    CONSTRAINT [PK_Employees] PRIMARY KEY ([EmployeeId]),
    PERIOD FOR SYSTEM_TIME([ValidFrom], [ValidTo])
) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [' + @historyTableSchema + N'].[EmployeeHistoricalData]))');

Korzystanie z tabel czasowych

W większości przypadków tabele czasowe są używane tak samo jak każda inna tabela. Oznacza to, że kolumny okresu i dane historyczne są obsługiwane w sposób przejrzysty przez SQL Server, tak aby aplikacja mogła je zignorować. Na przykład nowe jednostki można zapisywać w bazie danych w normalny sposób:

context.AddRange(
    new Employee
    {
        Name = "Pinky Pie",
        Address = "Sugarcube Corner, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Party Organizer",
        AnnualSalary = 100.0m
    },
    new Employee
    {
        Name = "Rainbow Dash",
        Address = "Cloudominium, Ponyville, Equestria",
        Department = "DevDiv",
        Position = "Ponyville weather patrol",
        AnnualSalary = 900.0m
    },
    new Employee
    {
        Name = "Fluttershy",
        Address = "Everfree Forest, Equestria",
        Department = "DevDiv",
        Position = "Animal caretaker",
        AnnualSalary = 30.0m
    });

await context.SaveChangesAsync();

Te dane mogą być następnie odpytywane, aktualizowane i usuwane w normalny sposób. Przykład:

var employee = await context.Employees.SingleAsync(e => e.Name == "Rainbow Dash");
context.Remove(employee);
await context.SaveChangesAsync();

Ponadto po normalnym zapytaniu śledzenia można uzyskać dostęp do wartości z kolumn okresu bieżących danych z śledzonych jednostek. Jeśli kolumny okresu są mapowane na właściwości CLR, możesz uzyskać do nich dostęp bezpośrednio w jednostce; w przeciwnym razie użyj polecenia EF.Property, aby uzyskać do nich dostęp jako właściwości cieniowe. Przykład:

var employees = await context.Employees.ToListAsync();
foreach (var employee in employees)
{
    var employeeEntry = context.Entry(employee);
    var validFrom = employeeEntry.Property<DateTime>("ValidFrom").CurrentValue;
    var validTo = employeeEntry.Property<DateTime>("ValidTo").CurrentValue;

    Console.WriteLine($"  Employee {employee.Name} valid from {validFrom} to {validTo}");
}

To drukuje:

Starting data:
  Employee Pinky Pie valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Rainbow Dash valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM
  Employee Fluttershy valid from 8/26/2021 4:38:58 PM to 12/31/9999 11:59:59 PM

Zwróć uwagę, że kolumna ValidTo (domyślnie nazywana PeriodEnd) zawiera wartość maksymalną datetime2 . Tak jest zawsze w przypadku bieżących wierszy w tabeli. Kolumna ValidFrom (domyślnie nazywana PeriodStart) zawiera czas w UTC, w którym wiersz został wstawiony.

Wykonywanie zapytań dotyczących danych historycznych

Program EF Core obsługuje zapytania obejmujące dane historyczne za pośrednictwem kilku wyspecjalizowanych operatorów zapytań:

• TemporalAsOf: zwraca wiersze, które były aktywne (bieżące) o danej godzinie UTC. Jest to pojedynczy wiersz z bieżącej tabeli lub tabeli historii dla danego klucza podstawowego.
• TemporalAll: zwraca wszystkie wiersze w danych historycznych. Zazwyczaj jest to wiele wierszy z tabeli historycznej i/lub z tabeli bieżącej dla danego klucza podstawowego.
• TemporalFromTo: zwraca wszystkie wiersze, które były aktywne między dwoma podanymi godzinami UTC. Może chodzić o wiele wierszy z tabeli historii i/lub bieżącej tabeli w odniesieniu do danego klucza podstawowego.
• TemporalBetween: To samo co TemporalFromTo, z tą różnicą, że uwzględniane są wiersze, które stały się aktywne na górnej granicy.
• TemporalContainedIn: zwraca wszystkie wiersze, które zaczęły być aktywne i kończą się aktywne między dwoma podanymi godzinami UTC. Może chodzić o wiele wierszy z tabeli historii i/lub bieżącej tabeli w odniesieniu do danego klucza podstawowego.

Uwaga / Notatka

Zapoznaj się z dokumentacją tabel czasowych programu SQL Server , aby uzyskać więcej informacji na temat dokładnie wierszy uwzględnionych dla każdego z tych operatorów.

Na przykład po wprowadzeniu niektórych aktualizacji i usunięcia danych możemy uruchomić zapytanie przy użyciu polecenia TemporalAll , aby wyświetlić dane historyczne:

var history = await context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

foreach (var pointInTime in history)
{
    Console.WriteLine(
        $"  Employee {pointInTime.Employee.Name} was '{pointInTime.Employee.Position}' from {pointInTime.ValidFrom} to {pointInTime.ValidTo}");
}

Zauważ, jak EF.Property metoda może być używana do uzyskiwania dostępu do wartości z kolumn dotyczących okresów. Jest on używany w klauzuli OrderBy do sortowania danych, a następnie w projekcji w celu uwzględnienia tych wartości w zwracanych danych. Jeśli kolumny okresowe są mapowane na właściwości CLR, możesz odwoływać się do nich bezpośrednio w zapytaniu zamiast korzystania z EF.Property.

To zapytanie przywraca następujące dane:

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM

Zwróć uwagę, że ostatni wiersz przestał być aktywny o godzinie 26.08.2021 4:44:59. Wynika to z faktu, że wiersz dla Rainbow Dash został usunięty z tabeli głównej wówczas. Później zobaczymy, jak można przywrócić te dane.

Podobne zapytania można zapisywać przy użyciu metody TemporalFromTo, TemporalBetweenlub TemporalContainedIn. Przykład:

var history = await context
    .Employees
    .TemporalBetween(timeStamp2, timeStamp3)
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => EF.Property<DateTime>(e, "ValidFrom"))
    .Select(
        e => new
        {
            Employee = e,
            ValidFrom = EF.Property<DateTime>(e, "ValidFrom"),
            ValidTo = EF.Property<DateTime>(e, "ValidTo")
        })
    .ToListAsync();

To zapytanie zwraca następujące wiersze:

Historical data for Rainbow Dash between 8/26/2021 4:41:14 PM and 8/26/2021 4:42:44 PM:
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM

Przywracanie danych historycznych

Jak wspomniano powyżej, Rainbow Dash został usunięty z Employees tabeli. Był to wyraźnie błąd, więc wróćmy do punktu w czasie i przywróćmy brakujący wiersz z tego czasu.

var employee = await context
    .Employees
    .TemporalAsOf(timeStamp2)
    .SingleAsync(e => e.Name == "Rainbow Dash");

context.Add(employee);
await context.SaveChangesAsync();

To zapytanie zwraca pojedynczy wiersz dla Rainbow Dash, tak jak wyglądała o danej godzinie UTC. Wszystkie zapytania wykorzystujące operatory czasowe są domyślnie bez śledzenia, więc zwracana encja nie jest śledzona. Ma to sens, ponieważ obecnie nie istnieje w tabeli głównej. Aby ponownie wstawić jednostkę do tabeli głównej, po prostu oznaczymy ją jako Added , a następnie wywołamy metodę SaveChanges.

Po ponownym wstawieniu wiersza Rainbow Dash, zapytywanie danych historycznych pokazuje, że wiersz został przywrócony, tak, jak wyglądał o danej godzinie UTC.

Historical data for Rainbow Dash:
  Employee Rainbow Dash was 'Ponyville weather patrol' from 8/26/2021 4:38:58 PM to 8/26/2021 4:40:29 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:40:29 PM to 8/26/2021 4:41:59 PM
  Employee Rainbow Dash was 'Wonderbolt Reservist' from 8/26/2021 4:41:59 PM to 8/26/2021 4:43:29 PM
  Employee Rainbow Dash was 'Wonderbolt' from 8/26/2021 4:43:29 PM to 8/26/2021 4:44:59 PM
  Employee Rainbow Dash was 'Wonderbolt Trainee' from 8/26/2021 4:44:59 PM to 12/31/9999 11:59:59 PM

Mapowanie kolumn okresu na właściwości CLR

Uwaga / Notatka

Ta funkcja jest wprowadzana w programie EF Core 11, który jest obecnie w wersji zapoznawczej.

Domyślnie kolumny okresów w tabeli czasowej są mapowane na właściwości shadow w modelu platformy EF Core, co oznacza, że nie muszą istnieć w typie jednostki .NET. Począwszy od programu EF Core 11, można zamiast tego mapować kolumny okresów na zwykłe właściwości CLR w typie jednostki, co umożliwia bezpośredni dostęp do ich wartości.

Aby to zrobić, dodaj DateTime właściwości dla początku i końca okresu do typu jednostki:

public class Employee
{
    public Guid EmployeeId { get; set; }
    public string Name { get; set; }
    public string Position { get; set; }
    public string Department { get; set; }
    public string Address { get; set; }
    public decimal AnnualSalary { get; set; }
    public DateTime PeriodStart { get; set; }
    public DateTime PeriodEnd { get; set; }
}

Następnie skonfiguruj tabelę czasową tak, aby korzystała z tych właściwości za pomocą wyrażenia lambda:

modelBuilder
    .Entity<Employee>()
    .ToTable(
        "Employees",
        b => b.IsTemporal(
            b =>
            {
                b.HasPeriodStart(e => e.PeriodStart);
                b.HasPeriodEnd(e => e.PeriodEnd);
            }));

Uwaga / Notatka

Właściwości okresu są automatycznie konfigurowane przy użyciu ValueGenerated.OnAddOrUpdate, więc ich wartości są zawsze generowane przez SQL Server. Nie musisz — i nie należy — ustawiać ich wartości podczas wstawiania lub aktualizowania jednostek.

Gdy kolumny czasowe są mapowane na właściwości CLR, można uzyskać dostęp do ich wartości bezpośrednio w encjach zamiast używać EF.Property:

var history = context
    .Employees
    .TemporalAll()
    .Where(e => e.Name == "Rainbow Dash")
    .OrderBy(e => e.PeriodStart)
    .Select(
        e => new
        {
            Employee = e,
            e.PeriodStart,
            e.PeriodEnd
        })
    .ToList();