LINQ to SQL object versioning

Question

I'm trying to create a LINQ to SQL class that represents the "latest" version of itself.

Right now, the table that this entity represents has a single auto-incrementing ID, and I was thinking that I would add a version number to the primary key. I've never done anything like this, so I'm not sure how to proceed. I would like to be able to abstract the idea of the object's version away from whoever is using it. In other words, you have an instance of this entity that represents the most current version, and whenever any changes are submitted, a new copy of the object is stored with an incremented version number.

How should I proceed with this?

Answer 1

The best way to proceed is to stop and seriously rethink your approach.

If you are going to keep different versions of the "object" around, then you are better off serializing it into an xml format and storing that in an XML column with a field for the version number.

There are serious considerations when trying to maintain versioned data in sql server revolving around application maintenance.

UPDATE per comment:

Those considerations include: the inability to remove a field or change the data type of a field in future "versions". New fields are required to be nullable or, at the very least, have a default value stored in the DB for them. As such you will not be able to use them in a unique index or as part of the primary keys.

In short, the only thing your application can do is expand. Provided the expansion can be ignored by previous layers of code.

This is the classic problem of Backwards Compatibility which desktop software makers have struggled with for years. And is the reason you might want to stay away from it.

Answer 2

If you can avoid keeping a history, do. It's a pain.

If a complete history is unavoidable (regulated financial and medical data or the like), consider adding history tables. Use a trigger to 'version' into the history tables. That way, you're not dependent on your application to ensure a version is recorded - all inserts/updates/deletes are captured regardless of the source.

If your app needs to interact with historical data, make sure it's readonly. There's no sense capturing transaction histories if someone can simply change them.

If your concern is concurrent updates, consider using a record change timestamp. When both User A and User B view a record at noon, they fetch the record's timestamp. When User A updates the record, her timestamp matches the record's so the update goes through and the timestamp is updated as well. When User B updates the record five minutes later, his timestamp doesn't match the record's so he's warned that the record has changed since he last viewed it. Maybe it's automatically reloaded...

Whatever you decide, I would avoid inter-mingling current and historic data.

---

Trigger resources per comments:

• MSDN
• A SQL Team Introduction
• Stackoverflow's Jon Galloway describes a general data-change logging trigger

The keys to an auditing trigger are the virtual tables 'inserted' and 'deleted'. These tables contain the rows effected by an INSERT, UPDATE, or DELETE. You can use them to audit changes. Something like:

CREATE TRIGGER tr_TheTrigger
ON [YourTable]
FOR INSERT, UPDATE, DELETE 
AS
    IF EXISTS(SELECT * FROM inserted)
    BEGIN
        --this is an insert or update
        --your actual action will vary but something like this
        INSERT INTO [YourTable_Audit]
            SELECT * FROM inserted
    END
    IF EXISTS(SELECT * FROM deleted)
    BEGIN
        --this is a delete, mark [YourTable_Audit] as required
    END
GO