Dealing With Optimistic Concurrency In Entity Framework

Question

When you update a database record with the entity values, the database values are overwritten by the entity values (default behavior). In a real world situation it is quite possible that database values might have got changed after selecting them for modification. In such cases, your update operation might be overwriting changes made by someone else. That is why it becomes important to detect if concurrency violation has occurred. You can then take some corrective action or at least inform the user about such a violation. To that end this article shows how to detect concurrency violation in EF and also the possible ways to deal with the situation.

Detecting Concurrency Violation

Entity Framework can be configured to use optimistic concurrency while updating database records. That means no locks are held on the data being modified. The data is updated only if the database values at the time of fetching the data and the database values currently stored in the database match. To understand how the concurrency violation can be detected, let's develop a simple application that updates Employee details from Employees table. The Employees table consists of the columns shown by the following data model:

As you can see the Employees table has five columns: EmployeeID, FirstName, LastName, Title and UpdateToken. Most of the columns are self explanatory except UpdateToken. The UpdateToken column is of type rowversion. The rowversion data type ensures that the column holds a different value every time that record is updated. A rowversion column is automatically updated by the SQL Server and it won't play any direct role in your application data. Why do you need a rowversion column here? That's because it allows us to detect if a record has been changed after the initial selection. However, merely adding a rowversion column is not sufficient. In the entity framework designer you also need to set the ConcurrencyMode property of the UpdateToken property to Fixed. The following figure shows how this is done:

As you can see the Concurrency Mode is changed from None to Fixed. Changing this setting will cause EF to use the UpdateToken column in the WHERE clause of the UPDATE queries it generates. This way a row is updated only when value of UpdateToken at the time of initial fetch matches with the current value in the database. If they don't match no record is updated and EF concludes that there was a concurrency violation.

You can also detect a concurrency violation without using rowversion column. In that case you need to set ConcurrencyMode for each property that you wish to include in the WHERE clause. Using rowversion column simplifies your job and the resultant query.

Creating a Web Form

The data from the Employees table is shown in a DetailsView as shown below:

In order to bind this DetailsView with EF data model you need to write two methods in the code behind - DetailsView1_GetItems() and DetailsView1_UpdateItem(). The former method returns all the Employee objects from the Employees DbSet as IQueryable whereas the later method updates employee details back to the database. Below the DetailsView place a Label (not shown in the figure) to display the concurrency violation error.

The DetailsView1_GetItems() method is given below:

public IQueryable<Employee> DetailsView1_GetItems()
{
  EmployeeDbEntities db=new EmployeeDbEntities();
  var query = from e in db.Employees
              orderby e.EmployeeID
              select e;
  return query;
}

The DetailsView1_GetItems() method creates an instance of EmployeeDbEntities context. It then selects all Employee objects from Employees DbSet and returns them to the caller.

The DetailsView1_UpdateItem() method is shown below:

public void DetailsView1_UpdateItem(int EmployeeID)
{
  EmployeeDbEntities db=new EmployeeDbEntities();
  Employee item = db.Employees.Find(EmployeeID);
  TryUpdateModel(item);
  System.Threading.Thread.Sleep(15000);
  try
  {
    db.SaveChanges();
  }
  catch(DbUpdateConcurrencyException ex)
  {
    lblErr.Text = ex.Message;
  }
}

The above code finds an existing Employee based on the EmployeeID. Remember that this EmployeeID is passed by the DetailsView only if DataKeyNames property is set. The TryUpdateModel() method then sets the properties of the existing Employee from the values entered in the DetailsView. The code then halts the execution for 15 seconds. This is done purely for the sake of testing. Adding this delay will allow you to switch to the physical database table and modify its data manually to test the concurrency violation. An alternative is to set a breakpoint at SaveChanges() method to halt the execution and then change the database values manually.

The try...catch block is an important piece of code because it traps the concurrency violation error and displays it to the user. The try block attempts to save the changes back to the database by calling SaveChanges() method. If there are pending changes but SaveChanges() causes zero records to be updated it indicates a concurrency violation. If such a violation is detected EF throws DbUpdateConcurrencyException exception. The DbUpdateConcurrencyException class resides in System.Data.Entity.Infrastructure namespace. The catch block simply displays the error in the Label control.

To test what you developed so far, run the Web Form and modify an employee record. After clicking the Update button quickly switch to the database in Server Explorer of Visual Studio and modify the same record with some different values (you have 15 seconds to do this change!) and wait to see what is thrown on the screen. The following figure shows a sample run of the Web Form with the error:

During the above test run you will find that after the error message is displayed to the user, the DetailsView shows the latest data from the database.

Handling Concurrency Violation

 Once you detect concurrency violation, the next step is to decide what action to take. There are three approaches that you can take:

• Do nothing. Simply show the error message to the user and exit from the data modification stage.
• Update the entity under consideration from the latest data from the database table. Then show that data to the user so that he can take the necessary action.
• Forcefully update the database table with the values from the entity.

The first approach is quite easy and that is what you used in the preceding example. The second approach requires you to load database values in an entity and present them to the user. You can do that in the catch block as shown below:

...
catch(DbUpdateConcurrencyException ex)
{
  lblErr.Text = "Concurrency violation! Please review the latest values shown above.";
  ex.Entries.Single().Reload();
  DetailsView1.ChangeMode(DetailsViewMode.ReadOnly);
}

As you can see the DbUpdateConcurrencyException class provides access to the entity causing the error. The Reload() method called on an entity loads the values from the database into that entity. You then change the mode of the DetailsView to ReadOnly so that user can read the new values.

In the third approach, you make the current database values as the original values and then attempt SaveChanges() again. This approach is shown below:

...
catch(DbUpdateConcurrencyException ex)
{
  lblErr.Text = "Concurrency violation! Attempting to force save to the database.";
  var emp = ex.Entries.Single();
  emp.OriginalValues.SetValues(emp.GetDatabaseValues());
  db.SaveChanges();
}

Here, you set the OriginalValues of the entity under consideration to the current values from the database (obtained by calling GetDatabaseValues()). You then attempt SaveChanges() again.

You can test the same Web Form by adding the code for second and third approaches discussed above and verify whether it works as expected.