EF Core 6.0 - Usando tabelas Temporais
 Hoje veremos o que é e como trabalhar com o novo recurso Tabelas Temporal no EF Core 6.0.

O conceito de tabelas temporais foi introduzido no SQL Server 2016. As tabelas temporais são tabelas gerenciadas pelo sistema usadas para armazenar registros de alterações de dados, permitindo a análise de dados de tabela em diferentes momentos.

Os dados históricos armazenados em uma tabela temporal podem ser usados ​​para realizar auditorias, restaurar dados para um carimbo de data/hora específico no passado, visualizar e analisar tendências, etc.

Quando uma tabela é criada como uma tabela temporal, o SQL Server cria uma tabela de histórico correspondente para registrar as alterações nos dados da tabela original junto com seus carimbos de data/hora UTC. Sempre que um registro é atualizado na tabela principal, o valor anterior é copiado para a tabela de histórico antes que uma atualização seja confirmada na tabela principal. Da mesma forma, sempre que um registro é excluído da tabela principal, os valores são copiados para a tabela de histórico antes da exclusão.

A partir do .NET 6, o EF Core 6 dá suporte ao trabalho com tabelas temporais do SQL Server e neste artigo veremos um exemplo simples de como podemos usar o EF Core 6 para criar tabelas temporais usando a abordagem Code First e como podemos consultar os dados históricos armazenados em tabelas temporais usando LINQ.

Recursos usados:

Criando o projeto

Vamos criar uma aplicação Console no VS 2022 chamada EFCoreTemporal;

A seguir vamos incluir no projeto os seguintes pacotes:

Para isso abra uma janela - Package Manager Console - no VS 2022 via menu Tools e emita o comando :

install-package <nome_do_pacote>

Ao final o arquivo de projeto da aplicação deverá estar assim:

Para trabalhar com as migrações usando a abordagem Code-First do EF podemos usar o Package Manager Console no Visual Studio ou as ferramentas dotnet-cli para EF.

Eu vou trabalhar usando a janela do Package Manager Console no VS 2022, mas se você preferir usar a ferramenta de linha de comando lembre-se de alterar o diretório atual para o diretório do projeto antes de executar o comando, e, também certifique-se de instalar a ferramenta do EF core para realizar esta tarefa usando o comando abaixo:

dotnet tool update --global dotnet-ef

Para simplificar o exemplo vamos criar uma pasta Data e nesta pasta vamos criar a classe Cliente no projeto com o código a seguir:

public class Cliente
{
        public int Id { get; set; }
        public string? Nome { get; set; }
        public string? Email { get; set; }
        public string? Telefone { get; set; }
}

A seguir vamos criar a classe ClienteContext na pasta Data que herda de DbContext e que representa a sessão com o banco de dados.

using Microsoft.EntityFrameworkCore;

namespace EFCoreTemporal.Data
{
    public class ClienteContext : DbContext
    {
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var defaultConnection = @"sua string de conexão";
            optionsBuilder.UseSqlServer(defaultConnection);
        }
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Cliente>().ToTable("Clientes", e => e.IsTemporal());
        }
        public DbSet<Cliente>? Clientes { get; set; }
    }
}

A string de conexão do meu ambiente local vai criar o banco DemoDB conforme abaixo:

"Data Source=<minha_instância>;Initial Catalog=DemoDB;Integrated Security=True";

No método OnConfiguring, definimos a string de conexão. Lembrando que, em um ambiente de produção, a string de conexão não deve ser definida no código e também não deve ser armazenada como texto simples no arquivo de configuração. Considere soluções de armazenamento seguro, como o cofre de chaves do Azure, para gerenciar segredos como cadeias de conexão.

No método OnModelCreating especificamos que a tabela Cliente deve ser criada como tabela temporal que terá uma tabela de histórico associada que o SQL Server criará e gerenciará automaticamente.

Poderíamos ter definindo mais detalhes na definição da tabela temporal :

 protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
            modelBuilder.Entity<Cliente>().ToTable("Clientes", e => e.IsTemporal(t=>
             {
                   t.HasPeriodStart("InicioValidade");
                   t.HasPeriodEnd("TerminoValidade");
                   t.UseHistoryTabele("ClientesHistorico");
             }));
   }

Com este código ao invés de usar os nomes padrão estamos atribuindo os nomes para os campos que serão criados e para  a tabela de histórico. (Eu não vou usar esta definição)

Vamos agora aplicar a migração para criar o banco e a tabela Cliente no SQL Server. Para isso na janela do Package Manager Console no VS 2022 digite os comandos :

1- Add-Migration Inicial

