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. Rightnow 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

Yes but there is a problem with the auto generated identity from the db if you do it in the C# code, especially if you wire up the event handler for the SavingChanges in the entity model.

Can anyone suggest how to go get the @@identity after the insert and attach to the audit log.