Tip 19 – How to use Optimistic Concurrency with the Entity Framework

  • [アーティクル]

This is the 19th post in my ongoing series of Entity Framework Tips.

Background:

If you have a table with a timestamp column, and you reverse engineer an Entity from that table, you will end up with a Binary property in your entity (in my example called Version).

If you look at the properties window for that property you will see something like this:

ConcurrencyModeFixed

The interesting thing here is the Concurrency Mode. The Entity Framework support 2 Concurrency Modes:

  • None – This is the default and means the property is not involved in any concurrency checks
  • Fixed – Which means that the original value of this property is sent as part of the WHERE clause in all updates or deletes

So timestamps have a Concurrency Mode of Fixed, which means the original value loaded from the database is included in the WHERE clause of any Updates or Deletes.

If you delve a little deeper by looking at the Storage Model (aka SSDL), which you can see if you open your EDMX in the XML editor, you will notice something else, namely that the Version property has a StoreGeneratedPattern of Computed.

There are 3 possible StoreGeneratedPatterns:

  • None – This is the default and by far the most common. It means that this column is not generated in the database.
  • Identity – This means that when the EF does inserts the database will generate a value. So after an insert the EF will get the generated value and feed it back into the Entity. This setting is frequently used for primary keys that are automatically generated in the database.
  • Computed – This means that whenever the EF does insert OR update the store will generate a new value. So the EF will feed the generated value back into the entity after both inserts AND updates. Yes you guessed it this is generally used for things like Timestamps.

So because timestamps have a StoreGeneratedPattern of Computed, whenever the EF Inserts or Updates an entity with a timestamp column it will automatically get the latest timestamp value and feed it back into the Entity.

Handling Optimistic Concurrency Exceptions:

In the example below I have an Entity called Post that has a timestamp column called Version. Given that this simple code:

using (TipsEntities ctx1 = new TipsEntities())
{
// Get a post (which has a Version Timestamp) in one context
// and modify. 
Post postFromCtx1 = ctx1.Post.First(p => p.ID == 1);
postFromCtx1.Title = "New Title";

// Modify and Save the same post in another context
// i.e. mimicking concurrent access.
using (TipsEntities ctx2 = new TipsEntities())
{
Post postFromCtx2 = ctx2.Post.First(p => p.ID == 1);
postFromCtx2.Title = "Newer Title";
ctx2.SaveChanges();
}
// Save the changes... This will result in an Exception
ctx1.SaveChanges();
}

… will cause an OptimisticConcurrencyException:

OptimisticConcurrencyExceptionHeader

… now if we delve a little deeper, by clicking ‘View Detail’ you will see that the OptimisticConcurrencyException gives you access to the ObjectStateEntry(s)associated with the Entity(s) that caused the concurrency exception via the StateEntries property:

OptimisticConcurrencyException

Which means that if you want to handle this sort of situation gracefully you simply trap the OptimisticConcurrencyException and grab the Entity(s) related to those StateEntries so you can provide some sort of message:

catch (OptimisticConcurrencyException ex) {
ObjectStateEntry entry = ex.StateEntries[0];
Post post = entry.Entity as Post;
Console.WriteLine("Failed to save {0} because it was changed in the database", post.Title);
}

Pretty simple if you ask me.

Of course in the real world things are seldom this simple. Your requirements probably states that you have to give the user the ability to compensate and retry. How do you do that exactly?

Well that is definitely an interesting scenario, so expect another tip soon.

Comments

  • Anonymous
    June 08, 2009
    PingBack from http://blogs.msdn.com/alexj/archive/2009/03/26/index-of-tips.aspx
  • Anonymous
    June 08, 2009
    Tried it but as soon as I want to 'generate database script from model', I get the 'The given key was not present in the dictionary' error.Would you please tell us what the equivalent settings in the database column version is for:'ConcurrencyMode = Fixed''StoreGeneratedPattern = Computed'That way I can try to create the table first and them update the model.
  • Anonymous
    October 08, 2009
    Tried it but as soon as I want to 'generate database script from model', I get the 'The given key was not present in the dictionary' error.Would you please tell us what the equivalent settings in the database column version is for:'ConcurrencyMode = Fixed''StoreGeneratedPattern = Computed'That way I can try to create the table first and them update the model.I have the same problem. I need help, please
  • Anonymous
    October 08, 2009
    @Renato and IvánHmm I'm not sure what the limitation is with Model First, but what you need in the SSDL is a timestamp column for SQL server.Hope this helpsAlex
  • Anonymous
    June 17, 2010
    Please help, I have a real problem with Concurrency in EF4.I have 3 database tables: User, Session, and Workstation.I am using Concurrency mode = Fixed and StoreGeneratedPattern=Computed on timestamp columns in Workstation and User, but not Session.I create a new Session object.  It has relationships to User and Workstation objects.  I just set those relationship navigation properties directly to existing objects I already had selected from the Context before.Next I save the Session object by calling Context.SaveChanges().  When I use SQL Profiler to look at the SQL, it always updates all 3 objects.  But I only want it to save the Session.  It is re-saving the Workstation and the User objects (just touching the Timestamp and selecting it back) because they have the Fixed Concurrency Timestamp fields and they participate in a relationship with the new Session.This is a performance concern when using EF4, since I am only changing one object I just want to save one object to the database.  How can I prevent EF4 from also saving the related objects when I have Concurrency mode=Fixed and StoreGeneratedPattern=Computed on timestamp columns in those related tables?Thanks for your help,Hugh
  • Anonymous
    January 29, 2011
    I had a problem where I created a 'standard' table with a 'standard' attribute that is of type DateTime and when I created an object of that table and created a new DateTime object and added it to the right parameter of that table-object it contained milliseconds, but that was in conflict to the databasetype. So I had to create a DateTime object without miliseconds.//This code failed:Order order = new Order();DateTime dt = DateTime.Now;order.timeOrdered = dt;db.AddToOrders(order);db.SaveChanges(); // OptimisticConcurrencyException//This worked:Order order = new Order();DateTime dt = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second);order.timeOrdered = dt;db.AddToOrder(order);db.SaveChanges();
  • Anonymous
    May 08, 2011
    The comment has been removed
  • Anonymous
    February 28, 2012
    Why use a class named Post in an example, when it could confuse people thinking that it has some meaning?
  • Anonymous
    March 09, 2012
    Paul... yeah good question.I'm often guilty of that particular sin - thanks for pointing it out!