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 lot of overhead of managing history of data changes away from the developer. Historical data stored in a temporal table can be used to perform audits, restoring data to a particular timestamp in 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 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 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 using, 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

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

In order to work with EF core code first migrations and database updates, you can either use Package Manager Console in Visual Studio, or dotnet-cli tools for EF. I prefer to use the later. 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 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.cs as follows. In the OnConfiguring method, specify the connection string. Connection string should not be hardcoded in the code and neither should it be stored as plain text in configuration file. Consider secure storage solutions like Azure key vault for managing secrets like connection strings. In the OnModelCreating method we specify that Employee table should be created as 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 in that folder. If you look into those files, you will notice two additional datetime 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 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 EmployeesHistory table is created automatically. The schema of the EmployeesHistory table is same as that of Employees table.

Updating Data to Generate History

Let’s add some data to Employees table. Add following code to 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 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 maximum value of 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.

Let’s update couple of records. Let’s move Kurt from IT to Legal and Ervint from HR to IT. Add a new method in 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.

Let’s delete employee with id of 6 using the following method in 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.

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 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 end point 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 end point 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 end point 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 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 console, we can see that Ervint was with HR till 12/5/2021 1:58:25 PM (UTC time), post which he moved to IT.

If we want to restore the employee that was deleted in one of the previous steps, we first need to find the datetime 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 history table to main table. This is because Id is IDENTITY column in 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 audit trail of data changes for purpose of analytics, regulatory requirements, preventing accidental loss of data, ML and AI applications etc.

References

  • https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables?view=sql-server-ver15
  • https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew
  • https://devblogs.microsoft.com/dotnet/prime-your-flux-capacitor-sql-server-temporal-tables-in-ef-core-6-0/
  • https://youtu.be/_1fJeW4F3ts