Entity Framework 6 (code first) entity versioning and auditing

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 m…

Accepted Answer

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

Entity Framework 6 (code first) entity versioning and auditing

Solution

Problem

Related problems