Tracking data changes with Entity Framework Core

History/Audit of data changes using Entity Framework Core

Tracing data changes can be beneficial in narrowing down the issue that may occur in production. If you are using Entity Framework Core you have several ways to store actions performed against your data. It all depends on the volume, where, how long and how do you want to query actions performed against the data.
So far I used two approaches with EF Core to store data actions.

Audit history with Serilog

Simple as it sounds, you store your data changes history in logs. Serilog is a great library to use in .NET Core applications to store and structure your logs and probably the most used one now days.
The way to do this is first to configure Serilog dependency injection in the Startup class and then just simply add ILoggerFactory to the constructor of your DbContext.

    public class MyDbContext : DbContext
    {
        private readonly ILoggerFactory loggerFactory;

        public MyDbContext(DbContextOptions<MyDbContext> options)
         : base(options)
        {
        }

        public MyDbContext(DbContextOptions<MyDbContext> options, ILoggerFactory loggerFactory)
          : base(options)
        {
            this.loggerFactory = loggerFactory;
        }
		...
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(loggerFactory);
        }

		...
    }
    

I am not going go into details here, you can find all details in Logging EF Core actions to a file using Serilog.

Benefits of storing data actions this way is that you have the exact query that was executed against your data. You do not necessary have to store data to database, instead you can store everything in text files and later setup Elastic Search on top of those logs for querying.
This is good because you are not overloading your database server with trace data and you can clear it without interference with production system. The downside is that you are going to have to spend some time setting up Elastic Search and making it usable for easy querying.

Audit history with Audit entity

If you like to keep things in one place, than this approach might work for you. It is basically an additional entity which will contain your dbContext tracked changes. This means before you do save in your dbContxt, you need to collect all the changes and store them in collection of Audit entities and store it to your database,

It is up to you what you want to save in your audit records, but some most common fields would be following

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Sample.Auditing.Data.Entities
{
    public class Audit
    {
        [Key, Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public long Id { get; set; }
        public DateTime DateTime { get; set; }
        public String Username { get; set; }
        [Required]
        [MaxLength(128)]
        public String TableName { get; set; }
        [Required]
        [MaxLength(50)]
        public String Action { get; set; }

        public String KeyValues { get; set; }
        public String OldValues { get; set; }
        public String NewValues { get; set; }
    }
}
    

You can see that I also included username which represents current identity in application which is not available in our dbContext by default, so we need to inject IHttpContextAccessor singleton instance in our startup to make it available in our dbContext constructor. We'll do that in ConfigureServices method in Startup.cs of web application which will use our dbContext.

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddSingleton<IHttpContextAccessor, HttpContextAccessor>();

            services.AddDbContext<CatalogDbContext>(options =>
                options.UseSqlServer(Configuration.GetConnectionString("CatalogModelConnection")));

            services.AddMvc();
        }
    

Now we just need to fetch the HttpContextAccessor instance in the dbContext constructor and store it in the private readonly field for using later to fetch username of the authenticated user

namespace Sample.Auditing.Data
{
    public class CatalogDbContext : DbContext
    {
        private readonly ILoggerFactory loggerFactory;
        private readonly IHttpContextAccessor httpContextAccessor;

        public CatalogDbContext(DbContextOptions<CatalogDbContext> options)
         : base(options)
        {
        }

        public CatalogDbContext(DbContextOptions<CatalogDbContext> options, ILoggerFactory loggerFactory, IHttpContextAccessor httpContextAccessor)
          : base(options)
        {
            this.loggerFactory = loggerFactory;
            this.httpContextAccessor = httpContextAccessor;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(loggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.ApplyConfiguration(new ProductConfiguration());
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<Audit> Audits { get; set; }
    }
}
    

We have initial setup done, we only need something we are going to track changes for. For that purpose I added a simple Product entity class and and abstract class for it just to avoid repeating of common shared properies of all data entities

using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Sample.Auditing.Data.Entities
{
    public abstract class BaseEntity
    {
        [Key, Column(Order = 0)]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        public Guid Id { get; set; }
    }

}

    
using System;

namespace Sample.Auditing.Data.Entities
{
    public class Product:BaseEntity
    {
        public String Name { get; set; }
        public String Description { get; set; }
        public double Price { get; set; }
    }
}

    

Only thing left is to update our dbContext and override SaveChangesAsync method. Because some of the properties will have their actual values generated by the database itself, we need to handle changes tracked by our dbContext in two separate methods, one before calling base.SaveChangesAsync which will add Audit entity for non temporary properties and collect all entities with temporary properties to be added after saving dbContext changes upon these values will be generated and it is safe to add their Audit records.

The problem with collection of entities with temporary properties is that they will change the state to Unchanged after saving changes. This means that after first save of entities with non temporary properties, all entities with previously temporary values will have state Unchanged and we won't be able to access tracked changes, because in new entity state, there will be none.

For that reason we need to couple EntityEntry and Audit object instances because we will take values before saving the entity and key values after saving the entity.

