Audit with Entity Framework Core

How to implement an efficient audit system with C# and Entity Framework Core? I have talked about Entity Framework in my previous posts:

• Creating A Model For An Existing Database
• Entity Framework Core And Calling A Stored Procedure
• Transactions With Entity Framework Core
• Database Connection Resiliency In Entity Framework Core
• Database Connection Resiliency In Entity Framework Core: Update

Often, during my implementation, there is a common request: track all changes in the database and where and who is changing a record.

A way to implement an audit is to override the SubmitChanges function in your data context. I created a post about it a long time ago; it was 2014. The post has title Log record changes in SQL server in an audit table and I was using Entity Framework.

So, I googled a bit and I found a very nice NuGet package that can help us to implement what we need. Audit.NET is the package and it is pretty complete. Although the documentation is good, I was struggle to understand how to use it in my project. For this reason, I want to give you all my thoughts and the final solution. So, you can use it.

What is Audit.NET?

An extensible framework to audit executing operations in .NET and .NET Core that allow you to generate audit logs with evidence for reconstruction and examination of activities that have affected specific operations or procedures.

Then, with Audit.NET you can generate tracking information about operations being executed. It gathers environmental information such as the caller user id, machine name, method name, exceptions, including execution time and duration, and exposing an extensible mechanism to enrich the logs and handle the audit output.

Extensions

Output extensions are provided to log to JSON Files, Event Log, SQL, MySQL, PostgreSQL, MongoDB, AzureBlob, DocumentDB, Redis, Elasticsearch, DynamoDB, UDP datagrams and more.

Interaction extensions to audit different systems are provided, such as Entity Framework, MVC, WebAPI, WCF, File System, SignalR and HttpClient.

Getting started with Audit.NET

From the author of this library, we can use a project template. To install the template and create a new project from the command line, use this command:

dotnet new -i Audit.WebApi.Template::1.0.1
dotnet new webapiaudit -E -S

So, as an advice, it is easy to run this commands in a folder because the second line creates all files in the current folder.

If we analyse the project, the main reason of this solution is to show how tracking all the requests to the webapis. The configuration commands Audit.NET to save on files all logs.

So, I specified the version of the template 1.0.1 because this one is supporting Entity Framework Core 5.0.1. If you face an error like:

that means you are using an older version of Audit.NET. Take a look to Stackoverflow or GitHub.

The parameter -S means you want to add Swagger to your project.

Then, my first change is to remove [FromBody] from the ValuesController in order to have a better input in the Swagger page. For example:

// before
[HttpPost]
public async Task<ActionResult<int>> Post([FromBody] string value)
{
    return Ok(await _provider.InsertAsync(value));
}

// after
[HttpPost]
public async Task<ActionResult<int>> Post(string value)
{
    return Ok(await _provider.InsertAsync(value));
}

Now, if you run the project, you can open the Swagger documentation from

https://localhost:50732/swagger/index.html

and you can have this page:

Now, the project is ready for my changes. But first, where can we find the configuration for Audit.NET? In the AuditConfiguration.cs there is the configuration and by default the logs are created in a temporary folder in the root of your hard disk.

/// <summary>
/// Global Audit configuration
/// </summary>
public static IServiceCollection ConfigureAudit(this IServiceCollection serviceCollection)
{
    Audit.Core.Configuration.Setup()
        .UseFileLogProvider(_ => _
            .Directory(@"C:\Temp")
            .FilenameBuilder(ev => 
                $"{ev.StartDate:yyyyMMddHHmmssffff}_" + 
                $"{ev.CustomFields[CorrelationIdField]?.ToString().Replace(':', '_')}.json"))
        .WithCreationPolicy(EventCreationPolicy.InsertOnEnd);

    // Entity framework audit output configuration
    Audit.EntityFramework.Configuration.Setup()
        .ForContext<MyContext>(_ => _
            .AuditEventType("EF:{context}"))
        .UseOptOut();

    return serviceCollection;
}

So, I’m going to change this too. Let’s start to change the project adding the database.

Log changes in an Audit table

First, Audit.NET has a SQL Server provider to store the audit events in a SQL Table, in JSON format. I’m going to add it to the project

Install-Package Audit.NET.SqlServer

Therefore, this approach requires to create manually a table in the database. Here you have the script I changed to add the User that made the change.

Create an audit table

First, connect to your database with SQL Manager Studio or your preferred tool and run this script.

CREATE TABLE [Event]
(
    [EventId] BIGINT IDENTITY(1,1) NOT NULL,
    [InsertedDate] DATETIME NOT NULL DEFAULT(GETUTCDATE()),
    [LastUpdatedDate] DATETIME NULL,
    [JsonData] NVARCHAR(MAX) NOT NULL,
    [EventType] NVARCHAR(100) NOT NULL,
    [User] NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_Event PRIMARY KEY (EventId)
)

Now, we have to change the AuditConfiguration and in particular the ConfigureAudit. I want to add a new parameter to pass the connection string for the database. So, the function appears like that:

/// <summary>
/// Global Audit configuration
/// </summary>
public static IServiceCollection ConfigureAudit(this IServiceCollection serviceCollection, 
                                                string connString)
{
    // ...
}

Then, we have to change the ConfigureServices to pass to the function the connection string. I’m going to add a new configuration in the appsettings.json and it looks like:

