how to create an audit trail with Entity framework 5 and MVC 4

Question

I am building an MVC 4 application, using EF 5. I need to do an audit trail, ie log any changes that end users make.

I have asked this question a few times, but haven't really gotten a satisfying answer before. So I am adding a lot more details in hoping to get somewhere..

currently I have multiple repositories

ie

 public class AuditZoneRepository : IAuditZoneRepository     {         private AISDbContext context = new AISDbContext();           public int Save(AuditZone model, ModelStateDictionary modelState)         {             if (model.Id == 0)             {                 context.AuditZones.Add(model);             }             else             {                 var recordToUpdate = context.AuditZones.FirstOrDefault(x => x.Id == model.Id);                 if (recordToUpdate != null)                 {                     recordToUpdate.Description = model.Description;                     recordToUpdate.Valid = model.Valid;                     recordToUpdate.ModifiedDate = DateTime.Now;                 }             }              try             {                 context.SaveChanges();                 return 1;             }             catch (Exception ex)             {                 modelState.AddModelError("", "Database error has occured.  Please try again later");                 return -1;             }         }     }        public class PostcodesRepository : IPostcodesRepository     {         private AISDbContext context = new AISDbContext();           public int Save(Postcodes model, ModelStateDictionary modelState)         {             if (model.Id == 0)             {                 context.Postcodes.Add(model);             }             else             {                 var recordToUpdate = context.Postcodes.FirstOrDefault(x => x.Id == model.Id);                 if (recordToUpdate != null)                 {                     recordToUpdate.Suburb = model.Suburb;                     recordToUpdate.State = model.State;                     recordToUpdate.Postcode = model.Postcode;                     recordToUpdate.AuditZoneId = model.AuditZoneId;                     recordToUpdate.ModifiedDate = DateTime.Now;                 }             }              try             {                 context.SaveChanges();                 return 1;             }             catch (Exception ex)             {                 modelState.AddModelError("", "Database error has occured.  Please try again later");                 return -1;             }         }        } 

Now I know for me to add the code to check to see if there are any changes i need to add it in the try of the save. Before the context.SaveChanges().

But currently I have 10 repos. I don't really want to add code to 10 different places. As this code will do exactly the same thing. I want to somehow have a baseclass that the repos inherit from.

any help? any sample code? any pointers?

would be appreciated. I am sure other people would have done this before