    public class CatalogDbContext : DbContext
    {
        private readonly ILoggerFactory loggerFactory;
        private readonly IHttpContextAccessor httpContextAccessor;

        public CatalogDbContext(DbContextOptions<CatalogDbContext> options)
         : base(options)
        {
        }

        public CatalogDbContext(DbContextOptions<CatalogDbContext> options, ILoggerFactory loggerFactory, IHttpContextAccessor httpContextAccessor)
          : base(options)
        {
            this.loggerFactory = loggerFactory;
            this.httpContextAccessor = httpContextAccessor;
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseLoggerFactory(loggerFactory);
        }

        protected override void OnModelCreating(ModelBuilder builder)
        {
            base.OnModelCreating(builder);
            builder.ApplyConfiguration(new ProductConfiguration());
        }

        public override async Task<int> SaveChangesAsync(bool acceptAllChangesOnSuccess, CancellationToken cancellationToken = default(CancellationToken))
        {
            var temoraryAuditEntities = await AuditNonTemporaryProperties();
            var result = await base.SaveChangesAsync(acceptAllChangesOnSuccess, cancellationToken);
            await AuditTemporaryProperties(temoraryAuditEntities);
            return result;
        }


        async Task<IEnumerable<Tuple<EntityEntry, Audit>>> AuditNonTemporaryProperties()
        {
            ChangeTracker.DetectChanges();
            var entitiesToTrack = ChangeTracker.Entries().Where(e => !(e.Entity is Audit) && e.State != EntityState.Detached && e.State != EntityState.Unchanged);

            await Audits.AddRangeAsync(
                entitiesToTrack.Where(e => !e.Properties.Any(p => p.IsTemporary)).Select(e => new Audit()
                {
                    TableName = e.Metadata.Relational().TableName,
                    Action = Enum.GetName(typeof(EntityState), e.State),
                    DateTime = DateTime.Now.ToUniversalTime(),
                    Username = this.httpContextAccessor?.HttpContext?.User?.Identity?.Name,
                    KeyValues = JsonConvert.SerializeObject(e.Properties.Where(p => p.Metadata.IsPrimaryKey()).ToDictionary(p => p.Metadata.Name, p => p.CurrentValue).NullIfEmpty()),
                    NewValues = JsonConvert.SerializeObject(e.Properties.Where(p => e.State == EntityState.Added || e.State == EntityState.Modified).ToDictionary(p => p.Metadata.Name, p => p.CurrentValue).NullIfEmpty()),
                    OldValues = JsonConvert.SerializeObject(e.Properties.Where(p => e.State == EntityState.Deleted || e.State == EntityState.Modified).ToDictionary(p => p.Metadata.Name, p => p.OriginalValue).NullIfEmpty())
                }).ToList()
            );

            //Return list of pairs of EntityEntry and ToolAudit
            return entitiesToTrack.Where(e => e.Properties.Any(p => p.IsTemporary))
                 .Select(e => new Tuple<EntityEntry, Audit>(
                     e,
                 new Audit()
                 {
                     TableName = e.Metadata.Relational().TableName,
                     Action = Enum.GetName(typeof(EntityState), e.State),
                     DateTime = DateTime.Now.ToUniversalTime(),
                     Username = this.httpContextAccessor?.HttpContext?.User?.Identity?.Name,
                     NewValues = JsonConvert.SerializeObject(e.Properties.Where(p => !p.Metadata.IsPrimaryKey()).ToDictionary(p => p.Metadata.Name, p => p.CurrentValue).NullIfEmpty())
                 }
                 )).ToList();
        }

