If you prefer videos, I got this entry in video format: https://youtu.be/eLH566y6wdw

Github repo: https://github.com/gavilanch/AuditByColumns/tree/master/eng/AuditEFCore

Let’s see how to create an audit by columns system for our applications that use Entity Framework Core. The idea is that we want each time a record is created or updated in our database, the time of the operation and the user who performed the operation is saved.

We call this audit by columns, because in this kind of audit each table will have columns that indicate the user who created the record, and the last user who modified a record, in addition to indicating the dates on which these operations occurred. This is different from a table audit where we have a specialized audit table where a history of the changes of each record of each table is kept.

With Entity Framework Core we can automate this audit by columns, so you don’t have to go coding to place the user who has performed the operation in each place where you update or insert a record, but we can centralize this operation in one place, so that it always works.

We are going to do this example in an ASP.NET Core MVC application with EF Core 2.2, with its user system. However, what we are going to learn applies to all types of .NET applications that use EF Core.

We will start by creating an ASP.NET Core application, using the “Web Application (Model-View-Controller)” template and using an in-app authentication system.

When the application is created, we can run it and verify that everything works. We can register a user if we wish.

Now, let’s start with our example. As I said, each table in our database will have user creation and creation date columns, since this means that each of our models will have these properties, we will start by creating an abstract class in the Models folder:

Now, each of our models must inherit from this Entity class. Suppose we are going to have a model called Person, which is going to have a Name property, and we want to be able to audit this model, so we create the following class in the Models folder:

Now, what we are going to do is create a controller with its views in order to have a Person CRUD. For that, we will create a controller using Scaffolding, selecting the option “MVC Controller with views, using Entity Framework”. We select the Person class as a model and select the ApplicationDbContext as the data context class. Finally, we click Add.

In this way, we have created our Controller PeopleController with their respective views. Let’s go to our ApplicationDbContext and change the name of the Person to People property:

Now, we must add a new migration, and then create our new People table in the database. For that, we go to the Package Manager Console and write the following two commands:

Add-Migration Add-People-Table

And then:

Update-Database

With this, we have the People table in the database.

Let’s go to the layout and add a menu to navigate to the index of the PeopleController:

With this, if we run the application, we can go to our People page and create a new person. However, on the creation page of the person, we see that we are shown the controls to insert the CreatedByUser and CreationDate, which is not right, because we want this information to be filled automatically. Let’s work on this

The first thing we will do is remove the extra controls we don’t need from our creation and update views, leaving only the person’s name.

Then, we need to write a function that is executed every time we are going to insert or update a record. We can do this by overwriting the SaveChangesAsync method of the ApplicationDbContext. Let’s go to the ApplicationDbContext class and write the following:

It is within the ProcessSave method that we will do our audit. Within this method we will iterate the entries, and fill in the date and user data as appropriate:

As we can see, if a record is going to be added, we fill in the date and user information, both creation and modification. If a record is going to be updated, we only modify its date and user data related to updates. If you look, in the case of user information, we are placing an empty string. We will look for a way to obtain this data.

It would not be correct if we hardcode here a reference to the HTTPContext or any class that helps us find the user who has performed the operation. This is because then we would be creating a dependency without need between our ApplicationDbContext and HTTPContext, this makes your ApplicationDbContext limited to being used in an ASP.NET environment. We can use the dependency inversion principle, which says that we must depend on abstractions, not concrete types. This to ensure we have a loosely coupled relationship between our ApplicationDbContext and the mechanism of obtaining the identity of the current user.

For that, the first thing we will do is create an interface that will serve as an abstraction for any class that provides us with current logged in user information:

Then, let’s create a class that implements this interface:

Basically, what we do is use the IHttpContextAccessor service to get the name of the logged in user. Now, we must register the ICurrentUserService in our ConfigureServices:

With this, we can inject our ICurrentUserService in our ApplicationDbContext class:

Then, through the currentUserService field, we can get the logged in user’s name:

With this we have concluded. We can run our application, create a Person, and we will see that the authenticated user information and the date and time of the operation are saved. The great thing about this is that, no matter what model we use, if our model inherits from the Entity class, the audit will be performed automatically.

Regards!