Entity Framework 6 (code first) entity versioning

Question

I'm looking at using Entity Framework 6.1.1 with SQL Server 2008 R2.

Currently I'm creating my models and database using the code-first EF feature. My basic use-case is to create a journal of all changes to a particular entity (ID is the key column) to help auditors track all changes made and by whom. e.g:

|ID|Version|Created Date|Created By|Modified Date|Modified By|Modify Action| ... (rest of entity fields)
-------------------------------------------------------------------------------------------------------
| 4| 12    | 12-Mar-14  | tom      | 20-Feb-15   | jack      | Update      |
| 4| 11    | 12-Mar-14  | tom      | 14-Feb-15   | jill      | Update      |
| 4| 1     | 12-Mar-14  | tom      | 12-Mar-14   | tom       | Create      |

Does Entity Framework support this type of database scheme? If so, how can I set my models/solution up to facilitate this?

The other alternative I have is by intercepting all calls to the SaveChanges() method on the DbContext and log all database changes into a separate Audit table, but this might make retrieving information more challenging.

Any help on creating audit trails with SQL Server and EF 6 would be greately appreciated.

Answer 1

You could look at Entity Framework Extended. It has audit functionality that I have used to log all changes to entities to XML. From the documentation:

The Audit Log feature will capture the changes to entities anytime they are submitted to the database. The Audit Log captures only the entities that are changed and only the properties on those entities that were changed. The before and after values are recorded. AuditLogger.LastAudit is where this information is held and there is a ToXml() method that makes it easy to turn the AuditLog into xml for easy storage.

The AuditLog can be customized via attributes on the entities or via a Fluent Configuration API.

UPDATE:

Entity Framework Extended is no longer supported since 2015. Please refer to Entity Framework Plus for this feature.

Answer 2

I would say this is a good candidate for the event sourcing pattern mentioned in a DDD architecture. You never change your entity table but always inserting.

In that way, when you need an specific version you just re-play all events and apply them to the entity from version 0 to the version you are looking for. Scalability can be solved with entity snapshots.

Second approach is also valid.

Reference: http://microservices.io/patterns/data/event-sourcing.html

Answer 3

I have used the 2nd approach you mention, by overloading the dbContext SaveChanges() method:

public class MyContext : DbContext
{

 public int SaveChanges(int userId)
 {
    // Get all Added/Deleted/Modified entities (not Unmodified or Detached)
    foreach (var ent in this.ChangeTracker.Entries().Where(p => p.State ==  EntityState.Added 
    || p.State == EntityState.Deleted || p.State == EntityState.Modified))
    {

        foreach (AuditLog x in GetAuditRecordsForChange(ent, userId))
        {
            this.AuditLogs.Add(x);
        }
    }
    return base.SaveChanges();
  }
...

So if I want to log a particular entity, I just call the overloaded SaveChanges & pass in a UserId:

public void Update(StockCatalogueItem entity, int userId)
{
     _context.SaveChanges(userId);
}

I also have a custom DoNotLog attribute which I use to decorate the entity properties that I don't want to log. Without this, the logging could generate a huge amount of data, as each entity modification equals one db entry.

[DoNotLog]
public int CreatedBy { get; set; }

The GetAuditRecordsForChange method does the checking for any DoNotLog properties and returns a List<AuditLog> which gets saved in the AuditLogs table:

public class AuditLog
    {
        public int Id { get; set; }
        public int CreatedBy { get; set; }
        public DateTime CreatedOn { get; set; }
        public AuditEventType EventType { get; set; }
        public string TableName { get; set; }
        public int EntityId { get; set; }
        public string ColumnName { get; set; }
        public string Controller { get; set; }
        public string Action { get; set; }
        public string IPAddress { get; set; }
        public string OriginalValue { get; set; }
        public string NewValue { get; set; }
    }