        async Task AuditTemporaryProperties(IEnumerable<Tuple<EntityEntry, Audit>> temporatyEntities)
        {
            if (temporatyEntities != null && temporatyEntities.Any())
            {
                await Audits.AddRangeAsync(
                temporatyEntities.ForEach(t => t.Item2.KeyValues = JsonConvert.SerializeObject(t.Item1.Properties.Where(p => p.Metadata.IsPrimaryKey()).ToDictionary(p => p.Metadata.Name, p => p.CurrentValue).NullIfEmpty()))
                    .Select(t => t.Item2)
                );
                await SaveChangesAsync();
            }
            await Task.CompletedTask;
        }

        public DbSet<Product> Products { get; set; }
        public DbSet<Audit> Audits { get; set; }
    }

    

You probably noticed I am using NullIfEmpty method call when selecting properties to Dictionary. This is a custom extension method which helps me to avoid storing serialized empty Dictionary JSON value {}. In LINQ, ToDictionary method will never produce a null value, instead you will have an empty Dictionary and when empty dictionary instance ins serialized to JSON, you get {} string value. Now, this is not such a big deal, but it is better not to save anything if it has not value and save some space in your database.

Another extension I am using is ForEach extension method. There is a similar method for Array class already build in .NET, but it is void and in some cases like this I need chaining to do projection and/or additional filtering on the collection.

    public static class Extensions
    {
        public static IDictionary<TKey, TValue> NullIfEmpty<TKey, TValue>(this IDictionary<TKey, TValue> dictionary)
        {
            if (dictionary == null || !dictionary.Any())
            {
                return null;
            }
            return dictionary;
        }

        public static IEnumerable<T> ForEach<T>(this IEnumerable<T> source, Action<T> action)
        {
            foreach (T element in source)
            {
                action(element);
            }
            return source;
        }
    }
    

Extension method NullIfEmpty will simply take an instance of Dictionary and if it has no elements it will return null, otherwise it will just return same instance of Dictionary.

Although it is easy to query Audits table in your database, this data audit method adds additional operations when saving your dbContext. Usually this is not a big load, but it also depends on the way your application works and the amount of entities it stores on dbContext save. It also uses additional space in you database.

Depending on your needs and application architecture you can decide for one of these methods for tracing data changes, or if you are not sure, you can just use them both in your application :).

The whole code with the sample ASP.NET Core WebAPI application is available for forking/downloading from Github https://github.com/dejanstojanovic/dotnetcore-efcore-audit

Entity update issues

If you test this solution on your local, you will notice that if you update your entity by mapping your DTO to entity instance (either through binding or using Automapper), old and new values JSON will be pretty much the same. The problem is that your entity does not originally comes from the database, so DbContext does not know the old value of the properties.

This can be simply fixed by pulling the entity from the database and updating it, either manually like in the sample code below or using Automapper.

        [HttpPut("{id}")]
        public async Task Put(Guid id, [FromBody] Product value)
        {
            var product = dbContext.Products.Single(p => p.Id == id);

            //Map exisiting product with new values (idealy with Automapper)
            product.Name = value.Name;
            product.Price = value.Price;
            product.Description = value.Description;

            dbContext.Update(product);
            await dbContext.SaveChangesAsync();
        }
    

Saving all the fields

Mapping DTO to entity, also cause all the properties mapped to be marked as modified, so new and old values JSON has all properties, even the ones that are not changed. I still haven' managed to fix this issue, but once I find out the way to overcome this overhead I will publish it. Stay tuned for the updates

References

Disclaimer

Purpose of the code contained in snippets or available for download in this article is solely for learning and demo purposes. Author will not be held responsible for any failure or damages caused due to any other usage.


About the author

DEJAN STOJANOVIC

Dejan is a passionate Software Architect/Developer. He is highly experienced in .NET programming platform including ASP.NET MVC and WebApi. He likes working on new technologies and exciting challenging projects

CONNECT WITH DEJAN  Loginlinkedin Logintwitter Logingoogleplus Logingoogleplus

JavaScript

read more

SQL/T-SQL

read more

Umbraco CMS

read more

PowerShell

read more

Comments for this article