Temporal Tables with EF Core 6

Temporal tables – not to be mixed up with temporary tables, also known as system-versioned temporal tables keep all the changes with specific tables. This is a feature of SQL Server since SQL Server 2016. Without using this feature from SQL Server, with EF Core often the behavior when saving objects was overridden to keep the data in the database. With updates from EF Core 6 and using SQL Server (including SQL Azure), just a few lines of code are required to enable temporal tables and use this feature as shown with this article.

Time Travel

Configure Temporal Tables with EF Core

To configure a temporal table, you just need to define the table mapping invoking the ToTable method, and invoke the TableBuilder method IsTemporal. The sample code defines the mapping to the Books table with the corresponding temporal table.

Temporal model definition

Creating the database with this defintion, the PeriodStart and PeriodEnd columns are added to the Books table, and the history table BooksHistory is created containing the same columns. The next two images show the SQL code for these tables.

Create System-versioned table

System-versioned table

The table BooksHistory is prepared to contain all the changed and deleted rows of the table Books, with a value to specify the time range when the Books record had this state. This table doesn’t have a primary key – the primary key with the Books table will have duplicates in this history table.

With an overload of the IsTemporal method, the additional columns and the history table can be customized. HasPeriodStart, HasPeriodEnd, and UseHistoryTable can be used for customization.

Adding and updating records

With this context configuration in place, to add and update records, nothing needs to be changed with how you work with EF Core. Adding a record, the PeriodStart column contains the current time. The PeriodEnd column contains the date and time value 31/12/9999 23:59:59.

The end year 9999 shouldn’t be an issue as the application most likely will not be used anymore – and I think C# and .NET will not be used anymore in this year. I’m not so sure about COBOL 😉 If .NET is still used at that time, I’m 100% sure I don’t work on fixing applications for the year 10.000, so I’m full ok about this end date chosen from the .NET team.

As soon as a record is updated or deleted, information is written to the history table. With an update, the original record with the same PeriodStart value but the actual time for PeriodEnd is written to the history table. With the system-versioned Books table, the data is updated, as well as the PeriodStart value of the update time.

Deleting a record, a record is added to the history table in a similar way, but it is deleted from the system-versioned Books table.

Retrieving Temporal Values

To read records from previous times, SQL Server extension methods defined with the SqlServerDbSetExtensions class are defined: TemporalAsOf, TemporalFromTo, TemporalBetween, TemporalContainedIn, and TemporalAll.

With these methods, one or more DateTime values can be passed with the arguments to define the time range to retrieve the values. The following code snippet shows retrieving the period start value of a record accessing a shadow property, and using the TemporalAsOf method to get a previous value from the history table.

Query temporal data

The query translated to SQL uses the FOR SYSTEM_TIME AS OF 'time' SQL sub-clause.

No tracking for Temporal Objects

Using the temporal query methods, the objects materialized from the history table are not tracked with the context. You don’t want to change the history. The following code snippet gets all the temporal Book objects from the history table and shows the entity state. As should be expected, the entities are Disconnected.

Access entity state with temporal query

Data Privacy and User Information

To add the user who had the responsibility of updating or adding a record, you can add write information about the user to another column. Probably you don’t want the username to be part of the .NET class, here you can create a shadow property with the model definition.

Depending on the data you store, of course you also need to make sure to fulfill the requirements of the General Data Protection Regulation (GDPR).

Take away

Temporal tables (also known as system-versioned temporal tables) are a feature of SQL Server since SQL Server 2016, and with the .NET 6 updates easily accessible with EF Core. All what needs to be done to store history data with a table is to define the table mapping with the IsTemporal method.

For querying the historical data, you can use temporal APIs to specify the time when the data was valid.

Enjoy learning and programming! Christian

More Information

More information about the EF Core is available in my new book and my workshops.

Professional C# and .NET – 2021 Edition

Trainings

Get the sample code from the book repo within the 5_MoreSamples folder sample code.

Related Information

Querying with system-versioned temporal tables

Temporal Tables with Azure SQL Database

EF Core 6.0 What’s New

If you’ve read this far, consider buying me a coffee which supports the infrastructure I’m using to write articles. Buy Me A Coffee

3 thoughts on “Temporal Tables with EF Core 6

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.