EF6 Code First Audit Table Per Entity

Question

I have a requirement that feels like it probably has a simpler solution with EF than what we're currently using.

Essentially, as an auditing requirement, for any entity that inherits from a given base class, I need to create both the entity's table itself, but also a table that's identical, but with 3 additional columns - a FK back to the original entity's table, a description (e.g. "Modified", "Added", "Deleted") and an XML column that will contain a serialized version of the state of the entity.

At present, we're manually adding the entities to create the audit tables (currently inherit from an AuditableEntity class and developers have to manually ensure that other fields match the original entity) and using migrations to add T-SQL triggers to the entity tables to update the data in the audit tables on any insert, update, delete.

I'd prefer if I could somehow get EF to automatically create/migrate the audit tables based on the entity tables without having to manually sync them, and likewise use an interceptor or something similar to update the audit table on insert/update/delete of an entity rather than using triggers. Does anyone know if this is possible, or done anything similar? In the past, the closest I've come is a single, common audit history table which wasn't too bad.

Answer 1

Disclaimer: I'm the owner of the project Entity Framework Plus

This project may answer to your requirement. You can access to all auditing information like entity name, action name, property name, original and current values, etc.

A lot of options is available like an AutoSave all information in the database.

// using Z.EntityFramework.Plus; // Don't forget to include this.

var ctx = new EntityContext();
// ... ctx changes ...

var audit = new Audit();
audit.CreatedBy = "ZZZ Projects"; // Optional
ctx.SaveChanges(audit);

// Access to all auditing information
var entries = audit.Entries;
foreach(var entry in entries)
{
    foreach(var property in entry.Properties)
    {
    }
}

Documentation: EF+ Audit

Answer 2

You could create one table with the columns:

• Id
• TableName
• Action (Add, update, delete)
• IdOfRecord
• XmlSerialized
• DateChanges (use datetime2)

Then override SaveChanges() to write each change to that one table.

No need to mess around with keeping Audit table schema up to date when running migrations etc