Auditing in Entity Framework

Question

After going through Entity Framework I have a couple of questions on implementing auditing in Entity Framework.

I want to store each column values that is created or updated to a different audit table.

1. Right now I am calling SaveChanges(false) to save the records in the DB(still the changes in context is not reset). Then get the added | modified records and loop through the GetObjectStateEntries. But don't know how to get the values of the columns where their values are filled by stored proc. ie, createdate, modifieddate etc.

2. Below is the sample code I am working on it.

   // Get the changed entires( ie, records)
   IEnumerable<ObjectStateEntry> changes = context.ObjectStateManager.GetObjectStateEntries(EntityState.Modified);
   
   // Iterate each ObjectStateEntry( for each record in the update/modified collection)
   foreach (ObjectStateEntry entry in changes)
   {
       // Iterate the columns in each record and get thier old and new value respectively
       foreach (var columnName in entry.GetModifiedProperties())
       {
           string oldValue = entry.OriginalValues[columnName].ToString();
           string newValue = entry.CurrentValues[columnName].ToString();
   
           // Do Some Auditing by sending entityname, columnname, oldvalue, newvalue
       }
   }
   
   changes = context.ObjectStateManager.GetObjectStateEntries(EntityState.Added);
   
   foreach (ObjectStateEntry entry in changes)
   {
       if (entry.IsRelationship) continue;
       var columnNames = (from p in entry.EntitySet.ElementType.Members
                          select p.Name).ToList();
   
       foreach (var columnName in columnNames)
       {
           string newValue = entry.CurrentValues[columnName].ToString();
   
           // Do Some Auditing by sending entityname, columnname, value
       }
   }
   

Answer 1

Here you have two basic options:

• Do it at the database level
• Do it in the c# code

Doing it at the data base level, means using triggers. In that case there is no difference if you are using enterprise library or another data access technology.

To do it in the C# code you would add a log table to your datamodel, and write the changes to the log table. When you do a save changes both the changes to the data and the information which you wrote to the log table would be saved.

Answer 2

Are you inserting the new record using a stored proc? If not (i.e. you are newing up an object, setting values, inserting on submit and then saving changes the new object id will be automatically loaded into the id property of the object you created. If you are using a stored proc to do the insert then you need to return the @@IDENTITY from the proc as a return value.

EX:

StoreDateContext db = new StoreDataContext(connString);
Product p = new Product();
p.Name = "Hello Kitty Back Scratcher";
p.CategoryId = 5;
db.Products.Add(p);
try
{
   db.SaveChanges();
   //p.Id is now set
   return p.Id;
}
finally
{
   db.Dispose;
}