Temporal tables – to not be combined up with momentary tables, also called system-versioned temporal tables preserve all of the adjustments with particular tables. This can be a function of SQL Server since SQL Server 2016. With out utilizing this function from SQL Server, with EF Core typically the habits when saving objects was overridden to maintain the information within the database. With updates from EF Core 6 and utilizing SQL Server (together with SQL Azure), only a few traces of code are required to allow temporal tables and use this function as proven with this text.
Configure Temporal Tables with EF Core
To configure a temporal desk, you simply must outline the desk mapping invoking the ToTable methodology, and invoke the TableBuilder methodology IsTemporal. The pattern code defines the mapping to the Books desk with the corresponding temporal desk.
Creating the database with this defintion, the PeriodStart and PeriodEnd columns are added to the Books desk, and the historical past desk BooksHistory is created containing the identical columns. The following two pictures present the SQL code for these tables.
The desk BooksHistory is ready to comprise all of the modified and deleted rows of the desk Books, with a price to specify the time vary when the Books report had this state. This desk doesn’t have a major key – the first key with the Books desk may have duplicates on this historical past desk.
With an overload of the
IsTemporalmethodology, the extra columns and the historical past desk might be personalized.HasPeriodStart,HasPeriodEnd, andUseHistoryTablecan be utilized for personalisation.
Including and updating data
With this context configuration in place, so as to add and replace data, nothing must be modified with how you’re employed with EF Core. Including a report, the PeriodStart column incorporates the present time. The PeriodEnd column incorporates the date and time worth 31/12/9999 23:59:59.
The top 12 months 9999 shouldn’t be a difficulty as the appliance almost definitely is not going to be used anymore – and I believe C# and .NET is not going to be used anymore on this 12 months. I’m not so positive about COBOL 😉 If .NET continues to be used at the moment, I’m 100% positive I don’t work on fixing purposes for the 12 months 10.000, so I’m full okay about this finish date chosen from the .NET crew.
As quickly as a report is up to date or deleted, info is written to the historical past desk. With an replace, the unique report with the identical PeriodStart worth however the precise time for PeriodEnd is written to the historical past desk. With the system-versioned Books desk, the information is up to date, in addition to the PeriodStart worth of the replace time.
Deleting a report, a report is added to the historical past desk in an analogous means, however it’s deleted from the system-versioned Books desk.
Retrieving Temporal Values
To learn data from earlier occasions, SQL Server extension strategies outlined with the SqlServerDbSetExtensions class are outlined: TemporalAsOf, TemporalFromTo, TemporalBetween, TemporalContainedIn, and TemporalAll.
With these strategies, a number of DateTime values might be handed with the arguments to outline the time vary to retrieve the values. The next code snippet reveals retrieving the interval begin worth of a report accessing a shadow property, and utilizing the TemporalAsOf methodology to get a earlier worth from the historical past desk.
The question translated to SQL makes use of the
FOR SYSTEM_TIME AS OF 'time'SQL sub-clause.
No monitoring for Temporal Objects
Utilizing the temporal question strategies, the objects materialized from the historical past desk aren’t tracked with the context. You don’t wish to change the historical past. The next code snippet will get all of the temporal Ebook objects from the historical past desk and reveals the entity state. As needs to be anticipated, the entities are Disconnected.
Knowledge Privateness and Consumer Info
So as to add the person who had the accountability of updating or including a report, you may add write details about the person to a different column. Most likely you don’t need the username to be a part of the .NET class, right here you may create a shadow property with the mannequin definition.
Relying on the information you retailer, after all you additionally want to ensure to satisfy the necessities of the Basic Knowledge Safety Regulation (GDPR).
Take away
Temporal tables (also called system-versioned temporal tables) are a function of SQL Server since SQL Server 2016, and with the .NET 6 updates simply accessible with EF Core. All what must be achieved to retailer historical past information with a desk is to outline the desk mapping with the IsTemporal methodology.
For querying the historic information, you need to use temporal APIs to specify the time when the information was legitimate.
Take pleasure in studying and programming!
Christian
Extra Info
Extra details about the EF Core is on the market in my new e-book and my workshops.
Skilled C# and .NET – 2021 Version
Get the pattern code from the e-book repo throughout the 5_MoreSamples folder pattern code.
Associated Info
Querying with system-versioned temporal tables
If you happen to’ve learn this far, contemplate shopping for me a espresso which helps the infrastructure I’m utilizing to write down articles.
