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 TableBuilder method IsTemporal. The sample code defines the mapping to the Books table with the corresponding temporal table.
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.
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
IsTemporalmethod, the additional columns and the history table can be customized.HasPeriodStart,HasPeriodEnd, andUseHistoryTablecan 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.
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).
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
Get the sample code from the book repo within the 5_MoreSamples folder sample code.
Related Information
Querying with system-versioned temporal tables
Time Travel ID 194915 © Cecilia Lim | Dreamstime.com
csharp.christiannagel.com 
3 thoughts on “Temporal Tables with EF Core 6”