This article explains how to implement optimistic concurrency checking using a SQL Server DateTime or DateTime2 column. It's a follow-up of my previous article on using a TimeStamp column for that same purpose. In most -if not all- concurrency checking cases it actually makes more sense to use a DateTime column instead of a TimeStamp. The DateTime data types occupy the same storage (8 bytes) as a TimeStamp, or even less: DateTime2 with 'low' precision takes only 6 bytes. On top of that: their content makes sense to the end user. Unfortunately the DateTime data types are 'a little bit' less evident to use for concurrency checking: you need to declare a trigger (or a stored procedure) on the table, and you need to hack the entity model.

A sample table

Sample time! Let's start with creating a table to hold some data.

Table definition

Here's how the table looks like (the solution at the end of the article contains a full T-SQL script). The LastModified column will be used for optimistic concurrency checking:

Trigger definition

Unlike an Identity or a TimeStamp value, a DateTime value is not automatically generated and/or updated. So we have to give the database a little help, e.g. by creating a trigger for insert and update on that table:

Alternatively, you could insert through a stored procedure. 

A sample application

I already prepared for you a small sample application. Here's how the main window looks like:

Cute, isn't it ?

The problem

In the entity model, you have to make sure that the LastModified column has the correct settings (Fixed and Computed):

Run the application with just the generated code. You will observe that when you update a record, the entity's LastModified property will NOT be updated. SQL Server Profiler will reveal that only an update statement is issued. The new value of LastModified is assigned by the trigger but NOT fetched:

The solution

In order to let the Entity Framework fetch the new value of the DateTime column -or whatever column that is modified by a trigger-, you need to hack the model's XML and manually add the following attribute in the SSDL:

Somewhere in Redmond there will certainly be an architect who will provide an excuse for this behavior. To us developers, this sure smells like a bug. Anyway, if you re-run the application with the modified SSDL, the new DateTime value will appear after insert or update. SQL Server profiler reveals the extra select statement:

Source Code

Here's the source code, the whole source code, and nothing but the source code: U2UConsult.EF40.DateTimeConcurrency.Sample.zip (616,27 kb)

Enjoy!