Este comando cria um arquivo de scripts chamado Inicial que herda de Migration e contém os comandos para criar o banco e a tabela.

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace EFCoreTemporal.Migrations
{
    public partial class Inicial : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "Clientes",
                columns: table => new
                {
                    Id = table.Column<int>(type: "int", nullable: false)
                        .Annotation("SqlServer:Identity", "1, 1"),
                    Nome = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    Email = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    Telefone = table.Column<string>(type: "nvarchar(max)", nullable: true),
                    PeriodEnd = table.Column<DateTime>(type: "datetime2", nullable: false)
                        .Annotation("SqlServer:IsTemporal", true)
                        .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                        .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart"),
                    PeriodStart = table.Column<DateTime>(type: "datetime2", nullable: false)
                        .Annotation("SqlServer:IsTemporal", true)
                        .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                        .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart")
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_Clientes", x => x.Id);
                })
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "ClientesHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropTable(
                name: "Clientes")
                .Annotation("SqlServer:IsTemporal", true)
                .Annotation("SqlServer:TemporalHistoryTableName", "ClientesHistory")
                .Annotation("SqlServer:TemporalHistoryTableSchema", null)
                .Annotation("SqlServer:TemporalPeriodEndColumnName", "PeriodEnd")
                .Annotation("SqlServer:TemporalPeriodStartColumnName", "PeriodStart");
        }
    }
}

Observe que no código gerado temos duas colunas de data e hora adicionais que são adicionadas pela migração do EF Core – PeriodStart e PeriodEnd. Eles serão adicionados como colunas ocultas no SQL Server e são mapeados para a entidade como propriedades de sombra ou Shadow Property. Será criada também a tabela ClientesHistory.

Para aplicar este script de migração vamos emitir agora o comando :

1- Update-Database

Vamos abrir o SQL Server Management Studio e verificar o banco DemoDB e a tabela Cliente gerada.

Observe que foram criadas duas tabelas, a tabela ClientesHistory e a tabela Clientes, e que o esquema da tabela ClientesHistory é o mesmo da tabela Clientes.

Atualizando dados para gerar histórico

Vamos adicionar alguns dados à tabela Clientes.

Inclua o código abaixo no arquivo Program para incluir dados de alguns clientes:

using EFCoreTemporal.Data;

InicializaDadosDB();

Console.ReadKey();

static void InicializaDadosDB()
{
    Console.WriteLine("Inicializando...");
    Cliente cli1 = new()
    {
        Nome = "Leonardo",
        Email = "leonardo@email.com",
        Telefone = "11-9805-9633"
    };
    Cliente cli2 = new()
    {
        Nome = "Ermínio",
        Email = "erminio@email.com",
        Telefone = "21-9856-0021"
    };
    Cliente cli3 = new()
    {
        Nome = "Clementina",
        Email = "clementina@email.com",
        Telefone = "11-8549-9955"
    };
    Cliente cli4 = new()
    {
        Nome = "Patricia",
        Email = "patri@email.com",
        Telefone = "31-7895-9922"
    };
    Cliente cli5 = new()
    {
        Nome = "Carlos",
        Email = "carlos@email.com",
        Telefone = "21-9852-9988"
    };
    using
    var dbContext = new ClienteContext();
    dbContext.AddRange(cli1, cli2, cli3, cli4, cli5);
    dbContext.SaveChanges();
    Console.WriteLine("Inicialização concluída !");
}

Vamos executar o programa e seguir analisar o resultado abrindo o SQL Server Management Studio e emitindo os seguintes comandos SQL em uma nova janela de consulta:

Quando inserimos um novo registro na tabela, a data PeriodStart é definida como o carimbo de data/hora UTC quando a linha é inserida e a data PeriodEnd é definida como o valor máximo do tipo SQL datetime2.

Nenhuma entrada é feita na tabela de histórico em caso de operação de inserção. Observe que, como PeriodStart e PeriodEnd são colunas ocultas no SQL Server, elas precisam ser especificadas explicitamente na cláusula SELECT para buscá-las no conjunto de resultados.

Vamos atualizar alguns registros da tabela criando o método AtualizaTelefone() e vamos atualizar o telefone dos clientes com Id igual a 1 e 5 :

using EFCoreTemporal.Data;

AtualizaTelefone(1, "41-9999-9999");
AtualizaTelefone(5, "51-8888-8888");

Console.ReadKey();

static void AtualizaTelefone(int clienteId, string telefone)
{
    using
    var dbContext = new ClienteContext();
    Cliente cliente = dbContext.Clientes.Find(clienteId);
    if (cliente != null)
    {
        cli.Telefone = telefone;
        dbContext.SaveChanges();
    }
}

