Entity Framework Core: History / Audit table

Some applications need to keep a list of all the changes in a table. You can implement this functionality by adding triggers in the database or by using native features of the RDBMS such as Temporal tables or Change Data Capture). Let's see how you can implement a similar functionnalities in C# with Entity Framework Core in a provider agnostic way!

Note: The following code has been tested with Entity Framework Core 2.0.

The short idea is to store a list of all changes in a table. For instance the following code does some changes in the customer table. After calling the SaveChangeAsync method, the Audit table is automatically filled with the old and new values of each rows.

using (var context = new SampleContext())
{
    // Insert a row
    var customer = new Customer();
    customer.FirstName = "John";
    customer.LastName = "doe";
    context.Customers.Add(customer);
    await context.SaveChangesAsync();

    // Update the first customer
    customer.LastName = "Doe";
    await context.SaveChangesAsync();

    // Delete the customer
    context.Customers.Remove(customer);
    await context.SaveChangesAsync();
}

Audit table

How does it work?

The idea is to wrap the SaveChangesAsync method. Before saving rows, you can get the old and new values of the entries by using the ChangeTracker property of the context. If some of the rows have database generated values (for instance, an auto-incremented value), you can get the value of the concerned properties after rows are saved. In this case, you must save the new audit rows just after.

First, create the model:

public class Audit
{
    public int Id { get; set; }
    public string TableName { get; set; }
    public DateTime DateTime { get; set; }
    public string KeyValues { get; set; }
    public string OldValues { get; set; }
    public string NewValues { get; set; }
}

public class Customer
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
}

public class SampleContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Audit> Audits { get; set; }
}

In the following code, I only override the SaveChangesAsync method for brevity. In real usage, you should also override the SaveChanges method.

The OnBeforeChanges method creates a list of AuditEntry. An AuditEntry store the table name, the ids and the old and new values of an entry. If all the values are known before actually saving the row, you can the Audit instances in the change tracker, so there are saved at the same time as other entries.

If some entries have temporary values, e.g. values that will be set by the database, you cannot save the Audit entry immediately. Instead, you must wait the SaveChanges method does its job. Then, you can get the generated value and finish the Audit entry. Finally, you must save the new entries in the database.

public class SampleContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Audit> Audits { get; set; }

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

    private List<AuditEntry> OnBeforeSaveChanges()
    {
        ChangeTracker.DetectChanges();
        var auditEntries = new List<AuditEntry>();
        foreach (var entry in ChangeTracker.Entries())
        {
            if (entry.Entity is Audit || entry.State == EntityState.Detached || entry.State == EntityState.Unchanged)
                continue;

            var auditEntry = new AuditEntry(entry);
            auditEntry.TableName = entry.Metadata.Relational().TableName;
            auditEntries.Add(auditEntry);

            foreach (var property in entry.Properties)
            {
                if (property.IsTemporary)
                {
                    // value will be generated by the database, get the value after saving
                    auditEntry.TemporaryProperties.Add(property);
                    continue;
                }

                string propertyName = property.Metadata.Name;
                if (property.Metadata.IsPrimaryKey())
                {
                    auditEntry.KeyValues[propertyName] = property.CurrentValue;
                    continue;
                }

                switch (entry.State)
                {
                    case EntityState.Added:
                        auditEntry.NewValues[propertyName] = property.CurrentValue;
                        break;

                    case EntityState.Deleted:
                        auditEntry.OldValues[propertyName] = property.OriginalValue;
                        break;

                    case EntityState.Modified:
                        if (property.IsModified)
                        {
                            auditEntry.OldValues[propertyName] = property.OriginalValue;
                            auditEntry.NewValues[propertyName] = property.CurrentValue;
                        }
                        break;
                }
            }
        }

        // Save audit entities that have all the modifications
        foreach (var auditEntry in auditEntries.Where(_ => !_.HasTemporaryProperties))
        {
            Audits.Add(auditEntry.ToAudit());
        }

        // keep a list of entries where the value of some properties are unknown at this step
        return auditEntries.Where(_ => _.HasTemporaryProperties).ToList();
    }

    private Task OnAfterSaveChanges(List<AuditEntry> auditEntries)
    {
        if (auditEntries == null || auditEntries.Count == 0)
            return Task.CompletedTask

        foreach (var auditEntry in auditEntries)
        {
            // Get the final value of the temporary properties
            foreach (var prop in auditEntry.TemporaryProperties)
            {
                if (prop.Metadata.IsPrimaryKey())
                {
                    auditEntry.KeyValues[prop.Metadata.Name] = prop.CurrentValue;
                }
                else
                {
                    auditEntry.NewValues[prop.Metadata.Name] = prop.CurrentValue;
                }
            }

            // Save the Audit entry
            Audits.Add(auditEntry.ToAudit());
        }

        return SaveChangesAsync();
    }
}

