Implementing Audit Trail using Ado.net Entity Framework : Part -1

Posted on March 26, 2009. Filed under: Entity Framework |


Please Note: For Audit trail with DbContext (Entity Framework 4.1 and above) I have written about generating Audit log in following article-
http://www.codeproject.com/Articles/363501/Repository-pattern-with-Entity-Framework-Code-Firs
 
https://morshedanwar.wordpress.com/2012/10/03/design-data-access-with-entity-framework-code-first-in-composable-service-end/
 

Entity framework keeps track for those entire objects and relationships which have been deleted, added and modified in the container. EF keeps the state of the object and holds the necessary change-information and all these track information for each object or relationship resides as “objectStateEntry”. Using “ObjectStateManager” one can access all these change-information like object-state (added/modified/deleted), modified properties, original and current values and can easily do audit trail for those objects. To get the Rollback feature from that audit trail we have to consider some issues.  We have to maintain the order of entity graph while insertion and deletion. That means root entity has been inserted before the children and during deletion we have to make it reverse. The most important issue is that we have to make sure that audit trail entry will be inserted according this order.

 Here I am going to talk about audit trail implementation that’s capable to rollback to a certain period. To make such implementation I am going to use the Entity framework’s caching Management that is called as “ObjectStateManager”.Using this Manager I will be capable to find out the object that is currently changed or added or deleted and resides in EF cache as Object state entry. In part 1, I just going to talk about creating audit trail objects using the object state entry. In Second Part I will talk about roll back feature of this audit trial.

 First I make table audit trail in database

 DataBaseDbAudit

For this table, I am going to make an Entity set in my conceptual level as

 dbAuditEF

In Entity Framework, to save my all changes into Db we have call “Context.SaveChanges()” and This Context is a container that has been inherited  from “ObjectContext” Class.To create the Audit trail  Objects for each “Modified/Added/Deleted”I am going to catch the event

Context.SavingChanges +=new EventHandler(Context_SavingChanges);

In sample program I have done it with writing partial class of

