Thursday, March 3, 2011

Automatic Auditing of Changes using Domain Service and Entity Framework

A special thanks to Fredrik Normen for providing the basis for my code. His great article is here. For a better understanding of the basic code, please read Fredrik’s implementation first. He does a good job of walking you through his code.

With that said I have changed most of his code and added logging to a database table using another Entity Framework (EF) model. You can download my updates to his code under the Source Code section.

Please note that only changes made through the Domain Service will be logged. If you don’t use a Domain Service to modify all your data that you want logged this solution is probably not for you. However, if you use a Domain Service and Entity Framework this is a pretty robust solution for adding auditing to your ASP.NET web application in minutes. I am using this with my Dynamic Data web application and it works great!

The code works because the Domain Service has a concept of a ChangeSet. When you save (update or insert or delete) a ChangeSet is created that basically shows what the original entities look like and what the current entities look like. This is all standard Domain Service stuff. Nothing I did. However, what I do is look at the original and current values and create EntityChangeInfo objects and PropertyChangeInfo objects. These then get written to the database where there is a record in the database for each field. This is very verbose for Inserts and for Deletes, but just write for Update. If you don’t need insert and delete auditing, you can easily comment them out.

To implement my solution, here are the basic steps (from my memory) to add auditing to you application.

  1. Download and use the AuditLog-Create.sql to create an AuditLog table in a database.
  2. Create an Auditing directory in your Services directory (if you have one).
  3. Download and copy the AuditLogger.cs, ChangeSetHelper.cs, EntityChangeInfo.cs, PropertyChangeInfo.cs to the Auditing directory you just created.
  4. In your Models directory (if you have one) create a new ADO.NET Entity Framework Data Model that points to the database where you created the AuditLog table. Call the model AuditModel. The only table you want to add to it is the AuditLog table. You don’t need a Domain Services or anything else for this. We will reference it directly.
  5. Download and copy the snippet from the DomainService-PersistChangeSetSnippet.cs and paste it in your domain service file that you already should have in your project. This is the file for the domain service that you want to audit. If you don’t want INSERT or DELETE auditing you can comment out the appropriate line in this snippet.

That is it. You should now be able to run your application, make some changes and see the changes show up in the AuditLog table.

You can then execute a query something like this to see the history of a particular record in a table.

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Insert'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Update'

select * from auditlog where EntityName = 'Activity' and EntityPKValues = '46' and operation = 'Delete'

My Comments

You may notice that I log my times in UTC and that the date/time is the same for a given update, insert, or delete. In other words, when an update is written to the database, multiple records are created (one for each column that changes), but the CreatedDateTimeUTC field is stamped with a ONE datetime.

When values are inserted, the OriginalValue column will ALWAYS blanks. When a record is deleted, the CurrentValue column will ALWAYS be blank.

If you have a properties that you have added to an entity they will have an initial value and the computed value in some cases. If this is the case for you, you may want to hard code these property names the ChagneSetHelper.GetPropertiesChangeInfo method as properties to not log. Just like I have done for the EntityState property. Up to you.

Source Code

AuditLog-Create.sql

AuditLogger.cs

ChangeSetHelper.cs

EntityChangeInfo.cs

PropertyChangeInfo.cs

DomainService-PersistChangeSetSnippet.cs

No comments: