Tabelas temporais SQL Server/SQL do Azure

As tabelas temporais do SQL Server controlam automaticamente todos os dados já armazenados em uma tabela, mesmo depois que esses dados forem atualizados ou excluídos. Isso é conseguido através da criação de uma "tabela de histórico" paralela na qual os dados históricos com carimbo de data/hora são armazenados sempre que uma alteração é feita na tabela principal. Isso permite que os dados históricos sejam consultados, como para auditoria, ou restaurados, como para recuperação após mutação ou exclusão acidental.

O EF Core suporta:

• A criação de tabelas temporais usando migrações do EF Core
• Transformação de tabelas existentes em tabelas temporais, novamente usando Migrações
• Consultando dados históricos
• Restaurando dados de algum ponto no passado

Configurando uma tabela temporal

O construtor de modelos pode ser usado para configurar uma tabela como temporal. Por exemplo:

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

Ao usar o EF Core para criar a base de dados, a nova tabela é configurada como uma tabela temporal com os padrões do SQL Server para os carimbos temporais e a tabela de histórico. Por exemplo, considere um tipo de Employee entidade:

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

A tabela temporal criada tem esta aparência:

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 o SQL Server cria duas colunas ocultas datetime2 chamadas PeriodEnd e PeriodStart. Essas "colunas de período" representam o intervalo de tempo durante o qual os dados na linha existiram. Por defeito, estas colunas são mapeadas para propriedades de sombra no modelo EF Core, permitindo que sejam usadas em consultas, conforme demonstrado a seguir. A partir do EF Core 11, as colunas de período também podem ser mapeadas para propriedades CLR no seu tipo de entidade.

Observe também que uma tabela de histórico associada chamada EmployeeHistory é criada automaticamente. Os nomes das colunas de período e da tabela de histórico podem ser alterados com configuração adicional para o construtor de modelos. Por exemplo:

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

Isso se reflete na tabela criada pelo 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]))');

Usando tabelas temporais

Na maioria das vezes, as tabelas temporais são usadas como qualquer outra tabela. Ou seja, as colunas de período e os dados históricos são tratados de forma transparente pelo SQL Server de forma que o aplicativo possa ignorá-los. Por exemplo, novas entidades podem ser salvas no banco de dados da maneira 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();

Esses dados podem ser consultados, atualizados e excluídos da maneira normal. Por exemplo:

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

Além disso, após uma consulta de acompanhamento normal, os valores das colunas de período dos dados atuais podem ser acessados a partir das entidades rastreadas. Se as colunas de período estiverem mapeadas para propriedades CLR, pode aceder diretamente a elas na entidade; caso contrário, usa EF.Property para aceder a elas como propriedades de sombra. Por exemplo:

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

Isto 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

Observe que a ValidTo coluna (por padrão chamada PeriodEnd) contém o datetime2 valor máximo. Este é sempre o caso das linhas atuais na tabela. As ValidFrom colunas (por padrão chamadas PeriodStart) contêm a hora UTC em que a linha foi inserida.

Consultando dados históricos

O EF Core suporta consultas que incluem dados históricos através de vários operadores de consulta especializados:

• TemporalAsOf: Retorna linhas que estavam ativas no tempo UTC especificado. Esta é uma única linha da tabela atual ou tabela de histórico para uma determinada chave primária.
• TemporalAll: Retorna todas as linhas nos dados históricos. Normalmente, são muitas linhas da tabela de histórico e/ou da tabela atual para uma determinada chave primária.
• TemporalFromTo: Devolve todas as linhas que estavam ativas entre duas determinadas horas UTC. Isso pode ser muitas linhas da tabela de histórico e/ou da tabela atual para uma determinada chave primária.
• TemporalBetween: O mesmo que TemporalFromTo, exceto que as linhas que se tornaram ativas no limite superior são incluídas.
• TemporalContainedIn: Devolve todas as linhas que começaram a estar ativas e que acabaram por estar ativas entre duas determinadas horas UTC. Isso pode ser muitas linhas da tabela de histórico e/ou da tabela atual para uma determinada chave primária.

Por exemplo, depois de fazer algumas atualizações e exclusões em nossos dados, podemos executar uma consulta usando TemporalAll para ver os dados 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 como o EF.Property pode ser usado para aceder a valores das colunas de período. Isso é usado na OrderBy cláusula para classificar os dados e, em seguida, em uma projeção para incluir esses valores nos dados retornados. Se as colunas de período estiverem mapeadas para propriedades CLR, pode referencia-las diretamente na consulta em vez de usar EF.Property.

Esta consulta traz de volta os seguintes dados:

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 a última linha retornada deixou de estar ativa em 26/08/2021 16:44:59. Isso ocorre porque a linha do Rainbow Dash foi excluída da tabela principal naquele momento. Veremos mais adiante como esses dados podem ser restaurados.

Consultas semelhantes podem ser escritas usando TemporalFromTo, TemporalBetweenou TemporalContainedIn. Por exemplo:

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 devolve as seguintes linhas:

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

Restaurando dados históricos

Como mencionado acima, Rainbow Dash foi excluído da Employees tabela. Isso foi claramente um erro, então vamos voltar a um momento específico no tempo para restaurar a linha que faltava daquele momento.

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

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

Esta consulta devolve uma única linha para Rainbow Dash como estava no horário UTC fornecido. Todas as consultas que usam operadores temporais não são rastreadas por padrão, portanto, a entidade retornada aqui não é rastreada. Isso faz sentido, porque atualmente não existe na tabela principal. Para reinserir a entidade na tabela principal, basta marcá-la como Added e, em seguida, chamar SaveChanges.

Depois de reinserir a linha Rainbow Dash, consultar os dados históricos mostra que a linha foi restaurada como estava no horário UTC fornecido:

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

Mapear colunas de período para propriedades CLR

Por defeito, as colunas de período numa tabela temporal são mapeadas para propriedades sombra no modelo EF Core, o que significa que não precisam de existir no seu tipo de entidade .NET. A partir do EF Core 11, pode, em vez disso, mapear colunas de período para propriedades normais de CLR no seu tipo de entidade, o que lhe permite aceder diretamente aos seus valores.

Para isso, adicione DateTime propriedades para o início e fim do período ao seu tipo de entidade:

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

Depois, configure a tabela temporal para usar estas propriedades através de uma expressão lambda:

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

Quando as colunas de período são mapeadas para propriedades CLR, pode aceder diretamente aos seus valores na entidade em vez 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();