Entity Framework 6 (code first) entity versioning and auditing

c# entity-framework entity-framework-6 sql-server

Question

Entity Framework 6.1.1 and SQL Server 2008 R2 are both options I'm considering.

I'm currently using EF's code-first functionality to build my models and database. My primary use-case is to keep track of all modifications made to a specific entity (ID is the main column) to assist auditors in keeping track of all modifications and who made them.

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

Supports Entity Framework this kind of database design? If so, how can I configure my models and solution to make this possible?

The only option I have is to monitor all incoming calls to theSaveChanges() technique on theDbContext and create a separate log for each database modification.Audit table, but obtaining information can become more difficult as a result.

I would greatly appreciate any assistance with setting up audit trails using SQL Server and EF 6.

1
9
2/20/2015 11:19:07 AM

Popular Answer

By overloading the dbContext SaveChanges() method, I used the second strategy you mention:

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, to log a specific item, I just call the overloaded SaveChanges function and supply a UserId:

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

I also follow a tradition.DoNotLog I decorate the entity properties I don't want to log with an attribute. Without this, since each entity alteration results in one database entry, the logging might produce a significant volume of data.

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

The GetAuditRecordsForChange method checks to see whether there are anyDoNotLog features and produces aList<AuditLog> which is recorded and stored 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; }
    }
7
2/20/2015 11:46:32 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow