On Entity Framework, Concurrency

This is similar but not at all identical to this post about LINQ to SQL because it’s using a different framework and that has different capabilities.

Someone mailed and asked how we can detect concurrency problems with Entity Framework in order to ensure that when we submit changes to the DB we first;

  1. Present the user with a list of all the concurrency errors in one go
  2. Present the user with their changes versus the DB’s current data
  3. Ask the user how to go about resolving each issue
  4. Repeat until all the changes get to the DB one way or another

I can use the same tables and scripts and so on that I used in the previous post about LINQ to SQL. So…if I’ve got this table in my DB;

create table Person
(
  id int identity primary key,
  firstName nvarchar(30),
  lastName nvarchar(30),
  timestamp 
)

with data;

insert person(firstname,lastname) values('first1', 'last1')
insert person(firstname,lastname) values('first2', 'last2')
insert person(firstname,lastname) values('first3', 'last3')

and I bring this into an Entity Framework based project using the usual route and I make sure that I’m checking the timestamp for concurrency by altering the properties on the diagram as below;

image

and then I can start to write some code (similar to my LINQ to SQL) code as below;

    using (demoEntities context = new demoEntities())
    {
      Console.WriteLine("Enter a new surname");
      string surname = Console.ReadLine();

      var people = context.Person.ToList();

      foreach (Person p in people)
      {
        p.lastName = surname;
      }
      Console.WriteLine("Now's the time to cause a concurrency violation");
      Console.ReadLine();

      try
      {
        context.SaveChanges();
      }
      catch (OptimisticConcurrencyException ex)
      {
        foreach (ObjectStateEntry item in ex.StateEntries)
        {
          Console.WriteLine("Found an item");
        }
      }
    }

The “problem” that I’ve got here is that there’s no version of SaveChanges on the ObjectContext that allows for you to continue when you hit a concurrency error making it hard for me to build a list of “all the concurrency problems”.

So, if I cause a concurrency problem at the Console.ReadLine() above with two of my entities by doing something like;

update person set lastName='anything' where id=2
update person set lastName='else' where id=3
 

then what I’m going to see is 1 an exception containing the details of a single concurrency exception. I’m not going to get to “see” the second concurrency exception until I clear the first one.

How can I clear the first one? By refreshing the entity’s original values from the DB whilst keeping my changes.

That’s all fine in that I can write code like this but if I wanted to build up a complete list of all my concurrency problems and then present them to a user as a list before making any changes to the data then this code;

    using (demoEntities context = new demoEntities())
    {
      Console.WriteLine("Enter a new surname");
      string surname = Console.ReadLine();

      var people = context.Person.ToList();

      foreach (Person p in people)
      {
        p.lastName = surname;
      }
      Console.WriteLine("Now's the time to cause a concurrency violation");
      Console.ReadLine();

      bool allSaved = false;
      List<object> failedEntities = new List<object>();

      while (!allSaved)
      {
        try
        {
          context.SaveChanges();
          allSaved = true;
        }
        catch (OptimisticConcurrencyException ex)
        {
          // This will only iterate once.
          foreach (ObjectStateEntry item in ex.StateEntries)
          {
            context.Refresh(RefreshMode.ClientWins, item.Entity);
            failedEntities.Add(item.Entity);
          }
        }
      }
      // Spot the problem? It's too late already...
      Console.WriteLine("Encountered {0} concurrency problems along the way",
        failedEntities.Count);
    }

has an obvious problem in that by the time my code has figured out the list of entities that have concurrency problems it’s all too late because I’ve already submitted the changes to the DB without having had any opportunity to consult the user (i.e. the last call to SaveChanges will actually work).

The only way that I can think of stopping that happening is to ensure that the transaction will roll back in the case where SaveChanges fails ( this would happen anyway ) and the case where it succeeds ( this would not happen usually ).

Something along the lines of;

    using (demoEntities context = new demoEntities())
    {
      Console.WriteLine("Enter a new surname");
      string surname = Console.ReadLine();

      var people = context.Person.ToList();

      foreach (Person p in people)
      {
        p.lastName = surname;
      }
      Console.WriteLine("Now's the time to cause a concurrency violation");
      Console.ReadLine();

      bool allSaved = false;

      while (!allSaved)
      {
        bool allTried = false;
        List<object> failedEntities = new List<object>();

        while (!allTried)
        {
          TransactionScope scope = new TransactionScope();

          try
          {
            context.SaveChanges(false);

            allTried = true;

            if (failedEntities.Count == 0)
            {
              scope.Complete();
            }
            scope.Dispose();
          }
          catch (OptimisticConcurrencyException ex)
          {
            scope.Dispose();

            // This will only iterate once.
            foreach (ObjectStateEntry item in ex.StateEntries)
            {
              context.Refresh(RefreshMode.ClientWins, item.Entity);
              failedEntities.Add(item.Entity);
            }
          }
        }
        if (!(allSaved = failedEntities.Count == 0))
        {
          Console.WriteLine("Hit {0} concurrency problems", failedEntities.Count);

          foreach (object entity in failedEntities)
          {
            ObjectStateEntry stateEntry =
              context.ObjectStateManager.GetObjectStateEntry(entity);

            RefreshMode mode = PromptUserForEntityConflict(stateEntry);

            context.Refresh(mode, entity);
          }
        }
      }
      // Finally, we can make current == original.
      context.AcceptAllChanges();
    }

 

with a couple of little functions;

  private static RefreshMode PromptUserForEntityConflict(ObjectStateEntry entry)
  {
    RefreshMode mode = RefreshMode.StoreWins;

    Console.WriteLine("Conflict on entity with key [{0}]",
      EntityKeyToString(entry));

    foreach (string propertyName in entry.GetModifiedProperties())
    {
      Console.WriteLine("\tProperty [{0}], your value [{1}], db value [{2}]",
        propertyName, entry.CurrentValues[propertyName],
        entry.OriginalValues[propertyName]);
    }

    Console.WriteLine("Do you want to keep your values [y] or db values [d]?");

    mode = Console.ReadLine() == "y" ?
      RefreshMode.ClientWins : RefreshMode.StoreWins;

    return (mode);
  }
  private static string EntityKeyToString(ObjectStateEntry entry)
  {
    StringBuilder sb = new StringBuilder();

    for (int i = 0; i < entry.EntityKey.EntityKeyValues.Length; i++)
    {
      sb.AppendFormat("{0}{1}", i > 0 ? "," : string.Empty,
        entry.EntityKey.EntityKeyValues[i]);
    }
    return (sb.ToString());
  }

This seems to be about the best I can do if I need to present the user with all (current) concurrency problems in a single go and also present them with their values versus the database values.

In a lot of systems, concurrency issues are rare (e.g. generally I’m hoping that when I’m updating my bank account details via a call centre there isn’t someone else also updating my details :-)) so this might not be such a common scenario but, with this implementation it means that if you have M modifications to update which cause N concurrency exceptions then you’ll need at least N+2 trips to the database (with quite a lot of rolled back transactions) to make this work.

Feel free to suggest improvements and I’ll add them here.