{
  "Logging": {
    "LogLevel": {
      "Default": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionString": "yourconnectionstring"
}

Change the ConfigureServices

And now I’m going to change the ConfigureServices as the following code:

public void ConfigureServices(IServiceCollection services)
{
    string connString = Configuration.GetValue<string>("ConnectionString");
    services.AddHttpContextAccessor();
    services.AddTransient<IValuesProvider, ValuesProvider>();

    // TODO: Configure your context connection
    services.AddDbContext<MyContext>(_ => _.UseSqlServer(connString));
    services
        .ConfigureAudit(connString)
        .AddMvc(options =>
        {
            options.AddAudit();
            options.EnableEndpointRouting = false;
        });

    services.AddSwaggerGen();
}

Next step is to add the configuration to save the logs into the Event table. In the configuration, we can define:

• ConnectionString: the connection string to the database
• Schema: the database schema
• TableName: the table name
• IdColumnName: the Id column name
• JsonColumnName: the column where to save the log in JSON format
• LastUpdatedColumnName: the last update date and time
• an column for event type. In the case of the webapi implementation, we have all requests to the api plus the requests to the context.
• the user that made the change

Then, we have to setup Audit.NET for Entity Framework for the data context.

Audit.Core.Configuration.Setup()
    .UseSqlServer(config => config
        .ConnectionString(connString)
        .Schema("dbo")
        .TableName("Event")
        .IdColumnName("EventId")
        .JsonColumnName("JsonData")
        .LastUpdatedColumnName("LastUpdatedDate")
        .CustomColumn("EventType", ev => ev.EventType)
        .CustomColumn("User", ev => ev.Environment.UserName));

Audit.EntityFramework.Configuration.Setup()
    .ForContext<MyContext>(_ => _
        .AuditEventType("EF:{context}"))
    .UseOptOut();

Finally, we are ready to save logs. Run the application, go to the Swagger page and add a new record and update it. Look at the Event table: you have some logs that come from the api calls and the entity framework activities.

Finally, nice but I want to achieve something more specific and easy to understand and use.

Adding an audit table for each table

After that, audit with Entity Framework Core is going quite well but there is one more important step I want to achieve. I want to save every change for every record that occurs in the database. So, I can track who and when changed a record and also I can decide to go back.

So, the idea is to have the main table (for example Values or Contacts) to save the updated data and a specular table for audit (for example Audit_Value and Audit_Contacts): an audit table contains all the fields from the main table plus some common fields to track what action occurred, when and who did it.

IAudit interface

It is a bit weird but in C# we can’t inherit from two classes. So, I have to create an interface for all the audit table to add the fields I need, quite basic interface.

public interface IAudit
{
    string AuditAction { get; set; }
    DateTime AuditDate { get; set; }
    string UserName { get; set; }
}

After that, all the audit table must be implemented this interface. Now, look at the project: there is a ValueEntity. I want to create a replica for the audit; then, I create a new file Audit_ValueEntity and implement the interface and the fields.

public class ValueEntity
{
    [Key]
    public int Id { get; set; }
    public string Value { get; set; }
}

public class Audit_ValueEntity : IAudit
{
    [Key]
    public int AuditId { get; set; }
    public int Id { get; set; }
    public string Value { get; set; }

    public string AuditAction { get; set; }
    public DateTime AuditDate { get; set; }
    public string UserName { get; set; }
}

So, I can hear you now. Why are we doing that? I use the IAudit interface to have a common inheritance: so, I can configure once and for all the behaviour in the Audit.NET configuration. This is an interface, so I have to implement the fields. I know I can now inherit from IAudit and ValueEntity but if I do that, for same reasons, Entity Framework won’t create the audit table. If you have a better idea, please comment below or use the forum.

Now, I have to update the context. The Contacts table is a bit more complex table and I created it to check the audit. You have the code of this table and its implementation on GitHub.

public class MyContext : AuditDbContext
{
    public MyContext(DbContextOptions<MyContext> options) : base(options)
    {
    }

    public DbSet<ContactEntity> Contacts { get; set; }
    public DbSet<Audit_ContactEntity> Audit_Contacts { get; set; }


    public DbSet<ValueEntity> Values { get; set; }
    public DbSet<Audit_ValueEntity> Audit_Values { get; set; }
}

Finally, we can change the AuditConfiguration. How you see in the following code, I use Map to associate the main table with the audit table and with AuditEntityAction I’m saying that an entity implement IAudit, it has to put same values in the fields AuditDate, UserName and AuditAction. The Action values can be Insert, Update and Delete.

Audit.Core.Configuration.Setup()
    .UseEntityFramework(ef => ef.AuditTypeExplicitMapper(m => m
        .Map<ValueEntity, Audit_ValueEntity>()
        .Map<ContactEntity, Audit_ContactEntity>()
        // add more .Map<TableEntity, Audit_TableEntity>()
        .AuditEntityAction<IAudit>((evt, entry, auditEntity) =>
        {
            auditEntity.AuditDate = DateTime.UtcNow;
            auditEntity.UserName = evt.Environment.UserName;
            auditEntity.AuditAction = entry.Action;
        })));

Just for your information, you have ValuesProvider that implement the basic CRUD functionalities for the value table. I created the a similar one for Contacts.

One important thing: don’t forget to add a migration for your context and update the database.

add-migration InitialMigration
update-database

This is the final audit with Entity Framework Core I expected. What do you think?

Last look at the audit tables

Now, I’m using the Contacts table only because it is a bit more interesting than a single value in the Values table. As you can see, I added some records in the table and this is the current state of the records.

If you take a look to the audit table, we can see all the changes I made, field by field.

Conclusion

So, we implemented a pretty clean audit with Entity Framework Core. It is easy to use in every application. The source code is a web application and you find it on GitHub.