Dealing With Optimistic Concurrency in Entity Framework
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
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
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);
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
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
- 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:
lblErr.Text = "Concurrency violation! Please review the latest values shown above.";
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:
lblErr.Text = "Concurrency violation! Attempting to force save to the database.";
var emp = ex.Entries.Single();
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.