MVC 5 - Logging inserts and updates in database

Question

I have looked for alot of different approaches to achive this. But I have yet to find a good and simple exmaple on how to do this without alot of 3 party instalations wich focuses on performance logging and debugging.

I am looking for a way to easily log ALL changes to my database and also when new rows are added. I want to have a own table that stores which action in which controller was called / or simply the database table would be enough to track it. And the which fields where updated or added. I am picturing a table something like this:

ID - ACTION/TABLE/METHOD - ID -  TYPE - DETAILS - CREATED BY - TIMESTAMP

 x - TableName/ActionResult/JsonResult/ - ID of the new or updated item - updated or new - details on what have changed or created - user.identity - timestamp

So i can view the log table in each spesific view and i can see the history for that item and which fields where changed etc.

I looked at the bottom suggestion here: How to implement a MVC 4 change log? since my SQL database does not support the SQL Service Broker and I dont really want to start with adding Triggers in SQL.

I am using MVC 5.2 and EF 6.0 so I have looked at the Database.Log property but I really need some guidance on how to set up a good method to achive what I want.

Answer 1

I found a solution i am currently modifying to my needs.

Here is the code:

Overriding SaveChanges Class in

public class ApplicationDbContext : IdentityDbContext<ApplicationUser>

and adding theese methods:

public async Task SaveChangesAsync(string 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))
            {
                // For each changed record, get the audit record entries and add them
                foreach (Log x in GetAuditRecordsForChange(ent, userId))
                {
                    this.Log.Add(x);
                }
            }

            // Call the original SaveChanges(), which will save both the changes made and the audit records
            await base.SaveChangesAsync();
        }

        private List<Log> GetAuditRecordsForChange(DbEntityEntry dbEntry, string userId)
        {
            List<Log> result = new List<Log>();

            DateTime changeTime = DateTime.Now;

            // Get the Table() attribute, if one exists
            TableAttribute tableAttr = dbEntry.Entity.GetType().GetCustomAttributes(typeof(TableAttribute), false).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)
            string keyName = dbEntry.Entity.GetType().GetProperties().Single(p => p.GetCustomAttributes(typeof(KeyAttribute), false).Count() > 0).Name;

            if (dbEntry.State == EntityState.Added)
            {
                // For Inserts, just add the whole record
                // If the entity implements IDescribableEntity, use the description from Describe(), otherwise use ToString()
                result.Add(new Log()
                {
                    LogID = Guid.NewGuid(),
                    EventType = "A", // Added
                    TableName = tableName,
                    RecordID = dbEntry.CurrentValues.GetValue<object>(keyName).ToString(),  // Again, adjust this if you have a multi-column key
                    ColumnName = "*ALL",    // Or make it nullable, whatever you want
                    NewValue = (dbEntry.CurrentValues.ToObject() is IDescribableEntity) ? (dbEntry.CurrentValues.ToObject() as IDescribableEntity).Describe() : dbEntry.CurrentValues.ToObject().ToString(),
                    Created_by = userId,
                    Created_date = changeTime
                }
                    );
            }
            else if (dbEntry.State == EntityState.Deleted)
            {
                // Same with deletes, do the whole record, and use either the description from Describe() or ToString()
                result.Add(new Log()
                {
                    LogID = Guid.NewGuid(),
                    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(),
                    Created_by = userId,
                    Created_date = changeTime
                }
                    );
            }
            else if (dbEntry.State == 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 Log()
                        {
                            LogID = Guid.NewGuid(),
                            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(),
                            Created_by = userId,
                            Created_date = changeTime
                        }
                            );
                    }
                }
            }
            // Otherwise, don't do anything, we don't care about Unchanged or Detached entities

            return result;
        }

        public DbSet<Log> Log { get; set; }

And here is the Log class

[Table("N_Log")]
public class Log
{
    [Key]
    public Guid LogID { get; set; }

    [Required]
    public string EventType { get; set; }

    [Required]
    public string TableName { get; set; }

    public string ActionID { get; set; }

    [Required]
    public string RecordID { get; set; }

    [Required]
    public string ColumnName { get; set; }

    public string OriginalValue { get; set; }

    public string NewValue { get; set; }

    [Required]
    public string Created_by { get; set; }

    [Required]
    public DateTime Created_date { get; set; }
}