Entity Framework Create Audit Table/History table? [duplicate]

Question

I want to create a History/Audit Table for a particular entity. This is a complex entity with many child tables and we are using Repository Patter for our application. I looked into overriding DbContext SaveChanges?. Is it good practice to use this specially for one entity?. What are my other options?.

Thanks in advance.

Answer 1

I've been working on a library that might help.

Take a look at Audit.EntityFramework library, it intercepts SaveChanges() and can be configured to filter the entities you want to audit.

Answer 2

@thepirat000 solution probably works fine but I l like to have a minimum of NuGet dependencies, preferably 0, that are not backed by a large community/corporation and that depends heavily on a single developer.

https://github.com/thepirat000/Audit.NET/graphs/contributors

You can do it like this without any external library:

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();
}

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; }
}

DbContext:

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;
    }
}

Source:

https://www.meziantou.net/entity-framework-core-history-audit-table.htm and comment from @rasputino

You can also read more about Slowly changing dimension types and from there create a solution that fits your needs.

If you need entire Entity Framework Snapshot History look at this answer.