Working With Temporal Tables In EF Core 6.0

Introduction

The concept of temporal tables was introduced in SQL Server 2016. Temporal tables are system-managed tables that are used for storing records of data changes, hence enabling analysis of table data at different points in time. As this is automatically managed by SQL Server, it takes a lot of overhead to manage the history of data changes away from the developer. Historical data stored in a temporal table can be used to perform audits, restore data to a particular timestamp in the past, view and analyze trends, etc.

When a table is created as a temporal table, SQL Server creates a corresponding history table for recording changes to the data in the original table along with their UTC timestamps. Whenever a record is updated in the main table, the previous value is copied to the history table before an update is committed to the main table. Similarly, whenever a record is deleted from the main table, the values are copied to the history table before deletion.

Entity Framework (EF) Core 6, which runs on .NET 6, supports working with SQL Server temporal tables. In this article, we will look into a simple example of how we can use EF 6 to create temporal tables using the Code First approach and how can we query the historical data stored in temporal tables using LINQ.

Setup

I will be using Visual Studio 2022 Preview for Mac for setup and execution. The concepts and steps mentioned in this article remain more or less the same regardless of the operating system. We will be using some new features introduced in C# 10 in our project like implicit use, file-scoped namespace declaration, etc. Let’s start by creating a console app in Visual Studio. Make sure you select .NET 6 as the target framework version

Framework

Add the following NuGet packages to the project,

Microsoft.EntityFrameworkCore.Design v 6.0.0
Microsoft.EntityFrameworkCore.SqlServer v 6.0.0
Microsoft.EntityFrameworkCore.Tools v 6.0.0

Nuget

In order to work with EF core code first migrations and database updates, you can either use Package Manager Console in Visual Studio or dot net-cli tools for EF. I prefer to use the latter. Install the dotnet CLI tools for EF by executing the following command in the terminal. Make sure you change the current directory to your project directory in the terminal before executing the command.

dotnet tool update --global dotnet-ef

Create Database Model and Context

To keep things simple, we will work with just one table entity – Employee. Add a new class called Employee. cs to the project. An employee entity has an ID, First Name, Last Name, and Department properties.

namespace EFCoreTemporalDemo;

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Department { get; set; }
}

Add a new class called CompanyContext. is as follows. In the OnConfiguring method, specify the connection string. The connection string should not be hardcoded in the code and neither should it be stored as plain text in the configuration file. Consider secure storage solutions like Azure key vault for managing secrets like connection strings. In the OnModelCreating method, we specify that the Employee table should be created as a temporal table that will have an associated history table which SQL Server will automatically create and manage.

using Microsoft.EntityFrameworkCore;
namespace EFCoreTemporalDemo;
public class CompanyContext: DbContext {
    public DbSet < Employee > Employees {
        get;
        set;
    }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        //Do not hard code connection string in code, or store it in plain text in config file
        //Consider options like Azure Key Vault for storing secrets like connection strings
        optionsBuilder.UseSqlServer(@ "<conn-string>");;
    }
    protected override void OnModelCreating(ModelBuilder modelBuilder) {
        modelBuilder.Entity < Employee > ().ToTable("Employees", e => e.IsTemporal());
    }
}

Run EF Core DB Migration and Update

Add database migration by executing the following CLI command. I have chosen to name my migration as TemporalInit. You can choose any name that you like.

dotnet ef migrations add TemporalInit

EF Core will create a Migrations folder inside your project and add migration files to that folder. If you look into those files, you will notice two additional date time columns that are added by EF core migration – PeriodStart and PeriodEnd. They will be added as hidden columns in SQL Server and are mapped to the entity as shadow properties.

Execute the following CLI command to update the database,

dotnet ef database update

Connect to SQL Server instance and notice the database is created with Employees table. Also, note that the EmployeesHistory table is created automatically. The schema of the EmployeesHistory table is the same as that of the Employees table.

Employees table

Updating Data to Generate History

Let’s add some data to the Employee's table. Add the following code to the Program.cs file. The InitDb method adds some employee data to the database. Build and execute the console app.

using Microsoft.EntityFrameworkCore;
namespace EFCoreTemporalDemo;
public class Program {
    public static void Main() {
        InitDb();
    }
    public static void InitDb() {
        Console.WriteLine("Initializing...");
        Employee hrEmp1 = new() {
            FirstName = "Leanney", LastName = "Graham", Department = "HR"
        };
        Employee hrEmp2 = new() {
            FirstName = "Ervint", LastName = "Howell", Department = "HR"
        };
        Employee legalEmp1 = new() {
            FirstName = "Clementine", LastName = "Baucho", Department = "Legal"
        };
        Employee itEmp1 = new() {
            FirstName = "Patriciari", LastName = "Lebsack", Department = "IT"
        };
        Employee itEmp2 = new() {
            FirstName = "Chelst", LastName = "Dietrich", Department = "IT"
        };
        Employee itEmp3 = new() {
            FirstName = "Kurt", LastName = "Weissnat", Department = "IT"
        };
        using
        var dbContext = new CompanyContext();
        dbContext.AddRange(hrEmp1, hrEmp2, legalEmp1, itEmp1, itEmp2, itEmp3);
        dbContext.SaveChanges();
        Console.WriteLine("Initialization done !");
    }
}

