Auditing with EF Core and Sql Server – Part 1: Temporal tables

It’s part of a lot of projects in which I’m involved. Keeping track of data that is changed, when the change occurred and, the most important part, who made the change. Apart from tracking down unauthorised changes it also helps when users claim they didn’t make a change. They tend to forget changes they made. It’s a recurring requirement, but most of the time it’s there without the need for any further features. I’ve never had to create a diff screen or the like. We just need to keep track of all the changes to be on the safe side.

Most of the time you will see this being implemented with an interface that has to be implemented by your entities. The IAuditable interface with ModifiedByUser and ModifiedDate. It’s not that hard to implement and there are plenty of examples out there. Entity Framework has all the info you need, you can even get access to the old and new values of the properties and log them as well.

A quick Google search shows you how others are doing it, like here or even a library that keeps track of it. It’s not that hard to roll your own implementation and you’ll learn more about EF in the process of implementing it.

I always consider it to be noise. It is a cross cutting concern showing up in all your entities and then there is the additional code in the dbcontext which needs to look at the changed entities and their properties. For my current project I wanted to look at it from a different angle and keep everything out of my entities and if possible the DbContext.

I’m using SQL Azure (or SQL 2016) and had read about temporal tables last year and it seemed this would be a perfect fit. SQL server can automatically insert the validity date of data and their value at the time in a history table.

When enabling it on a table you need to add two columns for the validity period and the rest is taken care of by SQL Server. After inserting one record in the Products table, it will contain one row and its history table will be empty.

If I then issue an update statement, I get one row in the products table and one in its history table.

So far so good, we get an automatic history log. I was concerned that if I would add a column to the source table I would have to create an update to the corresponding history table but that is also handled by SQL Server. Migrations would just work. There are some restrictions however, as cascading deletes are no longer possible and would need to be handled by the application. You can find more limitations here.

To enable temporal tables on all my entities I created a couple of extension methods and added a migration to my code base.

This is largely based on this blog post. I just adapted it to be a bit more generic. If I would create a migration for a new table I could just call the extension method and have it temporal in one go.

But there was still one missing piece. Who made the change? I have no property on my entities to store this information and thus no column in the table or history table to store that info. In one of the first versions of the article on the MSDN website that introduces temporal tables it was mentioned that you could track the user, but that reference has since been deleted. So custom code is necessary. I didn’t want to add the property to each of my entities so I used a new feature in EF core called shadow properties. Funny enough, the example on the MSDN site is about auditing.

So I created an override for the OnModelCreating and the SaveChanges and SaveChangesAsync method of the DbContext. So not completely honouring my initial goal to keep it out of my application code.

This worked, but one scenario is now missing in this solution. We know who created or updated a row, but with a delete we lose the value in the ModifiedBy property. The deleted row is copied from the source table to the history table. I considered the ultimate hack: create an instead of trigger on the table and issue an update and then a delete but luckily SQL server prevented me from even trying it. Instead of triggers are not possible with table that is temporal. I considered adding a separate table to keep track of who deleted a certain row, but this would again introduce more code than I wanted and clutter the codebase. I could introduce soft deletes so that a delete will become an update but that just opens another can of worms.

So I went back to the drawing board to find a better solution, which turned out to be a blast from the past combined with a new feature in SQL server 2016. But that’s for next time.