Após executar o programa vamos abrir novamente o SQL Server Management Studio e repetir os comandos Select:

Note que a tabela clientesHistory agora apresenta o registro da alteração.

Para concluir vamos deletar o registro de número 4 usando o método DeletaCliente(int id) :

using EFCoreTemporal.Data;

DeletaCliente(4);

Console.ReadKey();

static void DeletaCliente(int id)
{
    using
   var dbContext = new ClienteContext();
    Cliente cliente = dbContext.Clientes.Find(id);
    if (cliente != null)
    {
        dbContext.Clientes.Remove(cliente);
        dbContext.SaveChanges();
    }
}

Consultando a tabela de histórico
Agora podemos usar métodos de extensão da LINQ para consultar os dados históricos. Para isso vamos usar os seguintes métodos:

Por exemplo, se você quiser consultar todas as alterações que foram feitas para o Cliente com Id igual a 2, podemos usar o código a seguir que usa o método de extensão TemporalAll :

using EFCoreTemporal.Data;
using Microsoft.EntityFrameworkCore;

ConsultaHistorico(2);

Console.ReadKey();

static void ConsultaHistorico(int id)
{
    using var dbContext = new ClienteContext();
    var historico = dbContext.Clientes.TemporalAll().Where(emp => emp.Id == id)
                  .OrderByDescending(emp => EF.Property<DateTime>(emp, "PeriodStart"))
                  .Select(cli => new {
                      Cliente = cli,
                      PeriodStart = EF.Property<DateTime>(cli, "PeriodStart"),
                      PeriodEnd = EF.Property<DateTime>(cli, "PeriodEnd")
                  }).ToList();

  foreach (var reg in historico)
  {
     Console.WriteLine($"{reg.Cliente.Id} - {reg.Cliente.Nome} - {reg.PeriodStart} - {reg.PeriodEnd}");
   }
}

Obteremos o seguinte resultado :

Se quisermos restaurar o cliente que foi excluído em uma das etapas anteriores, primeiro precisamos encontrar a data e hora em que o cliente foi excluído.

Podemos então obter o registro da tabela de histórico usando TemporalAsOf e movê-lo para a tabela principal. Observe que preciso definir SET IDENTITY_INSERT antes de mover o registro da tabela de histórico para a tabela principal. Isso ocorre porque Id é a coluna IDENTITY na tabela Clientes. 

using EFCoreTemporal.Data;
using Microsoft.EntityFrameworkCore;

RestaurarClienteDeletado(4);

Console.ReadKey();

static void RestaurarClienteDeletado(int id)
{
    using var dbContext = new ClienteContext();

    var delTimestamp = dbContext.Clientes.TemporalAll().Where(emp => emp.Id == id)
                                    .OrderBy(emp => EF.Property<DateTime>(emp, "PeriodEnd"))
                                    .Select(emp => EF.Property<DateTime>(emp, "PeriodEnd")).Last();

    var clienteDeletado = dbContext.Clientes.TemporalAsOf(delTimestamp.AddMilliseconds(-1))
                                      .Single(emp => emp.Id == id);

    dbContext.Add(clienteDeletado );
    SetIdentityInsert("dbo.Clientes", true);
    dbContext.SaveChanges();
    SetIdentityInsert("dbo.Clientes", false);
}
static void SetIdentityInsert(string nomeEntidade, bool valor)
{
    using var dbContext = new ClienteContext();
    dbContext.Database.OpenConnection();
    if (valor)
    {
        dbContext.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT {nomeEntidade} ON");
    }
    else
    {
        dbContext.Database.ExecuteSqlInterpolated($"SET IDENTITY_INSERT {nomeEntidade} OFF");
    }
}

Dessa forma a o novo recurso  tabelas temporais no EF Core é um recurso que pode ajudar os desenvolvedores a economizar tempo e auxiliar muito na criação de aplicativos de bancos de dados que precisam manter trilhas de auditoria de alterações de dados para fins de análise, requisitos regulatórios, prevenção de perda acidental de dados, aplicativos de ML e IA etc.

Pegue o código exemplo aqui :   EFCoreTemporal.zip (sem as referências)

"Bendito seja o Deus e Pai de nosso Senhor Jesus Cristo, Pai das misericórdias e Deus de toda consolação,
que nos consola em todas as nossas tribulações, para que, com a consolação que recebemos de Deus, possamos consolar os que estão passando por tribulações."
2 Coríntios 1:3,4

Referências:

José Carlos Macoratti