Setting audit columns in Entity Framework Core when using the database first approach

It’s a very common use case for applications to have to track when records are created or modified and which user was responsible.

The steps below show how we can easily add createdBy, createdDate, changedBy and changedDate columns to our DB and populate them using Entity Framework Core.

Add audit columns to your tables

Adding the relevant columns to your tables is easy BUT if you have a lot tables it’s a bit of a pain.

I found the below script on Stack Exchange the other day which helps create the ALTER statements to add a column to any table which does not already have it.

In the below example the output shows that the created_date column is missing from three tables…

Add a new columns to all tables that don't have it currently

Create an interface with C# properties to match DB columns

Interface with audit related properties

We’ll use this interface to mark entities as having these audit related properties so we can have Entity Framework Core set them later on…

Re-Scaffold and have all your classes implement the new interface

We need to re-scaffold to bring our new DB columns into our models. After this we could manually add : IHasAuditProperties in each EF generated model class BUT this means as soon as we scaffold again we will lose these custom changes.

A better option is to use partial classes. Using the below as an example C# will combine the manually created Address partial class in the ExtendedModels folder with the partial class Entity Framework Core has generated in the Models folder. When we need to re-scaffold, our partial classes will remain so we won’t lose our changes.

Use partial classes to implement an interface

The partial class approach above is nice, but if you have a lot of models it can be time consuming. Another approach is to change the format of the generated models which are output during scaffolding. We can do this easily using the excellent EF Core Power Tools extension.

When using this tool, we will scaffold using a graphical rather than a command line interface. One of the options in the tool is to customise the generated code using Handlebar templates. This gives us the ability to have all our entities implement an interface or inherit from a base class…

Inherit from a base class

For full steps involved see my previous article…

Make entities inherit from a base class in Entity Framework Core DB First – Dave Callan

Override SaveChanges() to set the values for audit related properties.

Finally we can set the audit properties by Overriding SaveChanges/SaveChangesAsych in our static DbContext class…

Override SaveChanges

If we place the above in our scaffolded DbContext we will lose these customisations when we next scaffold. Therefore we should put them in a statically created DbContext class which inherits from the generated one….

Override DbContext

and our Startup.cs -> ConfigureServices method would look something like below….

ConfigureServices

In our code we use instances of StaticContext and not EFContext…

Products Controller

Leave a Reply

Your email address will not be published. Required fields are marked *