Member-only story
How to use Historical Data with Temporal Tables in EF Core 6
Store, Query and Restore Historical Data directly using SQL Server and Entity Framework Core
Time is the ever present dimension not only in our lives, but often times in our applications too. We store timestamped events in our database, schedule actions to be executed at a specific point in the future, or even keep track of a customer’s changing shipping address.
The world changes over time, and therefore, so does our data. Often times, it makes sense to keep track of this shifting world by keeping track of the historical state of records in our database. Instead of storing a singular truth, we may store a record that was valid from a specific point in time, until another point in time, in an unknown future.
Keeping track of shifting dimensions has traditionally been a responsibility of application code. You’d have to add at least the aforementioned timestamps to your entities, write some application code that utilizes these timestamps, and off you go.
Little known fact is that SQL Server has been supporting what is known as Temporal Tables ever since the release of SQL Server 2016, and storing this type of temporary state is exactly what it’s meant for.