I am mappying my keys, relationships and tables like so

 public class AuditZoneMap : EntityTypeConfiguration<AuditZone>     {         public AuditZoneMap()         {             // Primary Key             HasKey(t => t.Id);               // Properties             Property(t => t.Description)                 .HasMaxLength(100);               // Table & Column Mappings             ToTable("AuditZone");             Property(t => t.Id).HasColumnName("Id");             Property(t => t.Description).HasColumnName("Description");             Property(t => t.Valid).HasColumnName("Valid");                       Property(t => t.CreatedDate).HasColumnName("CreatedDate");             Property(t => t.CreatedBy).HasColumnName("CreatedBy");             Property(t => t.ModifiedDate).HasColumnName("ModifiedDate");             Property(t => t.ModifiedBy).HasColumnName("ModifiedBy");              // Relationships                     HasOptional(t => t.CreatedByUser)                .WithMany(t => t.CreatedByAuditZone)                .HasForeignKey(d => d.CreatedBy);              HasOptional(t => t.ModifiedByUser)                 .WithMany(t => t.ModifiedByAuditZone)                 .HasForeignKey(d => d.ModifiedBy);           }     } 

Answer 1

What I recommend you is to use the ChangeTracker property in EF.

Inside your DBContext.cs you will have this:

public class DBContext : DbContext     {          public DBContext () : base("DatabaseName")         {          }            protected override void OnModelCreating(DbModelBuilder modelBuilder)         {           }          public DbSet<YourPocoModelNameHere > YourPocoModelNameHere { get; set; }            // This is overridden to prevent someone from calling SaveChanges without specifying the user making the change         public override int SaveChanges()         {             throw new InvalidOperationException("User ID must be provided");         }         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 == System.Data.EntityState.Added || p.State == System.Data.EntityState.Deleted || p.State == System.Data.EntityState.Modified))             {                 // For each changed record, get the audit record entries and add them                 foreach (AuditLog x in GetAuditRecordsForChange(ent, userId))                 {                     this.AuditLogs.Add(x);                 }             }              // Call the original SaveChanges(), which will save both the changes made and the audit records             return base.SaveChanges();         }          private List<AuditLog> GetAuditRecordsForChange(DbEntityEntry dbEntry, int userId)         {             List<AuditLog> result = new List<AuditLog>();              DateTime changeTime = DateTime.UtcNow;              // Get the Table() attribute, if one exists             //TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).SingleOrDefault() as TableAttribute;              TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), true).SingleOrDefault() as TableAttribute;              // Get table name (if it has a Table attribute, use that, otherwise get the pluralized name)             string tableName = tableAttr != null ? tableAttr.Name : dbEntry.Entity.GetType().Name;              // Get primary key value (If you have more than one key column, this will need to be adjusted)             var keyNames = dbEntry.Entity.GetType().GetProperties().Where(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).ToList();              string keyName = keyNames[0].Name; //dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).Name;              if (dbEntry.State == System.Data.EntityState.Added)             {                 // For Inserts, just add the whole record                 // If the entity implements IDescribableEntity, use the description from Describe(), otherwise use ToString()                  foreach (string propertyName in dbEntry.CurrentValues.PropertyNames)                 {                     result.Add(new AuditLog()                     {                         AuditLogId = Guid.NewGuid(),                         UserId = userId,                         EventDateUTC = changeTime,                         EventType = "A",    // Added                         TableName = tableName,                         RecordId = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),                         ColumnName = propertyName,                         NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()                     }                             );                 }             }             else if (dbEntry.State == System.Data.EntityState.Deleted)             {                 // Same with deletes, do the whole record, and use either the description from Describe() or ToString()                 result.Add(new AuditLog()                 {                     AuditLogId = Guid.NewGuid(),                     UserId = userId,                     EventDateUTC = changeTime,                     EventType = "D", // Deleted                     TableName = tableName,                     RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),                     ColumnName = "*ALL",                     NewValue = (dbEntry.OriginalValues.ToObject() is IDescribableEntity) ? (dbEntry.OriginalValues.ToObject() as IDescribableEntity).Describe() : dbEntry.OriginalValues.ToObject().ToString()                 }                     );             }             else if (dbEntry.State == System.Data.EntityState.Modified)             {                 foreach (string propertyName in dbEntry.OriginalValues.PropertyNames)                 {                     // For updates, we only want to capture the columns that actually changed                     if (!object.Equals(dbEntry.OriginalValues.GetValue<object>(propertyName), dbEntry.CurrentValues.GetValue<object>(propertyName)))                     {                         result.Add(new AuditLog()                         {                             AuditLogId = Guid.NewGuid(),                             UserId = userId,                             EventDateUTC = changeTime,                             EventType = "M",    // Modified                             TableName = tableName,                             RecordId = dbEntry.OriginalValues.GetValue<object>(keyName).ToString(),                             ColumnName = propertyName,                             OriginalValue = dbEntry.OriginalValues.GetValue<object>(propertyName) == null ? null : dbEntry.OriginalValues.GetValue<object>(propertyName).ToString(),                             NewValue = dbEntry.CurrentValues.GetValue<object>(propertyName) == null ? null : dbEntry.CurrentValues.GetValue<object>(propertyName).ToString()                         }                             );                     }                 }             }             // Otherwise, don't do anything, we don't care about Unchanged or Detached entities              return result;         }       } 

This will use the following table in your DB:

USE [databasename] GO  /****** Object:  Table [dbo].[auditlog]    Script Date: 06/01/2014 05:56:49 p. m. ******/ SET ANSI_NULLS ON GO  SET QUOTED_IDENTIFIER ON GO  SET ANSI_PADDING ON GO  CREATE TABLE [dbo].[auditlog](     [auditlogid] [uniqueidentifier] NOT NULL,     [userid] [int] NOT NULL,     [eventdateutc] [datetime] NOT NULL,     [eventtype] [char](1) NOT NULL,     [tablename] [nvarchar](100) NOT NULL,     [recordid] [nvarchar](100) NOT NULL,     [columnname] [nvarchar](100) NOT NULL,     [originalvalue] [nvarchar](max) NULL,     [newvalue] [nvarchar](max) NULL,  CONSTRAINT [PK_AuditLog] PRIMARY KEY NONCLUSTERED  (     [auditlogid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  GO  SET ANSI_PADDING OFF GO  ALTER TABLE [dbo].[auditlog]  WITH CHECK ADD  CONSTRAINT [FK_auditlog_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO  ALTER TABLE [dbo].[auditlog] CHECK CONSTRAINT [FK_auditlog_users] GO 

With this all set then you will just need to call your dbContext.SaveChanges(here the userId);

Hope this will work for you... I use it in all my applications and works great!

Enjoy it.

---

Full code found here: https://jmdority.wordpress.com/2011/07/20/using-entity-framework-4-1-dbcontext-change-tracking-for-audit-logging/