SQL Server/Azure SQL-Zeitliche Tabellen

Zeitliche SQL Server-Tabellen verfolgen automatisch alle Daten, die jemals in einer Tabelle gespeichert sind, auch nachdem diese Daten aktualisiert oder gelöscht wurden. Dies wird erreicht, indem eine parallele "Verlaufstabelle" erstellt wird, in der zeitstempelte Verlaufsdaten gespeichert werden, wenn eine Änderung an der Haupttabelle vorgenommen wird. Auf diese Weise können historische Daten abgefragt werden, z. B. für die Prüfung, oder wiederhergestellt, z. B. für die Wiederherstellung nach versehentlicher Änderung oder Löschung.

EF Core unterstützt:

• Erstellung zeitlicher Tabellen mithilfe von EF Core-Migrationen
• Transformation vorhandener Tabellen in zeitliche Tabellen, wieder mithilfe von Migrationen
• Abfragen von historischen Daten
• Wiederherstellen von Daten aus einem bestimmten Punkt in der Vergangenheit

Konfigurieren einer zeitlichen Tabelle

Der Modell-Generator kann verwendet werden, um eine Tabelle als zeitlich zu konfigurieren. Beispiel:

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

Bei Verwendung von EF Core zum Erstellen der Datenbank wird die neue Tabelle als zeitliche Tabelle mit den SQL Server-Standardwerten für die Zeitstempel und Verlaufstabelle konfiguriert. Betrachten Sie z. B. einen Entitätstyp Employee :

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; }
}

Die erstellte zeitliche Tabelle sieht wie folgt aus:

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]))');

Beachten Sie, dass SQL Server zwei ausgeblendete datetime2 Spalten erstellt, namens PeriodEnd und PeriodStart. Diese "Zeitraumspalten" stellen den Zeitraum dar, in dem die Daten in der Zeile vorhanden waren. Standardmäßig werden diese Spalten Schatteneigenschaften im EF Core-Modell zugeordnet, sodass sie wie später dargestellt in Abfragen verwendet werden können. Ab EF Core 11 können periodische Spalten auch auf CLR-Eigenschaften Ihres Entitätstyps zugeordnet werden.

Beachten Sie auch, dass eine zugeordnete Verlaufstabelle namens EmployeeHistory automatisch erstellt wird. Die Namen der Periodenspalten und der Verlaufs- bzw. Historientabelle können durch zusätzliche Konfiguration am Modell-Generator geändert werden. Beispiel:

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

Dies spiegelt sich in der tabelle wider, die von SQL Server erstellt wurde:

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]))');

Verwenden von zeitlichen Tabellen

Die meiste Zeit werden zeitliche Tabellen wie jede andere Tabelle verwendet. Dies bedeutet, dass die Periodenspalten und historischen Daten von SQL Server transparent behandelt werden, sodass sie von der Anwendung ignoriert werden können. Beispielsweise können neue Entitäten auf normale Weise in der Datenbank gespeichert werden:

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();

Diese Daten können dann auf normale Weise abgefragt, aktualisiert und gelöscht werden. Beispiel:

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

Außerdem kann nach einer normalen Nachverfolgungsabfrage über die nachverfolgten Entitäten auf die Werte aus den Zeitraumspalten der aktuellen Daten zugegriffen werden. Wenn die periodischen Spalten CLR-Eigenschaften zugeordnet sind, können Sie direkt auf der Entität auf sie zugreifen; andernfalls verwenden Sie EF.Property, um auf sie als Schatteneigenschaften zuzugreifen. Beispiel:

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}");
}

Dies ergibt folgende Ausgabe:

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

Beachten Sie, dass die ValidTo Spalte (standardmäßig aufgerufen PeriodEnd) den datetime2 maximalen Wert enthält. Dies ist immer der Fall für die aktuellen Zeilen in der Tabelle. Die ValidFrom Spalten (standardmäßig genannt PeriodStart) enthalten die UTC-Zeit, zu der die Zeile eingefügt wurde.

Abfragen von historischen Daten

EF Core unterstützt Abfragen, die historische Daten über mehrere spezialisierte Abfrageoperatoren enthalten:

