EF Core 6.0: Introducing Support for SQL Server Temporal Tables

In this post, let's have a look at one of the nicest features coming with EF Core 6.0 (Actually I shouldn't be saying it's coming, it's already available. As of the day I am writing this post, we now have EF Core 6.0 RC2, support for SQL Server Temporal Tables was initially introduced with EF Core 6.0 RC1). It's one of the Highly requested features in EF Core 6.0.

Consider I have the following Entity.

public class Category
{
    public int Id { get; set; }
 
    public string Name { get; set; }
 
    public string Description { get; set; }
}

We can enable Temporal Tables through the ModelBuilder or IEntityTypeConfiguration<TEntity> (these are just things you already know and have been there forever). For simplicity, I will just use the ModelBuilder.

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<Category>(builder =>
    {
        builder
            .ToTable(x => x.IsTemporal());
    });
}

If we add in a migration for this, we will get something like this.

Create Table with Temporal Table Support

Here I am just creating a Category table with Temporal Table support from the first place. Oftentimes, we will already have the entities which we need to enable Temporal Table support. It's possible as well, just update the table with IsTemporal() .

Alter Table to support Temporal Tables

You can see the Period Columns and History table has default names. If you want, you can override those through TemporalTableBuilder as follows.

builder
    .ToTable(x => x.IsTemporal(x =>
        {
            x.HasPeriodStart("PeriodFrom");
            x.HasPeriodEnd("PeriodTo");
            x.UseHistoryTable($"{nameof(Category)}HistoricalData");
        })
    );

For simplicity, I am going to use the defaults.

And once the migration is applied, you can see your table now has Temporal Table support.

EF Core 6.0, also provides support for querying historical data which personally I am finding very much helpful.

Consider the following.

Category category = new() { Name = "Category A", Description = "Category A Description" };
await context.Categories.AddAsync(category);
await context.SaveChangesAsync();
 
await Task.Delay(5000);
 
category.Description = "Category A Description Updated";
context.Categories.Update(category);
await context.SaveChangesAsync();

Here I am just creating a new Category and then after 5 seconds, I am just doing an update to the Category Description.

Now I can query the history data as follows.

var categoryHistories = await context.Categories
    .TemporalAll()
    .Where(x => x.Id == category.Id)
    .OrderBy(x => EF.Property<DateTime>(x, "PeriodStart"))
    .Select(x => new
    {
        Employee = x,
        PeriodStart = EF.Property<DateTime>(x, "PeriodStart"),
        PeriodEnd = EF.Property<DateTime>(x, "PeriodEnd")
    })
    .ToListAsync();
 
foreach (var item in categoryHistories)
{
    Console.WriteLine($"Description: {item.Employee.Description}, " + $"Start: {item.PeriodStart} - End: {item.PeriodEnd}");
}

This would give an output something like the following.

Querying Historical Data

The important part here is the TemporalAll() method. We have a couple of options there.

• TemporalAll
• Returns all rows in the historical data.
• TemporalAsOf
• Returns a single row that was active (current) at the given UTC time
• TemporalFromTo
• Returns all rows that were active between two given UTC times
• TemporalBetween
• The same as TemporalFromTo, except that rows are included that became active on the upper boundary
• TemporalContainedIn
• Returns all rows that started being active and ended being active between two given UTC times.

Hope this helps.

Happy Coding.

Regards,

Jaliya