Linq Table Attach() based on timestamp or row version

In a previous post here, I showed an example of using the Attach() method in conjunction with a Timestamp column in your database table.  In listing options that are supported, Microsoft's documentation states: "Optimistic concurrency based on timestamps or RowVersion numbers."  So what are some alternatives to using a Timestamp column in your SQL Server database?  It turns out, this is pretty simple.  Two other alternatives are using a DateTime or a unique identifier column.

DateTime Last Updated

The key here is to create a LastUpdated DateTime column with a default value of getdate() and an AFTER UPDATE trigger which inserts the current getdate() any time there is a modification.

  
CREATE TABLE Contacts(  
 ContactID int IDENTITY(1,1) NOT NULL,
 FirstName varchar(50),
 LastName varchar(50),
 LastUpdated datetime NOT NULL default (getdate()),
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)
GO

CREATE TRIGGER trig_ContactsVersion  
ON Contacts  
AFTER UPDATE  
AS  
BEGIN  
    UPDATE    Contacts
    SET    LastUpdated = getdate()
    WHERE    ContactID IN (SELECT ContactID FROM inserted);
END;  

And the corresponding properties must be configured:

 

Unique Identifier

The key here is to create a Version unique identifier column with a default value of newid() and an AFTER UPDATE trigger which inserts a new guid any time there is a modification.

  
CREATE TABLE Contacts(  
 ContactID int IDENTITY(1,1) NOT NULL,
 FirstName varchar(50),
 LastName varchar(50),
 Version uniqueidentifier NOT NULL default (newid()),
 CONSTRAINT [PK_Contacts] PRIMARY KEY CLUSTERED (ContactID ASC)
)

CREATE TRIGGER trig_ContactsVersion  
ON Contacts  
AFTER UPDATE  
AS  
BEGIN  
    UPDATE    Contacts
    SET        Version = newid()
    WHERE    ContactID IN (SELECT ContactID FROM inserted);
END;  

And the corresponding properties must be configured:

So you actually have a lot of flexibility here.  If you don't like the SQL Server Timestamp data type, no problem.  Just use your Optimistic concurrency implementation of choice.  Of course, these implementations can all be used with a stored procedure approach as well.