Snapshot History With Entity Framework

Question

I've been looking at some auditing hooks with Entity Framework. Many of them show old/new value comparisons. This does great for an audit trail but I'm looking to snapshot objects.

For example... Let's say I have an application that manages products. A product has multiple attributes and associated other objects. Let's say I change an object 10 times. Let's also say that's important that I can view screens of those object changes (not an audit trail but what the screen actually looked like in a read only format). What I'm interested in is being able to retrieve the original EF product object (with all of the associated data) for all 10 of those changes (depending on which I want to see)and use that to bind to my screen.

If I'm using SQL Server, what type should I use for the serialized object nowadays (XML, blob, etc)? Does it make sense to do this?

Answer 1

Let's see. You have a requirement to take an object graph and serialize it into the database in a format which will allow you to materialize it later on. I think that there are tools which do exactly this. One of them, it strikes me, is the Entity Framework.

What you want to do is a very common thing. Consider a wiki engine. The wiki needs to have a tip revision that everyone sees, plus back revisions of every document. The wiki also needs to be able to display a back revision in just the same way that a tip revision is displayed. Therefore, the same storage format should be used for both of them.

I would propose that you allow all of your entity types to be versioned. When you edit an entity type, you will edit the tip revision and store a back revision containing the previous values. (The reason you edit the tip revision instead of inserting a new tip is because other objects, which are not currently materialized into an ObjectContext, may contain links to the tip which you would like to preserve as links to the tip, rather than links to the back revision.)

If necessary, you can partition your SQL Server tables so that the back revisions are stored in a different file group. This would allow you to backup the tip revisions and back revisions separately.

Answer 2

First you need to add a set of properties to your tables:

• Version - time of last modification (can also be autoincrementing counter instead of time).
• LastModifiedBy - reference to the user which made last modification (if you store that).

Then you have several options about how to store your version history. You can

1. Create a new table for each of the main tables you want to store history for. That history tables will have all the same fields as main table, but primary and foreign keys will not be enforced. For each foreign key also store Version of referenced entry at the time version was created.

2. OR you can serialize everything interesting about your entity and store all that serialized blobs for all entities you want to version in one global history table (I personally prefer first approach).

How do you fill your history tables? Via update and delete triggers.

• In update trigger for your entity - copy all previous values to the history table. For each foreign key - also copy current Version of referenced entity.
• In delete trigger - basically do the same.

Note that more and more modern systems do NOT really delete anything. They just mark things as deleted. If you would want to follow this pattern (which has several benefits) - instead of deleting add IsDeleted flag to your entities (of course you then have to filter deleted entities out everywhere).

How do you view your history? Just use history table, since it has all the same properties as main table - should not be a problem. But - when expanding foreign keys - ensure that referenced entity Version is the same as you store in your history table. If it's not - you need to go to History table of that referenced entity and grab values there. This way you will always have a snapshot of how entity looked like at THAT moment, including all references.

In addition to all above - you can also restore state of your entity to any previous version.

Note that this implementation, while easy, can consume some space, because it stores snapshot, not only changes being made. If you want to just store changes - in update trigger you can detect what fields has been changed, serialize them and store in global history table. That way you can at least show in user interface what has been changed and by whom (though you might have troubles to reverting to some previous version).

Answer 3

On a project I recently built we used we plugged in to the SaveChanges method in the DbContext class. This gave us access to an instance of the ChangeTracker class. Calling ChangeTracker.Entries() gives you access to a list of DbEntityEntry. DbEntityEntry has the following interesting properties and methods:

• State - is the object newly created, modified or being deleted
• Entity - a copy of the object as it stands
• CurrentValues - an enumeration of the edited valued
• OriginalValues - an enumeration of the original values

We created a set of POCOs for change sets and changes that we could then access through EF. This allowed our users to view field level changes along with dates and responsible users.