Click here to Skip to main content
15,860,861 members
Articles / Database Development / SQL Server

Implementing Audit Trail using Entity Framework - Part 1

Rate me:
Please Sign up or sign in to vote.
4.91/5 (52 votes)
27 Mar 2009CPOL3 min read 320K   5.6K   120   99
Implementing Audit Trail using Entity Framework's caching entries
This is the first part of a two part series on how to do audit trail implementation that is capable of rolling back to a certain period. Using the Entity Framework's caching manager, I will find out the object that is currently changed or added or deleted and resides in EF cache as Object state entry.

Introduction

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

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

Using the Code

First, I make table audit trail in database:

ImplAudingTrailUsingEFP1/DataBaseDbAudit.JPG

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

ImplAudingTrailUsingEFP1/dbAuditEF.JPG

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:

C#
Context.SavingChanges +=new EventHandler(Context_SavingChanges);

In the sample program, I have done it with writing partial class:

C#
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 that are going to save in DB. Here, it takes each entry from EF cache of state - Added or Deleted or Modified and calls a factory method to produce audit trail object.

C#
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 a Factory method to create dbaudit object. Especially for Modify state, it keeps the modified properties and serialized as XML. So using these fields, you can easily show the changes of modified object with doing any comparison of old and new data.

C#
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. In Entity Framework, each entry holds all change definition. First, I make a clone of the current object. Using entry.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 do XML serialized and return back XML string.

C#
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 original value 
                setterValue = entry.OriginalValues[propName];
            }
            else
            {
                //Get original value 
                setterValue = entry.CurrentValues[propName];
            }
            //Find property to update 
            PropertyInfo propInfo = target.GetType().GetProperty(propName);
            //update property with ribald 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 found in MSDN forum post (Thanks to Patrick Magee).

C#
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.

History

  • 27th March, 2009: Initial version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Team Leader PracticePRO Software Systems Inc
United States United States
In my childhood, my uncle has shown me how to see the cloud in a close look and I understand that one can draw some elements of the Earth in the sky-canvas if he/she wants to. After that the cloud becomes closer to me and It teaches me one thing that, a deeper-look to something will give you some clues to draw your imagination. You can able to see that one which you have build-up in your mind.

Years past, I have started my career as a software engineer and has been looking for passion in my coding and development which I should be to enjoy my profession and has started asking myself- 'am I doing any engineering here?!' Is my code becoming that thing which I have designed in my mind? So to find that answer I have tried that old solution here... I have decided to come closer to my code and start analyzing them. And it is really working for me and at least it gives me the confidence that I can build something that I really want to. I can draw my thinking there through my code and can build-up my vision that I have designed in my mind. It also helps me to think out of the box, solve each problems by making blocks and make me careful on each steps.

• Morshed's Technical Blog site: http://morshedanwar.wordpress.com/

• Morshed's Technical articles those are published in Codeproject site: http://www.codeproject.com/script/Articles/MemberArticles.aspx?amid=2992452

• Morshed's Linkedin profile: http://www.linkedin.com/in/morshedanwar

• Morshed's Facebook Profile : http://www.facebook.com/morshed.pulok

Beside all these I like to do - photography and music. Here is my Flickr photos : http://www.flickr.com/photos/morshed_anwar/

Comments and Discussions

 
QuestionSavingChanges event handler got removed when I updated model from database Pin
Member 1022678722-Dec-16 22:54
Member 1022678722-Dec-16 22:54 
QuestionPlease share db script with schema and data Pin
bharatmittal13-Apr-16 1:02
bharatmittal13-Apr-16 1:02 
AnswerRe: Please share db script with schema and data Pin
Rod at work25-May-16 6:24
Rod at work25-May-16 6:24 
QuestionDo I need to add over ride code to all my entities Pin
Member 1159775213-Apr-15 10:07
Member 1159775213-Apr-15 10:07 
QuestionHow you extend this to support tracking only selected entity and selected properties of that entity?? Pin
Nirosh29-Jan-14 18:47
professionalNirosh29-Jan-14 18:47 
AnswerRe: How you extend this to support tracking only selected entity and selected properties of that entity?? Pin
Morshed Anwar30-Jan-14 4:40
professionalMorshed Anwar30-Jan-14 4:40 
Questionentry.OriginalValues and CurrentValues are the same on audit Pin
Sean Brogan30-Sep-13 0:43
Sean Brogan30-Sep-13 0:43 
AnswerRe: entry.OriginalValues and CurrentValues are the same on audit Pin
Morshed Anwar30-Sep-13 1:35
professionalMorshed Anwar30-Sep-13 1:35 
GeneralRe: entry.OriginalValues and CurrentValues are the same on audit Pin
Sean Brogan30-Sep-13 2:46
Sean Brogan30-Sep-13 2:46 
GeneralRe: entry.OriginalValues and CurrentValues are the same on audit Pin
Morshed Anwar30-Sep-13 4:21
professionalMorshed Anwar30-Sep-13 4:21 
GeneralRe: entry.OriginalValues and CurrentValues are the same on audit Pin
Sean Brogan30-Sep-13 21:26
Sean Brogan30-Sep-13 21:26 
GeneralRe: entry.OriginalValues and CurrentValues are the same on audit Pin
Morshed Anwar1-Oct-13 1:38
professionalMorshed Anwar1-Oct-13 1:38 
QuestionXMLWriter not support base64 encoded data Pin
Member 102571856-Sep-13 3:09
Member 102571856-Sep-13 3:09 
AnswerRe: XMLWriter not support base64 encoded data Pin
Morshed Anwar6-Sep-13 4:42
professionalMorshed Anwar6-Sep-13 4:42 
GeneralMy vote of 5 Pin
Аslam Iqbal23-Apr-13 20:58
professionalАslam Iqbal23-Apr-13 20:58 
QuestionFor EntityState.Deleted not working Pin
Member 998096417-Apr-13 23:03
Member 998096417-Apr-13 23:03 
QuestionSaving changes event Pin
jportelas31-Dec-12 6:38
jportelas31-Dec-12 6:38 
AnswerRe: Saving changes event Pin
Morshed Anwar31-Dec-12 21:59
professionalMorshed Anwar31-Dec-12 21:59 
GeneralMy vote of 5 Pin
Lee Keel21-Nov-12 7:45
Lee Keel21-Nov-12 7:45 
GeneralRe: My vote of 5 Pin
Morshed Anwar21-Nov-12 11:07
professionalMorshed Anwar21-Nov-12 11:07 
BugCloneEntity throwing Out-of-memory error Pin
Lee Keel19-Nov-12 14:09
Lee Keel19-Nov-12 14:09 
GeneralRe: CloneEntity throwing Out-of-memory error Pin
Morshed Anwar19-Nov-12 22:29
professionalMorshed Anwar19-Nov-12 22:29 
GeneralRe: CloneEntity throwing Out-of-memory error Pin
Lee Keel20-Nov-12 6:55
Lee Keel20-Nov-12 6:55 
GeneralRe: CloneEntity throwing Out-of-memory error Pin
Morshed Anwar20-Nov-12 22:47
professionalMorshed Anwar20-Nov-12 22:47 
GeneralRe: CloneEntity throwing Out-of-memory error Pin
Lee Keel21-Nov-12 7:43
Lee Keel21-Nov-12 7:43 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.