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
IsTemporal. The pattern code defines the mapping to the
Books desk with the corresponding temporal desk.
Creating the database with this defintion, the
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.
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.
UseHistoryTablecan 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
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
Retrieving Temporal Values
To learn data from earlier occasions, SQL Server extension strategies outlined with the SqlServerDbSetExtensions class are outlined:
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).
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
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!
Extra details about the EF Core is on the market in my new e-book and my workshops.
Get the pattern code from the e-book repo throughout the 5_MoreSamples folder pattern code.
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.