Auditing in MVC EF Project

Recently I worked on a requirement to audit the web application for CRUD operations. Yes, all of CRUD that means read operation too. I had a MVC web application which used Entity Framework DB first option and was using Azure AD Authentication. The complete set of requirements (mentioned below) was bit different than normal auditing.

  1. Log one row per table change instead of per property (column) change.
  2. Log for read operation too
  3. Generate a checksum and log it to along with the audit trail
  4. Apart from that, have to log table name, logged in user name, timestamp and other generic stuff

We decided to just store the new (updated) values in the audit trail instead of both old and new ones. If needed, we can get history of that object from audit table so not storing old values was not going to be an issue.

To achieve this, I decided to overwrite the SaveChanges method of DbContext. Entity Framework DB first creates partial classes for everything including the DbContext. I just used partial classes and overrode the SaveChanges method. Before jumping to the code, below is the approach I followed for all of the above points, respectively:

  1. I decided to create JSON of the updated row by using JSON.NET library. I could have used XML too but the issue with XML is, it fails on out of the box generated DB classes due to circular references and use of ICollection and virtual properties. To do this with XML, I would have to create POCO objects, which was just increasing the development time and effort. Hence I decided to use JSON.NET to create a valid json of updated row and store it in varchar(max) field of audit table in SQL. You can go ahead with XML too
  2. By overriding SaveChanges I can audit for create, update and delete (CUD) operation but not for read. Therefore, to simplify the process I just manually added audit rows for every call to functions where I fetch any table’s details. It was straightforward and easy but there was a catch. As I was adding audit row while reading an object, SaveChanges was called and hence for every read operation, there was an entry in audit table which denotes that an entry is added in the audit table. Basically the audit table was getting audited which I did not want. Hence, I have to exclude the “Audit” table manually from code
  3. This was easy to implement. I just combined the complete row using StringBuilder and generated checksum using MD5. You can use any other algorithm too. Besides that I converted the generated checksum to hex to store it in DB, you can use Base64 too
  4. I was using three tier architecture in my project with web layer, service layer and one data layer. Other things are straightforward but for user id I did not want to store complete email address for every row. That’s why, what I did was, I entered logged in user’s email id in database once someone logs in to the application for the first time and at the same time stored that table’s primary key as customĀ claim. I then read this claim value and use it for audit purpose
  5. Last thing, as I need to audit create operations too and their id is not generated until the call to SaveChanges, I have to collect create entities in different collection and audit them after an initial call to SaveChangesĀ 

So below is the code to achieve all this:

public partial class CustomDbContext: DbContext
public override int SaveChanges()
// Get all Added/Deleted/Modified entities (not Unmodified or Detached)
//you can get updated and deleted in same collection. I am doing so for ease of purpose
//getting added entries in different collection to fetch their generated ids and then log them
var addedEntries = ChangeTracker.Entries().Where(e => e.State == EntityState.Added).ToList();
var updatedEntries = ChangeTracker.Entries().Where(e => e.State == EntityState.Modified).ToList();
var deletedEntries = ChangeTracker.Entries().Where(e => e.State == EntityState.Deleted).ToList();
foreach (var entry in updatedEntries)
foreach (var entry in deletedEntries)
int changes = base.SaveChanges();
foreach (var entry in addedEntries)
return changes;
/// <summary>
/// code to enter values in audit table
/// </summary>
/// <param name="entry"></param>
/// <param name="auditType"></param>
private void ApplyAuditLog(DbEntityEntry entry)
//get table name of entry
string tableName = GetTableName(entry);
//exlude audit table from getting audited
if (!tableName.Equals("Audit", StringComparison.OrdinalIgnoreCase))
//code to get primary key (id) of object which is updated
var objectStateEntry = ((IObjectContextAdapter)this).ObjectContext.ObjectStateManager.GetObjectStateEntry(entry.Entity);
int primaryKeyOfEntity = (int)objectStateEntry.EntityKey.EntityKeyValues[0].Value;
DateTime currentDateTime = DateTime.Now;
//code to convert the updated/added/deleted entry to json using newtonsoft json nuget package
string serializedJson = JsonConvert.SerializeObject(entry.CurrentValues.ToObject(), Formatting.Indented, new JsonSerializerSettings
//below code ignores the circular references. Main reason XML is not used as this was so wasy in JSON
ReferenceLoopHandling = ReferenceLoopHandling.Ignore
Audit auditTrail = new Audit()
TimeStamp = currentDateTime,
AuthenticatedUser = {/* code to log authenticated user */ },
LinkedID = primaryKeyOfEntity,
AuditType = {/*create/update/delete/or read. Write your own logic here */ },
TableName = tableName,
NewValue = serializedJson
//you can write code here to generated checksum using algo like SHA1, MD5 and then update it in audit trail
//string checksumValue = GetChecksumValueFromTrail(auditTrail);
//write code to add entry in audit table here like below
/// <summary>
/// method to fetch table name of updated entity
/// </summary>
/// <param name="dbEntityEntry"></param>
/// <returns></returns>
private string GetTableName(DbEntityEntry dbEntityEntry)
ObjectContext objectContext = ((IObjectContextAdapter)this).ObjectContext;
Type entityType = dbEntityEntry.Entity.GetType();
if (entityType.BaseType != null && entityType.Namespace == "System.Data.Entity.DynamicProxies")
entityType = entityType.BaseType;
string entityTypeName = entityType.Name;
EntityContainer container =
objectContext.MetadataWorkspace.GetEntityContainer(objectContext.DefaultContainerName, DataSpace.CSpace);
string entitySetName = (from meta in container.BaseEntitySets
where meta.ElementType.Name == entityTypeName
select meta.Name).First();
return entitySetName;