When you insert a new record in the table, the PeriodStart date is set to the UTC timestamp when the row is inserted and the PeriodEnd date is set to a maximum value of the datetime2 SQL type. No entries are made to the history table in case of insert operation. Note that since PeriodStart and PeriodEnd are hidden columns in SQL Server, they need to be specified explicitly in the SELECT clause in order to fetch them in the result set.

PeriodStart date

Let’s update a couple of records. Let’s move Kurt from IT to Legal and Ervint from HR to IT. Add a new method in the Program. cs to update the records as follows. Execute to commit the changes in the database.

using Microsoft.EntityFrameworkCore;
namespace EFCoreTemporalDemo;
public class Program {
    public static void Main() {
        UpdateDepartment(6, "Legal");
        UpdateDepartment(2, "IT");
    }
    public static void UpdateDepartment(int empId, string depName) {
        using
        var dbContext = new CompanyContext();
        Employee emp = dbContext.Employees.Find(empId);
        if (emp != null) {
            emp.Department = depName;
            dbContext.SaveChanges();
        }
    }
}

Notice the EmployeesHistory table automatically stores the previous version of the rows before the update is made in the main table.

EmployeesHistory table

Let’s delete an employee with an ID of 6 using the following method in the Program. cs.

using Microsoft.EntityFrameworkCore;
namespace EFCoreTemporalDemo;
public class Program {
    public static void Main() {
        DeleteEmployee(6);
    }
    public static void DeleteEmployee(int id) {
        using
        var dbContext = new CompanyContext();
        Employee emp = dbContext.Employees.Find(id);
        if (emp != null) {
            dbContext.Employees.Remove(emp);
            dbContext.SaveChanges();
        }
    }
}

The row is automatically copied to the history table before it is deleted from the main table.

History table

Querying the history table

You can use various EF LINQ extension methods to query the historical data. Their descriptions have been copied from Visual Studio Intellisense.

TemporalAsOf: returns elements that were present in the database at a given point in time.
TemporalAll: returns all historical versions of the entities as well as their current state.
TemporalFromTo: returns elements that were present in the database between two given points in time. Elements created at the starting point and removed at the endpoint are not included.
TemporalBetween: returns elements that were present in the database between two given points in time. Elements created at the starting point are not included, elements removed at the endpoint are included.
TemporalContainedIn: returns elements that were present in the database between two given points in time. Elements created at the starting point and removed at the endpoint are included.

For example, if you want to query all the changes that have been made for employee ID 2, we can use the following code that uses the TemporalAll LINQ extension method.

var history = dbContext.Employees.TemporalAll().Where(emp => emp.Id == 2)
              .OrderByDescending(emp => EF.Property < DateTime > (emp, "PeriodStart"))
              .Select(emp => new {
                Employee = emp,
                PeriodStart = EF.Property < DateTime > (emp, "PeriodStart"),
                PeriodEnd = EF.Property < DateTime > (emp, "PeriodEnd")
              }).ToList();

When we print the history object in the console, we can see that Ervint was with HR till 12/5/2021 at 1:58:25 PM (UTC time), post which he moved to IT.

Console

If we want to restore the employee that was deleted in one of the previous steps, we first need to find the date and time when the employee was deleted. We can then get the record from the history table using TemporalAsOf and move it to the main table. Note that I need to SET IDENTITY_INSERT before moving the record from the history table to the main table. This is because Id is the IDENTITY column in the Employees table.

public static void RestoreDeletedEmployee(int id) {
    using
    var dbContext = new CompanyContext();
    var delTimestamp = dbContext.Employees.TemporalAll().Where(emp => emp.Id == id).OrderBy(emp => EF.Property < DateTime > (emp, "PeriodEnd")).Select(emp => EF.Property < DateTime > (emp, "PeriodEnd")).Last();
    var delEmp = dbContext.Employees.TemporalAsOf(delTimestamp.AddMilliseconds(-1)).Single(emp => emp.Id == id);
    dbContext.Add(delEmp);
    SetIdentityInsert("dbo.Employees", true);
    dbContext.SaveChanges();
    SetIdentityInsert("dbo.Employees", false);
}
private static void SetIdentityInsert(string entityName, bool value) {
    using
    var dbContext = new CompanyContext();
    dbContext.Database.OpenConnection();
    if (value) {
        dbContext.Database.ExecuteSqlInterpolated($ "SET IDENTITY_INSERT {entityName} ON");
    } else {
        dbContext.Database.ExecuteSqlInterpolated($ "SET IDENTITY_INSERT {entityName} OFF");
    }
}

Temporal tables are very convenient and do a lot of heavy lifting out of the box. Introducing support for temporal tables in EF Core is a very welcome step. It can help developers save time and assist a great deal in building applications and databases that need to maintain an audit trail of data changes for the purpose of analytics, regulatory requirements, preventing accidental loss of data, ML and AI applications, etc.