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.
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:
CREATE TABLE [dbo].[Hero](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[Brand] [nvarchar](50) NULL,
[LastModified] [datetime] NULL,
CONSTRAINT [PK_Hero] PRIMARY KEY CLUSTERED
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:
CREATE TRIGGER [dbo].[trg_iu_Hero]
AFTER INSERT, UPDATE
SET NOCOUNT ON;
SET LastModified = GETDATE()
WHERE Id IN (SELECT Id FROM inserted)
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 ?
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:
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:
Here's the source code, the whole source code, and nothing but the source code: U2UConsult.EF40.DateTimeConcurrency.Sample.zip (616,27 kb)
This article is dedicated to my 3-year old daughter Merel. Last week she briefly turned into a real angel, but then decided to come back.
I want to thank from the bottom of my heart everybody who helped saving her life: her mama, her mammie, the MUG, and the emergency, reanimation, intensive care, and pediatric departments of the uza hospital.