none
Does EF 6 and higher offer better ways of auditing interactions with the data? RRS feed

  • Question

  • I've been tasked with developing a means of auditing reads/edits/deletes of data from our databases. I did a search on how to do this with EF. I found this blog post on CodeProject, Implementing Audit Trail using Entity Framework Part-1. However, the article was written back in 2009. I'm sure it will work, but I'm wondering if using EF 6 and higher, there might be better ways of doing this?

    Rod

    Wednesday, May 25, 2016 4:45 PM

All replies

  • Hi Rod at Work,

    EF6 use DbContext instead of OjbectContext, you could use ChangeTracker in SaveChanges override to find added/modified entities of custom type. like below:

    public interface IAuditedEntity {
      string CreatedBy { get; set; }
      DateTime CreatedAt { get; set; }
      string LastModifiedBy { get; set; }
      DateTime LastModifiedAt { get; set; }
    }
    
    public override int SaveChanges() {
      var addedAuditedEntities = ChangeTracker.Entries<IAuditedEntity>()
        .Where(p => p.State == EntityState.Added)
        .Select(p => p.Entity);
    
      var modifiedAuditedEntities = ChangeTracker.Entries<IAuditedEntity>()
        .Where(p => p.State == EntityState.Modified)
        .Select(p => p.Entity);
    
      var now = DateTime.UtcNow;
    
      foreach (var added in addedAuditedEntities) {
        added.CreatedAt = now;
        added.LastModifiedAt = now;
      }
    
      foreach (var modified in modifiedAuditedEntities) {
        modified.LastModifiedAt = now;
      }
    
      return base.SaveChanges();
    }

    Best regards,

    Cole Wu


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, May 26, 2016 9:13 AM
    Moderator
  • Why wouldn't you use the DB engine itself and use auditing tools like auditing triggers, like the ones for MS SQL Server? 
    Thursday, May 26, 2016 10:33 AM
  • Why wouldn't you use the DB engine itself and use auditing tools like auditing triggers, like the ones for MS SQL Server? 

    Normally I would, but I was told not to use triggers in SQL Server.

    Rod

    Thursday, May 26, 2016 1:42 PM
  • Thank you Cole, I'll look into this.

    Rod

    Thursday, May 26, 2016 1:43 PM
  • Why wouldn't you use the DB engine itself and use auditing tools like auditing triggers, like the ones for MS SQL Server? 


    Normally I would, but I was told not to use triggers in SQL Server.

    Rod


    Why you were told this makes no sense.
    Thursday, May 26, 2016 2:20 PM
  • Why wouldn't you use the DB engine itself and use auditing tools like auditing triggers, like the ones for MS SQL Server? 


    Normally I would, but I was told not to use triggers in SQL Server.

    Rod


    Why you were told this makes no sense.

    I don't know. It's an argument I lost, so I have to do it this way or something else using EF.

    Rod

    Thursday, May 26, 2016 4:48 PM
  • Why wouldn't you use the DB engine itself and use auditing tools like auditing triggers, like the ones for MS SQL Server? 


    Normally I would, but I was told not to use triggers in SQL Server.

    Rod


    Why you were told this makes no sense.


    I don't know. It's an argument I lost, so I have to do it this way or something else using EF.

    Rod


    I don't think I would use EF for this. And maybe you should have had a DBA backing you up. The DB engine and its tools are far superior to what some ORM can do concerning auditing. It's like saying you are going to write some auditing code for the Windows O/S and its tasks when the Windows O/S has the tools already for auditing its activities.
    Thursday, May 26, 2016 9:39 PM