Tables temporelles de SQL Server/Azure SQL

Les tables temporelles SQL Server effectuent automatiquement le suivi de toutes les données jamais stockées dans une table, même après la mise à jour ou la suppression de ces données. Pour ce faire, créez une « table d’historique » parallèle dans laquelle les données historiques horodatées sont stockées chaque fois qu’une modification est apportée à la table principale. Cela permet aux données historiques d’être interrogées, telles que pour l’audit ou la restauration, comme pour la récupération après une mutation ou une suppression accidentelles.

EF Core prend en charge :

• Création de tables temporelles à l’aide des migrations EF Core
• Transformation des tables existantes en tables temporelles, à nouveau à l’aide de migrations
• Interrogation des données historiques
• Restauration des données à partir d’un point dans le passé

Configuration d’une table temporelle

Le générateur de modèles peut être utilisé pour configurer une table comme temporelle. Par exemple:

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

Lorsque vous utilisez EF Core pour créer la base de données, la nouvelle table est configurée en tant que table temporelle avec les valeurs par défaut de SQL Server pour les horodatages et la table d’historique. Par exemple, considérez un type d’entité 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; }
}

La table temporelle créée ressemble à ceci :

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

Notez que SQL Server crée deux colonnes masquées datetime2 appelées PeriodEnd et PeriodStart. Ces « colonnes de période » représentent l’intervalle de temps pendant lequel les données de la ligne existaient. Par défaut, ces colonnes sont mappées aux propriétés d’ombre dans le modèle EF Core, ce qui leur permet d’être utilisées dans les requêtes comme indiqué plus tard. À compter d’EF Core 11, les colonnes de période peuvent également être mappées aux propriétés CLR sur votre type d’entité.

Notez également qu’une table d’historique associée appelée EmployeeHistory est créée automatiquement. Les noms des colonnes de période et de la table d’historique peuvent être modifiés avec une configuration supplémentaire au générateur de modèles. Par exemple:

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

Cela est reflété dans la table créée par 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]))');

Utilisation de tables temporelles

La plupart du temps, les tables temporelles sont utilisées comme n’importe quelle autre table. Autrement dit, les colonnes de période et les données historiques sont gérées de manière transparente par SQL Server afin que l’application puisse les ignorer. Par exemple, de nouvelles entités peuvent être enregistrées dans la base de données de la manière normale :

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

Ces données peuvent ensuite être interrogées, mises à jour et supprimées de la manière normale. Par exemple:

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

En outre, après une requête de suivi normale, les valeurs des colonnes de période des données actuelles sont accessibles à partir des entités suivies. Si les colonnes de période sont mappées aux propriétés CLR, vous pouvez y accéder directement sur l’entité ; sinon, utilisez-les EF.Property pour y accéder en tant que propriétés d’ombre. Par exemple:

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

Cela imprime :

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

Notez que la ValidTo colonne (par défaut appelée PeriodEnd) contient la datetime2 valeur maximale. Il en est toujours ainsi pour les lignes actuelles du tableau. Les ValidFrom colonnes (par défaut appelées PeriodStart) contiennent l’heure UTC que la ligne a été insérée.

Interrogation des données historiques

EF Core prend en charge les requêtes qui incluent des données historiques via plusieurs opérateurs de requête spécialisés :

• TemporalAsOf: retourne des lignes actives (actuelles) à l’heure UTC donnée. Il s’agit d’une ligne unique de la table ou de la table d’historique actuelle pour une clé primaire donnée.
• TemporalAll: retourne toutes les lignes des données historiques. Il s’agit généralement de nombreuses lignes provenant de la table d’historique et/ou de la table actuelle pour une clé primaire donnée.
• TemporalFromTo: retourne toutes les lignes actives entre deux heures UTC données. Cela peut correspondre à de nombreuses lignes provenant de la table d’historique et/ou de la table actuelle pour une clé primaire donnée.
• TemporalBetween: identique à TemporalFromTo, sauf que les lignes devenues actives à la limite supérieure sont incluses.
• TemporalContainedIn: retourne toutes les lignes qui ont commencé à être actives et terminées étant actives entre deux heures UTC données. Cela peut correspondre à de nombreuses lignes provenant de la table d’historique et/ou de la table actuelle pour une clé primaire donnée.

Par exemple, après avoir mis à jour et supprimé certaines données, nous pouvons exécuter une requête avec TemporalAll pour voir les données historiques.

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

Notez comment la méthode EF.Property peut être utilisée pour accéder aux valeurs des colonnes de temps. Il est utilisé dans la OrderBy clause pour trier les données, puis dans une projection pour inclure ces valeurs dans les données retournées. Si les colonnes de période sont mappées aux propriétés CLR, vous pouvez les référencer directement dans la requête au lieu d’utiliser EF.Property.

Cette requête renvoie les données suivantes :

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

Notez que la dernière ligne renvoyée a cessé d’être active le 26/08/2021 à 16:44:59. Cela est dû au fait que la ligne de Rainbow Dash a été supprimée du tableau principal à ce moment précis. Nous verrons ultérieurement comment ces données peuvent être restaurées.

Des requêtes similaires peuvent être écrites à l’aide de TemporalFromTo, TemporalBetween ou TemporalContainedIn. Par exemple:

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

Cette requête retourne les lignes suivantes :

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

Restauration des données historiques

Comme mentionné ci-dessus, Rainbow Dash a été supprimé de la Employees table. C’était clairement une erreur, alors revenons à un point dans le temps et restaurez la ligne manquante à partir de ce temps.

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

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

Cette requête retourne une ligne unique pour Rainbow Dash, car elle était à l’heure UTC donnée. Toutes les requêtes utilisant des opérateurs temporels ne sont pas suivis par défaut. Par conséquent, l’entité retournée ici n’est pas suivie. Cela est logique, car il n’existe pas actuellement dans la table principale. Pour réinscrire l’entité dans la table principale, il suffit de la marquer comme Added puis d'appeler SaveChanges.

Après avoir réinscrit la ligne Rainbow Dash, l’interrogation des données historiques indique que la ligne a été restaurée au moment UTC donné :

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

Mappage des colonnes de période à des propriétés CLR

Par défaut, les colonnes de période d'une table temporelle sont mappées aux propriétés shadow dans le modèle EF Core, ce qui signifie qu'elles n'ont pas besoin d'exister sur votre type d'entité .NET. À partir d’EF Core 11, vous pouvez mapper des colonnes de période à des propriétés CLR régulières sur votre type d’entité, ce qui vous permet d’accéder directement à leurs valeurs.

Pour ce faire, ajoutez DateTime des propriétés pour le début et la fin de la période à votre type d’entité :

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

Configurez ensuite la table temporelle pour utiliser ces propriétés via une expression lambda :

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

Lorsque les colonnes de période sont mappées aux propriétés CLR, vous pouvez accéder directement à leurs valeurs sur l’entité au lieu d’utiliser 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();