public class AuditEntry
{
    public AuditEntry(EntityEntry entry)
    {
        Entry = entry;
    }

    public EntityEntry Entry { get; }
    public string TableName { get; set; }
    public Dictionary<string, object> KeyValues { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> OldValues { get; } = new Dictionary<string, object>();
    public Dictionary<string, object> NewValues { get; } = new Dictionary<string, object>();
    public List<PropertyEntry> TemporaryProperties { get; } = new List<PropertyEntry>();

    public bool HasTemporaryProperties => TemporaryProperties.Any();

    public Audit ToAudit()
    {
        var audit = new Audit();
        audit.TableName = TableName;
        audit.DateTime = DateTime.UtcNow;
        audit.KeyValues = JsonConvert.SerializeObject(KeyValues);
        audit.OldValues = OldValues.Count == 0 ? null : JsonConvert.SerializeObject(OldValues);
        audit.NewValues = NewValues.Count == 0 ? null : JsonConvert.SerializeObject(NewValues);
        return audit;
    }
}

Conclusion

This solution is provider agnostic, so it should work with any database. However, it is not perfect. The datetime column does not correspond to the date of the actual change in the database. It introduces an overhead, mainly when there are temporary values as you need to call the SaveChanges method twice. If you are using SQL Server, you should consider using existing functionalities such as Change Data Capture, Change Tracking or Temporal Tables. Yes, you have the choice with SQL Server 😃 However, this post demonstrate how you can use the ChangeTracker of Entity Framework Core to get all the information about changes.

Follow me:
Enjoy this blog? Buy Me A Coffee Donate with PayPal

Comments

birthright2dream -

Bonjour! Nice approach to dealing w/db-generated (auto-inc) PK! for audit trail. I'm curious about using property.OriginalValue w/EF Core. While I've successfully used OriginalValue in ADO for audit trail and there was an issue resolved in EF Core 1.1.1 to fix this https://github.com/aspnet/EntityFrameworkCore/issues/7332), the problem seems to still exist – at least w/ EF Core 1.1.2. In my controller code (built by VS 2017 Scaffolding), on call to SaveChangesAsync(), the ChangeTracker entity's OriginalValue contains the SAME value as CurrentValue.

S'il vous plaît, what version of EF Core was your example using (as it appears OriginalValue was working as expected)? Merci beaucoup ! 😊

Gérald Barré -

Hi,

The code of this post is using Entity Framework Core 2.0 (I'll update the post to indicate it). I didn't test the code with EF Core 1.1, so I cannot tell you if it should work with this version.

Howard Stenton -

G'day.

Using EF Core 2.0 and experienced same problem Original Value contains same value as Current Value. see https://stackoverflow.com/questions/28344495/ef-objectstateentry-originalvalues-contains-currentvalues for the following work around

In CustomersController Edit changed

try
{
    _context.Update(customer);
    await _context.SaveChangesAsync();
}

to

try
{
    //following hack to get ChangeTracker to pickup original value
    Customer cust = _context.Customers.Find(customer.Id);
    if (cust.FirstName != customer.FirstName)
    {
        cust.FirstName = customer.FirstName;
    }

    if (cust.LastName != customer.LastName)
    {
        cust.LastName = customer.LastName;
    }

    _context.Update(customer);
    await _context.SaveChangesAsync();
}
Howard Stenton -

Apologies:

hack should read

_context.Update(cust);
await _context.SaveChangesAsync();
bborad -

Hi

How can I use this in EF 5? basically not sure how to replace followings

property.IsTemporary
prop.Metadata.IsPrimaryKey()
auditEntry.NewValues[prop.Metadata.Name] = prop.CurrentValue

Thanks

Leonardo Dorathoto -

in Controller ASP.NET Core 2.0

var db = _context.Customers.Find(id);
_context.Entry(db).CurrentValues.SetValues(Customers);
await _context.SaveChangesAsync();

so OriginalValue and NewValue worked flawlessly.

Lukas -

Hi,

Can I use your code in a commercial application?

Regards,

Lukas

Gérald Barré -

Hi,

Yes, you can use this code in a commercial application. This is why I wrote it on a public place! I'll appreciate you add a comment with a link to this post in your code. It could be a nice reading for your co-workers.

If I should add a license, I'd choose MIT: https://choosealicense.com/licenses/mit/. I'll eventually add a page on this site to clarify this point.

Lukas -

Could you point out how to get userid?

Gérald Barré -

Hi,

If you want to get the authenticated user, there are multiple ways depending of the type of application you are developing. If you are using ASP.NET Core, you can have a look at my answer on StackOverflow https://stackoverflow.com/a/48554738/2996339

Lukas -

thanks a lot

james donoghue -

EDIT:

  1. Add the following before foreach (var property in entry.Properties):
var oldValues = await entry.GetDatabaseValuesAsync().ConfigureAwait(false);
  1. Replace auditEntry.OldValues[propertyName] = property.OriginalValue; with:
auditEntry.OldValues[propertyName] = oldValues[propertyName];

Unfortunately this does result in a database call but I couldn't find any other generic way to do it.

The problem is that the entity must be loaded into EF, otherwise OldValue just returns the same as CurrentValue. If anybody enhances this to check whether the entity has been loaded before querying the DB then please do share.

Tzvi Kaidanov -

The trouble here that you can't set what entities to audit. You probably don't need it all. I've seen this https://github.com/thepirat000/Audit.NET. Would love to hear what you think about this library.

Gérald Barré -

You can easily select the entities you want to audit at the beginning of the foreach loop in OnBeforeSaveChanges. For instance, in the code the Audit entity is filtered, and you can add the condition you want.

I've never used Audit.NET, so I can't tell you much about it.

Pavel -

Hello!

Thank you for your post! I've tried to use it and would like to suggest improvement how to detect updates. On Core EF 2.2 EntityState.Modified is true even if field was not modified, so I added some code:

case EntityState.Modified:
    if (property.IsModified)
    {
        if(property.OriginalValue == null && property.CurrentValue == null)
          continue;

        if(property.OriginalValue == null ||
           property.CurrentValue == null ||
           !property.OriginalValue.Equals(property.CurrentValue))
        {
            auditEntry.OldValues[propertyName] = property.OriginalValue;
            auditEntry.NewValues[propertyName] = property.CurrentValue;
        }
CiMatt -

Great article, very clear and well written and certainly helped me to work out how I could save audit entries. One thing that may be worth considering to avoid tracking Temporary values is to save the initial changes without committing changes to the DB. I ended up with the following.

public async Task<int> SaveWithAuditAsync()
{
    // save requested changes to DB but don't accept changes to the ChangeTracker yet
    // this means we can get IDs and other temporary values set but still have original values
    // to use for our audit log
    var result = await _repositoryContext.SaveChangesAsync(false);

    // get a List of all audit log entries we want to add to the DB
    // reference https://www.meziantou.net/entity-framework-core-history-audit-table.htm
    var auditLogs = GetAuditLogs();

    // now we can accept original changes as we have the log entries with
    // original and new values and no temporary values
    _repositoryContext.ChangeTracker.AcceptAllChanges();

    // add all the audit entries to the context
    await _repositoryContext.AddRangeAsync(auditLogs);

    // save the log entries to DB
    await _repositoryContext.SaveChangesAsync();

    return result;
}

Leave a reply