How to enable Seperate Audits Table in Entity Framework

Question

I have a Entity Framework based database with a few entities/models/table. For e.g. Documents Model, I am want to track all changes to each record in that table, in a seperate table called DocumentChanges Model/Table.

Could you please guide me on how to enable/tell EF to track/audit all changes to the table in a separate table?, not just a date time stamp, but save the full record for every change in a separate table.

Answer 1

The library Audit.EntityFramework can help you to do what you want.

You'll need to implement your own DataProvider to store the data formatted as you wish.

For example:

void StartUp()
{
    //Setup to use your own provider to store the data
    Audit.Core.Configuration.Setup()
        .UseCustomProvider(new YourDataProvider());

    //Setup to audit EF operations only for the table Documents
    //(Your DbContext must inherit from AuditDbContext)
    Audit.EntityFramework.Configuration.Setup()
        .ForAnyContext(x => x.IncludeEntityObjects())
        .UseOptIn()
            .Include<Documents>();
}

class YourDataProvider : AuditDataProvider
{
    public override object InsertEvent(AuditEvent auditEvent)
    {
        //Get some enviroment info:
        var userName = auditEvent.Environment.UserName
        //Get the complete log for the EF operation:
        var efEvent = auditEvent.GetEntityFrameworkEvent();
        foreach(var entry in efEvent.Entries)
        {
            // each entry is a modified entity (updated, deleted or inserted)
            if (entry.Action == "Update")
            {
                //You can access the column values
                var value = entry.ColumnValues["ID"];
                //...or the columns changes
                var changes = entry.Changes.Select(ch => ch.ColumnName + ": " + 
                                      ch.OriginalValue + " -> " + ch.NewValue);
            }
            //... insert into DocumentChanges table
        }
        return id;
    }
}