Tablas temporales de SQL Server o Azure SQL

Las tablas temporales de SQL Server realizan un seguimiento automático de todos los datos almacenados en una tabla, incluso después de actualizar o eliminar los datos. Esto se logra mediante la creación de una "tabla de historial" paralela en la que se almacenan los datos históricos con marcas de tiempo cada vez que se realiza un cambio en la tabla principal. Esto permite consultar datos históricos, como para auditorías, o restaurarlos, como para la recuperación después de una mutación o eliminación accidental.

EF Core admite:

• Creación de tablas temporales mediante migraciones de EF Core
• Transformación de tablas existentes en tablas temporales, de nuevo mediante migraciones
• Consulta de datos históricos
• Restauración de datos desde algún punto del pasado

Configuración de una tabla temporal

El generador de modelos se puede usar para configurar una tabla como temporal. Por ejemplo:

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

Al usar EF Core para crear la base de datos, la nueva tabla se configura como una tabla temporal con los valores predeterminados de SQL Server para las marcas de tiempo y la tabla de historial. Por ejemplo, considere un Employee tipo de entidad:

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 tabla temporal creada tiene el siguiente aspecto:

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

Observe que SQL Server crea dos columnas ocultas datetime2 denominadas PeriodEnd y PeriodStart. Estas "columnas de período" representan el intervalo de tiempo durante el que existían los datos de la fila. De forma predeterminada, estas columnas se asignan a las propiedades de sombra en el modelo de EF Core, lo que les permite usarlos en las consultas, como se muestra más adelante. A partir de EF Core 11, las columnas de período también se pueden asignar a propiedades CLR de tu tipo de entidad.

Observe también que se crea automáticamente una tabla de historial asociada denominada EmployeeHistory . Los nombres de las columnas de período y la tabla de historial se pueden cambiar con configuración adicional al generador de modelos. Por ejemplo:

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

Esto se refleja en la tabla creada por 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]))');

Uso de tablas temporales

La mayoría de las veces, las tablas temporales se usan igual que cualquier otra tabla. Es decir, SQL Server controla de forma transparente las columnas de período y los datos históricos para que la aplicación pueda omitirlos. Por ejemplo, las nuevas entidades se pueden guardar en la base de datos de la manera normal:

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

Estos datos se pueden consultar, actualizar y eliminar de la manera normal. Por ejemplo:

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

Además, después de una consulta de seguimiento normal, se puede acceder a los valores de las columnas de período de los datos actuales desde las entidades con seguimiento. Si las columnas de período se asignan a propiedades CLR, puede acceder a ellas directamente en la entidad; de lo contrario, use EF.Property para acceder a ellas en forma de propiedades de sombra. Por ejemplo:

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

Esto muestra:

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

Observe que la ValidTo columna (denominada PeriodEndde forma predeterminada ) contiene el datetime2 valor máximo. Esto siempre es el caso de las filas actuales de la tabla. Las ValidFrom columnas (de forma predeterminada denominadas PeriodStart) contienen la hora UTC a la que se insertó la fila.

Consulta de datos históricos

EF Core admite consultas que incluyen datos históricos a través de varios operadores de consulta especializados:

• TemporalAsOf: devuelve filas que estaban activas (actuales) a la hora UTC especificada. Se trata de una sola fila de la tabla o tabla de historial actual para una clave principal determinada.
• TemporalAll: devuelve todas las filas de los datos históricos. Suele ser muchas filas de la tabla de historial o de la tabla actual para una clave principal determinada.
• TemporalFromTo: devuelve todas las filas que estaban activas entre dos horas UTC dadas. Puede ser muchas filas de la tabla de historial o de la tabla actual para una clave principal determinada.
• TemporalBetween: igual que TemporalFromTo, excepto que se incluyen las filas que se activaron en el límite superior.
• TemporalContainedIn: devuelve todas las filas que comenzaron a estar activas y finalizaron siendo activas entre dos horas UTC dadas. Puede ser muchas filas de la tabla de historial o de la tabla actual para una clave principal determinada.

Por ejemplo, después de realizar algunas actualizaciones y eliminaciones en nuestros datos, podemos ejecutar una consulta mediante TemporalAll para ver los datos históricos:

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

Observe cómo el método EF.Property se puede usar para acceder a los valores de las columnas de período. Esto se usa en la OrderBy cláusula para ordenar los datos y, a continuación, en una proyección para incluir estos valores en los datos devueltos. Si las columnas de período se asignan a propiedades CLR, puede hacer referencia a ellas directamente en la consulta en lugar de usar EF.Property.

Esta consulta devuelve los siguientes datos:

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

Observe que la última fila devuelta dejó de estar activa a las 8/26/2021 4:44:59 p. m. Esto se debe a que la fila de Rainbow Dash se eliminó de la tabla principal en ese momento. Veremos más adelante cómo se pueden restaurar estos datos.

Las consultas similares se pueden escribir mediante TemporalFromTo, TemporalBetweeno TemporalContainedIn. Por ejemplo:

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

Esta consulta devuelve las siguientes filas:

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

Restauración de datos históricos

Como se mencionó anteriormente, Rainbow Dash se eliminó de la Employees tabla. Esto fue claramente un error, así que volvamos a un momento dado y restauremos la fila que falta desde ese momento.

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

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

Esta consulta devuelve una sola fila para Rainbow Dash tal como estaba a la hora UTC dada. Todas las consultas que usan operadores temporales no son de seguimiento de forma predeterminada, por lo que no se realiza un seguimiento de la entidad devuelta aquí. Esto tiene sentido, ya que actualmente no existe en la tabla principal. Para volver a insertar la entidad en la tabla principal, simplemente la marcamos como Added y, a continuación, llamamos a SaveChanges.

Después de volver a insertar la fila Rainbow Dash, consultar los datos históricos muestra que la fila se restauró tal como estaba a la hora UTC dada:

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

Asignación de columnas de período a propiedades CLR

De forma predeterminada, las columnas de período de una tabla temporal se asignan a propiedades sombra del modelo EF Core, lo que significa que no necesitan estar presentes en el tipo de entidad .NET. A partir de EF Core 11, puede mapear columnas de período a propiedades CLR convencionales en su tipo de entidad, lo que le permite acceder directamente a sus valores.

Para ello, agregue propiedades DateTime para el inicio y fin del período al tipo de entidad:

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

A continuación, configure la tabla temporal para usar estas propiedades mediante una expresión lambda:

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

Cuando las columnas de periodo se asignan a propiedades CLR, puede acceder a sus valores directamente en la entidad en lugar de usar 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();