Temporal Tables in .NET with Entity Framework Core

Temporal tables are a powerful feature in SQL Server that allow you to track historical data changes over time. This post will guide you through the process of setting up and using temporal tables in a .NET application using Entity Framework Core.

What Are Temporal Tables?

Temporal tables automatically keep track of all changes made to the data in a table, including inserts, updates, and deletes. They provide the ability to query data as it existed at a specific point in time or over a range of time. This is particularly useful for auditing, compliance, data recovery, and change data capture.

Setting Up Temporal Tables in Entity Framework Core

Let’s walk through a complete example of setting up a temporal table in a .NET application using Entity Framework Core.

Step 1: Define the Order Class

First, we define the Order class, which represents the data we want to track.

public class Order
{
    public int OrderId { get; set; } // Primary key
    public Guid CustomerId { get; set; }
    public string Status { get; set; }
    public string ProductName { get; set; }
    public DateTime CreatedOnUtc { get; set; }
    public DateTime SubmittedOnUtc { get; set; }
    public double Amount { get; set; }
}

Step 2: Define the OrderContext Class

Next, we define the OrderContext class, which sets up the temporal table in the database.

using Microsoft.EntityFrameworkCore;

public class OrderContext : DbContext
{
    public DbSet<Order> Orders { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Order>(entity =>
        {
            entity.ToTable("Orders", tb => tb.IsTemporal());
        });
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer("Your SQL server connection string");// server=VOSTRO202301;database=OrdersDatabase; Integrated Security=True; TrustServerCertificate=True;
        base.OnConfiguring(optionsBuilder);
    }
}

Add a Migration and Update the Database

dotnet ef migrations add intialUpdate
dotnet ef database update

SQL Table

Step 3: Querying the Temporal Table

Once the table is set up, you can query it to see the state of the data at any point in time. Here’s an example of how to query the temporal table:

using (OrderContext context = new OrderContext())
{
    DateTime fromTime = new DateTime(2024, 8, 8);
    DateTime toTime = new DateTime(2024, 12, 31);
    Guid customerID = new Guid("431C59D9-AAF1-46F2-9F6A-C7AFDEAC9C9D"); // Customer ID

    var totalOrderByCustomer = context.Set<Order>()
        .TemporalBetween(fromTime, toTime)
        .Where(order => order.CustomerId == customerID)
        .ToList();

    foreach (var order in totalOrderByCustomer)
    {
        Console.WriteLine($"OrderId:{order.OrderId} \t Product:{order.ProductName} \t Status:{order.Status} \t Amount:{order.Amount}");
    }
}

Output:

OrderId:7   Product:IPhone 11   Status:Pending   Amount:100
OrderId:10   Product:Product 3   Status:Cancelled   Amount:50
OrderId:11   Product:Touch Pad   Status:Cancelled   Amount:30
OrderId:12   Product:Apple Watch   Status:Cancelled   Amount:150

Conclusion

Temporal tables in SQL Server, combined with Entity Framework Core, provide a robust solution for tracking historical data changes. They simplify the process of auditing, compliance, data recovery, and change data capture. By following the steps outlined in this post, you can easily set up and use temporal tables in your .NET applications.

If you found this post helpful, please share it and follow me for more insights on .NET and Entity Framework Core.

Happy coding!