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.
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
IsTemporal. The sample code defines the mapping to the
Books table with the corresponding temporal table.
Creating the database with this defintion, the
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.
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
IsTemporalmethod, the additional columns and the history table can be customized.
UseHistoryTablecan 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
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
Retrieving Temporal Values
To read records from previous times, SQL Server extension methods defined with the SqlServerDbSetExtensions class are defined:
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.
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.
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).
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
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 about the EF Core is available in my new book and my workshops.
Get the sample code from the book repo within the 5_MoreSamples folder sample code.