public partial class AdventureWorksEntities

    {

partial void OnContextCreated()

        {

            this.SavingChanges += new EventHandler(AdventureWorksEntities_SavingChanges);

        }

        void AdventureWorksEntities_SavingChanges(object sender, EventArgs e)

        {

        }

So in my AdventureWorksEntities_SavingChanges method I am going to create all “dbaudit” objects those are going to save in DB.Here its takes each entry from EF cache of state- Added or Deleted or Modified and call a factory method to produce audit trail object.

    public partial class AdventureWorksEntities

    {

        public string UserName { get; set; }

        List<DBAudit> auditTrailList = new List<DBAudit>();

        public enum AuditActions

        {

            I,

            U,

            D

        }

        partial void OnContextCreated()

        {

            this.SavingChanges += new EventHandler(AdventureWorksEntities_SavingChanges);

        }

        void AdventureWorksEntities_SavingChanges(object sender, EventArgs e)

        {

            IEnumerable<ObjectStateEntry> changes = this.ObjectStateManager.GetObjectStateEntries(EntityState.Added | EntityState.Deleted | EntityState.Modified);

            foreach (ObjectStateEntry stateEntryEntity in changes)

            {

                if (!stateEntryEntity.IsRelationship &&

                        stateEntryEntity.Entity != null &&

                            !(stateEntryEntity.Entity is DBAudit))

                {//is a normal entry, not a relationship

                    DBAudit audit = this.AuditTrailFactory(stateEntryEntity, UserName);

                    auditTrailList.Add(audit);

                }

            }

            if (auditTrailList.Count > 0)

            {

                foreach (var audit in auditTrailList)

                {//add all audits

                    this.AddToDBAudit(audit);

                }

            }

        }

And here “AuditTrailFactory” is Factory method to create “dbaudit” object. In Entity Framework each entry holds all change definition. Especially for Modify state it keeps the modified properties. I have collected all these properties and serialized them as XML. Using this field you can easily show the changes of modified object with doing any comparison of old and new data. 

        private DBAudit AuditTrailFactory(ObjectStateEntry entry, string UserName)

        {

            DBAudit audit = new DBAudit();

            audit.AuditId = Guid.NewGuid().ToString();

            audit.RevisionStamp = DateTime.Now;

            audit.TableName = entry.EntitySet.Name;

            audit.UserName = UserName;

            if (entry.State == EntityState.Added)

            {//entry is Added

                audit.NewData = GetEntryValueInString(entry, false);

                audit.Actions = AuditActions.I.ToString();

            }

            else if (entry.State == EntityState.Deleted)

            {//entry in deleted

                audit.OldData = GetEntryValueInString(entry, true);

                audit.Actions = AuditActions.D.ToString();

            }

            else

            {//entry is modified

                audit.OldData = GetEntryValueInString(entry, true);

                audit.NewData = GetEntryValueInString(entry, false);

                audit.Actions = AuditActions.U.ToString();

                IEnumerable<string> modifiedProperties = entry.GetModifiedProperties();

                //assing collection of mismatched Columns name as serialized string

                audit.ChangedColumns = XMLSerializationHelper.XmlSerialize(modifiedProperties.ToArray());

            }

            return audit;

        }

Here “GetEntryValueInString” is for creating XML text of Previous or modified object. First I made a clone the current object. Usingentry.GetModifiedProperties()” I can get only modified properties of an object and Using “OriginalValues” and “CurrentValues” I can build myself the old data and new data. Factory has told me what that wants – old or new. At the End I have done XML serialized and return XML string. 

        private string GetEntryValueInString(ObjectStateEntry entry, bool isOrginal)

        {

            if (entry.Entity is EntityObject)

            {

                object target = CloneEntity((EntityObject)entry.Entity);

                foreach (string propName in entry.GetModifiedProperties())

                {

                    object setterValue = null;

                    if (isOrginal)

                    {

                        //Get orginal value

                        setterValue = entry.OriginalValues[propName];

                    }

                    else

                    {

                        //Get orginal value

                        setterValue = entry.CurrentValues[propName];

                    }

                    //Find property to update

                    PropertyInfo propInfo = target.GetType().GetProperty(propName);

                    //update property with orgibal value

                    if (setterValue == DBNull.Value)

                    {//

                        setterValue = null;

                    }

                    propInfo.SetValue(target, setterValue, null);

                }//end foreach

                XmlSerializer formatter = new XmlSerializer(target.GetType());

                XDocument document = new XDocument();

                using (XmlWriter xmlWriter = document.CreateWriter())

                {

                    formatter.Serialize(xmlWriter, target);

                }

                return document.Root.ToString();

            }

            return null;

        }

To clone the entity I have used the method which I have found in MSDN forum Post (Thanx Patrick Magee J )

        public EntityObject CloneEntity(EntityObject obj)

        {

            DataContractSerializer dcSer = new DataContractSerializer(obj.GetType());

            MemoryStream memoryStream = new MemoryStream();

            dcSer.WriteObject(memoryStream, obj);

            memoryStream.Position = 0;

            EntityObject newObject = (EntityObject)dcSer.ReadObject(memoryStream);

            return newObject;

        }

That is all for Part-1 where I just create the Audit trail objects for each CUD operation.

You can get the SourceCode and the article also in codeproject site  –   http://www.codeproject.com/KB/database/ImplAudingTrailUsingEFP1.aspx

Advertisements

Make a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

14 Responses to “Implementing Audit Trail using Ado.net Entity Framework : Part -1”

RSS Feed for Morshed Anwar's Blog Comments RSS Feed

Won’t added entities that use an identity as the key serialize the temporary key to DBAudit?

Hemma , as u said – Store update, insert, or delete statement affected an unexpected number of rows (0) .Did u get any excepceptionlike it has already added into the list? if that so then u can try this-

if (null != auditTrailList)
{
foreach (var audit in auditTrailList)
{
ObjectStateEntry _entry;
if (!this.ObjectStateManager.TryGetObjectStateEntry(audit, out _entry))
{
//add into the audit list
this.AddToDBAudit(audit);
}
}
}//end if

and one think that I must have to say , U must ensure that audit will be done in master detail order ….I mean to say master table will be inserted first and then detail…..you can use creation time to maintain this as I did …..but If children and master in created at same time then Implement your own EquilityComparer to the objectStatetEntry while creating audit. Very soon I am going to upload a blog on this EquilityComparer which based on Parent-Child relationship of entity models. During roll back U just need to sort the entities in desending oder with CreationTime as I did.(named as RevistionStamp in my sample Model).

We want to log the data when IsRelationship changes.

we are trying to use your framework. but getting the xml serialization error. reason is code is trying to serialize the image/byte[]. And we don’t want serialize the image data. and also can’t modify out generated EDM file.

You can try it in binary serialization –
public static string GetEntryValueInString(ObjectStateEntry entry, bool isOrginal)
{
if (entry.Entity is EntityObject)
{
object target = CloneEntity((EntityObject)entry.Entity);
foreach (string propName in entry.GetModifiedProperties())
{
object setterValue = null;
if (isOrginal)
{
//Get orginal value
setterValue = entry.OriginalValues[propName];
}
else
{
//Get orginal value
setterValue = entry.CurrentValues[propName];
}
//Find property to update
PropertyInfo propInfo = target.GetType().GetProperty(propName);
//update property with orgibal value
if (setterValue == DBNull.Value)
{//
setterValue = null;
}
propInfo.SetValue(target, setterValue, null);
}//end foreach

DataContractSerializer dcSer = new DataContractSerializer(target.GetType());
MemoryStream memoryStream = new MemoryStream();
dcSer.WriteObject(memoryStream, target);
byte[] b = memoryStream.ToArray();
return Convert.ToBase64String(b);
}
return null;
}
and then during rollback , change the deserialization method (in place of GetAuditObjectFromXML()) –
public static object GetAuditObjectFromString(string ObjectInString, string typeName)
{
////get the Type
Type entityType = Assembly.GetExecutingAssembly().GetType(“ImplAuditTrailUsingEF.” + typeName);
DataContractSerializer dcSer = new DataContractSerializer(entityType);
byte[] b = Convert.FromBase64String(ObjectInString);
MemoryStream memoryStream = new MemoryStream(b);
memoryStream.Position = 0;
EntityObject newObject = (EntityObject)dcSer.ReadObject(memoryStream);
return newObject;
}
I think it will be worked.

“We want to log the data when IsRelationship changes.”

I want to do the same. Any suggestions?

Is there any way that we can add the entities that use identity for primary key, and get the real identity value and not the temporary key?

Thank You.

Hello Martin ..Sorry for the late reply. I didn’t understand what u are willing to do. For Identity column, all you have to do is that tou have add the audit trial entries after the calling of saveChange() method. Currently it creates the audit trial entries just before the saveChange() method via OnSavechange Event. If you are using any transaction , then you have to do this after thansaction has been commited. Hope it will be helpful for you.

Your implementation is not valid when optimistic concurrency is implemented, since multiple log records are inserted if ClientWins is selected with Refresh.

Thanks for the code and article. I implemented your method (without modification) but I do not get any updated properties when I insert a new record. It goes into the _SaveChanges method and shows that EntityState is Added, but the GetModifiedProperties() method does not return anything!! Am I doing something wrong?

GetModifiedProperties() is for the entries where EntityState === Modified . Sorry for the late reply

Awesome, great post. Wonder if you could do something similar but for selftracking pocos

I was visiting your article on codeproject and being the good self tester I am I noticed that I had access to “RollBack’ your article. I hit the rollback button and viola, it rolled it back. I reported this bug to code project, but you might want to also follow up and report and fix whatever got broken. My report bug post is here: http://www.codeproject.com/Messages/4161997/Site-allowed-me-to-Rollback-changes-to-another-art.aspx

BTW Sorry for any problems this caused, it is on CodeProject now tofix this problem. I am not to my knowledge an editor for thier site.


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d bloggers like this: