Many times our projects require to keep some kind of auditing on database rows for changes and updates, so we can know who and when created a row or did the last update to it.

When we have lots of entities and mappings going up and down through our application layers, sometimes is difficult to know where and how we have to handle this task.

In this article, I’ll show you a good technique to implement database row auditing using Entity Framework. Maybe you’ll find another approaches you might consider better, but this is a clean and single method one, and it’s the one I use in my own projects at work.

For the demonstration, I’m gonna use as base the project I created for my previous article “Entity Framework 6.0 code first & TPT, persistence of polymorphic collections“. (You did read it, didn’t you?).

Clone the repository: https://joanvilarino@bitbucket.org/joanvilarino/entityframework_tpt.git
Download Zip file: https://bitbucket.org/joanvilarino/entityframework_tpt/downloads

Audited Entity

We will add a new BaseAuditedEntity that will inherit from BaseEntity, then we’ll make the rest of the entities inherit from it. This is not mandatory, you could inherit just some of them, I mean, the ones you want to audit in your database.

In our base audit entity, we just create the needed fields to know who and when edited or created the row.

public class BaseAuditedEntity : BaseEntity
{
    public DateTime Created { get; set; }
    public string CreatedBy { get; set; }
    public DateTime? Updated { get; set; }
    public string UpdatedBy { get; set; }
}

We will then change Car and CarPart (our car parts main entity, all the other entities inherit from this one in our project) to inherit from BaseAuditedEntity

public class Car : BaseAuditedEntity
{
    // contents of the class
}

public class CarPart : BaseAuditedEntity
{
    // contents of the class
}

Doing the “magic”

Ok, we’re done with the entities, now how do we fill in the data?

The solution is our Entity Framework context BoundContext. There, we can override the SaveChanges() method to hook in just before the changes we have made to the context get written to the physical database.

Entity Framework‘s DBContext class has a property called ChangeTracker, that contains, besides more stuff, all the entities that are going to be written to the database, including added, updated and deleted rows in a inner collection called Entries.

There is where we are going to hook and check the entities that are going to be written to fill our audit fields, by checking the operation that is going to be performed on every one of them.

public override int SaveChanges()
{

    ChangeTracker.Entries<BaseAuditedEntity>().ToList().ForEach(
        entry =>
        {
            var auditedEntity = entry.Entity;
            if (entry.State == EntityState.Added)
            {
                auditedEntity.Created = DateTime.Now;
                auditedEntity.CreatedBy = GetCurrentUser();
            }
            if (entry.State == EntityState.Modified)
            {
                auditedEntity.Updated = DateTime.Now;
                auditedEntity.UpdatedBy = GetCurrentUser();
            }
        });

    return base.SaveChanges();
}

As you can see, we get every Entry in the collection that is BaseAuditedEntity using the generic form of Entries(), then we check State to see if it was to be Added or Modified (it’s useless to check for Deleted rows), and we fill the appropriate fields in our casted entity.

Finally, we just call the base.SaveChanges() to keep Entity Framework rocking, and that’s all!

Considerations on Current User

Auditing the right username can be a little tricky sometimes. As you see in the example, I’m using a private method called GetCurrentUser() that returns the current Windows user, or the string “Anonymous”.

private string GetCurrentUser()
{
    var currentUser = System.Security.Principal.WindowsIdentity.GetCurrent();
    return (currentUser != null) ? currentUser.Name : "Anonymous";
}

The problem is, most of the times our database or infrastructure’s layer can be in a server running a single user for all the application, in a system or db-tailored account, and it doesn’t know anything about the actual front-end or webservice user that’s calling it.

In those situations, you could just pass-down the a currentUser parameter to your context when calling to SaveChanges() from your Unit Of Work or Repository, and it should be ok.

Thank you

Well this is it for today, if you are still hungry for knowledge, you can always read any of my previous and delightful articles, or just go raw and download any of the demo solutions in my BitBucket repository. Peace!

Follow me

Joan Vilariño

Senior .NET Developer at Ohpen
Developer for more than 25 years and IT, gadget, mobile and electronics enthusiast working and publishing from Barcelona.
Follow me