Temporal tables in MSSQL 2016

The new version of MS SQL server offers a lot of new cool features. One of them is called system-versioned temporal tables.

As the name suggests, this feature enables versioning for data in table, it does quite simply by creating a second table that holds the historic state of each row, as well as information about the timespan in which the row was valid.

For more information see this great overview on MSDN.

The sad truth is that there is no support for versioned tables in EF 6, and no sings of it being prepared. For EF Core, there is at least an issue marked as enhancement so hopefully someone will jump on it and sooner or later we will be able to do: _context.Orders.AsOf(1.Months().Ago().LastDayOfMonth()).

The good thing is that, even though you can’t access the historic data through EF yet, you can use the versioning on the tables where EF stores your entities. This should be useful in systems where audits are not reviewed often, and so you can afford to query them through ad-hoc SQL, but still need to have the auditing in place.

Here is a great article on how to use versioning with EF.