Entity Framework by default doesn’t handle concurrency issues. If you want it to perform concurrency checks, you need to have some column in each of your tables to be able to check whether the row you’re about to overwrite has changed since the last time you read it. The best solution for that is to have a column in which to write a new value (ie. a version number) each time you write a row, and then compare it just before updating it.

Fortunately, Entity Framework does support this technique:

Timestamp column with code-first approach

For the code-first approach, you need to declare a column of type Byte[], and then decorate it with the Timestamp attribute. This way, Entity Framework will know this column must be included in the Where clause of SQL sentences for Updates and Deletes actions.

using System;
using System.ComponentModel.DataAnnotations;

namespace MyProject.Entities
{
   public class Person
   {
      public Int64 Id { get; set; }
      public string Name { get; set; }
      public DateTime BirthDate { get; set; }
      public string EMail { get; set; } 

      [Timestamp]
      public Byte[] Timestamp { get; set; }
   }
}

Now you need to update your EF model and re-create the database structure, so the new column will be created in the table.

And voila! Now you have automatic concurrency checking every time you try to update/delete a row in this table. You can check this column is being included in all row-modifying SQL sentences EF generates. You could use SQL server profiler, for example, to inspect these sentences.

Timestamp column with model-first approach

Now, setting this column with the model-first approach is a little bit trickier. There’s no built-in support for this in EF’s model itself (yet), so we’ll have to hack the code generation template to fulfill our needs.

What we need to do to set up a  timestamp column using model first approach is the following:

  1. Add a property named “Timestamp” to the entity in EF’s model
  2. Set the type to binary
  3. Set nullable to false
  4. Set StoreGeneratedPattern to Computed
  5. Set ConcurrencyMode to Fixed
  6. Create a copy of SSDLToSQL10.tt (typically found in C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen)
  7. Edit the line that says:
    [<#=Id(prop.Name)#>] <#=prop.ToStoreType()#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
  8. Change it to:
    [<#=Id(prop.Name)#>] <#if (string.Compare(prop.Name,"TimeStamp",true) == 0) { #>TIMESTAMP<# } else { #><#=prop.ToStoreType()#><# } #> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
    This will change any column that is called “Timestamp” (case insensitive) to be a Timestamp column.
  9. Click on the entity canvas and set the DDL Generation Template to this new copy of the file
  10. Click on Generate Database From Model
  11. Enjoy your new concurrency-aware data access!

Related posts:

  1. Entity Framework
  2. Things to consider when using Entity Framework
  3. Persistance layer: object-relational mapping tools
  4. Sluggish database view? Materialize it!
  5. Which database to use for a personal project?