• TemporalAsOf: Gibt Zeilen zurück, die zur angegebenen UTC-Zeit aktiv (aktuell) waren. Dies ist eine einzelne Zeile aus der aktuellen Tabelle oder Verlaufstabelle für einen bestimmten Primärschlüssel.
• TemporalAll: Gibt sämtliche Zeilen in den historischen Daten zurück. Dies sind in der Regel viele Tabellenzeilen aus der Verlaufstabelle und/oder der aktuellen Tabelle für einen bestimmten Primärschlüssel.
• TemporalFromTo: Gibt alle Zeilen zurück, die zwischen zwei angegebenen UTC-Zeiten aktiv waren. Dies kann viele Zeilen aus der Verlaufstabelle und/oder der aktuellen Tabelle für einen bestimmten Primärschlüssel sein.
• TemporalBetween: Dasselbe wie TemporalFromTo, mit der Ausnahme, dass Zeilen enthalten sind, die an der oberen Grenze aktiv wurden.
• TemporalContainedIn: Gibt alle Zeilen zurück, die innerhalb des Zeitraums zwischen zwei bestimmten UTC-Zeiten sowohl aktiv geworden als auch ihre Aktivität beendet haben. Dies kann viele Zeilen aus der Verlaufstabelle und/oder der aktuellen Tabelle für einen bestimmten Primärschlüssel sein.

Nach einigen Aktualisierungen und Löschungen an unseren Daten können wir beispielsweise eine Abfrage TemporalAll ausführen, um die historischen Daten anzuzeigen:

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}");
}

Beachten Sie, wie die EF.Property-Methode verwendet werden kann, um auf Werte aus den Zeitspalten zuzugreifen. Dies wird in der OrderBy Klausel zum Sortieren der Daten und dann in einer Projektion verwendet, um diese Werte in die zurückgegebenen Daten einzuschließen. Wenn die Zeitraumspalten CLR-Eigenschaften zugeordnet sind, können Sie sie direkt in der Abfrage referenzieren, anstatt EF.Property zu verwenden.

Diese Abfrage gibt die folgenden Daten zurück:

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

Beachten Sie, dass die letzte zurückgegebene Zeile am 26.08.2021 um 16:44:59 Uhr nicht mehr aktiv war. Dies liegt daran, dass die Zeile für Rainbow Dash zu diesem Zeitpunkt aus der Haupttabelle gelöscht wurde. Wir werden später sehen, wie diese Daten wiederhergestellt werden können.

Ähnliche Abfragen können mit TemporalFromTo, TemporalBetween oder TemporalContainedIn geschrieben werden. Beispiel:

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();

Diese Abfrage gibt die folgenden Zeilen zurück:

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

Wiederherstellen von historischen Daten

Wie bereits erwähnt, wurde Rainbow Dash aus der Employees Tabelle gelöscht. Dies war eindeutig ein Fehler, also kehren wir zu einem Punkt in die Zeit zurück und stellen die fehlende Zeile aus dieser Zeit wieder her.

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

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

Diese Abfrage gibt eine einzelne Zeile für Rainbow Dash zurück, wie sie zur angegebenen UTC-Zeit war. Alle Abfragen mit temporären Operatoren werden standardmäßig nicht nachverfolgt, sodass auch die zurückgegebene Entität hier nicht nachverfolgt wird. Dies ist sinnvoll, da sie derzeit nicht in der Haupttabelle vorhanden ist. Um die Entität erneut in die Haupttabelle einzufügen, markieren wir sie einfach als Added und rufen sie dann auf SaveChanges.

Nach dem erneuten Einfügen der Zeile Rainbow Dash zeigt die Abfrage der historischen Daten an, dass die Zeile wiederhergestellt wurde, wie sie zu der angegebenen UTC-Zeit war:

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

Zuordnen von Periodenspalten zu CLR-Eigenschaften

Standardmäßig werden die Zeitraumspalten in einer temporalen Tabelle den Shadow-Eigenschaften im EF Core-Modell zugeordnet, das bedeutet, dass sie nicht in Ihrem .NET-Entitätstyp vorhanden sein müssen. Ab EF Core 11 können Sie stattdessen Periodenspalten regulären CLR-Eigenschaften in Ihrem Entitätstyp zuordnen, sodass Sie direkt auf ihre Werte zugreifen können.

Fügen Sie dazu DateTime Eigenschaften für den Beginn und das Ende des Zeitraums zu Ihrem Entitätstyp hinzu:

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; }
}

Konfigurieren Sie dann die zeitliche Tabelle so, dass diese Eigenschaften über einen Lambda-Ausdruck verwendet werden:

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

Wenn Periodenspalten CLR-Eigenschaften zugeordnet sind, können Sie auf ihre Werte direkt auf der Entität zugreifen, statt EF.Property zu verwenden.

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();