Two Ways of Handling Concurrency Violations in Entity Framework Code First

 In one of my past articles I explained how to trap concurrency violations in entity framework. That article used database first approach for the example discussed therein. Since code first is becoming more and more popular it is worthwhile to see how concurrency violations can be trapped in code first approach. This article explains just that.

When you update an entity and call SaveChanges() to save the values back in the database, the existing values for that record are overwritten by the new entity values. This is the default behavior in entity framework code first. In a real world situation it is quite possible that database values might have got changed after you select them for modification. In such cases, your update operation might be overwriting changes made by someone else. This is concurrency violation. And it is 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.

Entity framework code first offers two approaches to detect concurrency violations:

• [ConcurrencyCheck] attribute
• [Timestamp] attribute

The former approach is used in cases where your table doesn't have any timestamp or rowversion column. You are required to decorate all the non-primary key properties of an entity with [ConcurrencyCheck] attribute. Entity framework then adds all these columns to the WHERE clause of the resultant SQL statement. This way you check whether all the column values that you fetched and the values in the database are same or not. If they are same that indicates no one else modified that record and your UPDATE / DELETE succeeds. On the other hand if the values don't match it's an indicator that someone else has modified the values and your UPDATE / DELETE statement fails. Whenever an UPDATE or DELETE affects zero records, entity framework throws  concurrency violation related exception (for example, DbUpdateConcurrencyException). You need to catch this exception and inform the user accordingly (or take a corrective action within your code).

The later approach is suitable when you have (or can add) timestamp or rowversion column in your table. In this case you decorate only the timestamp / rowversion property with the [Timestamp] attribute. Just like [ConcurrencyCheck] entity framework then adds WHERE clause for this property. The advantage here is that you have only one column in the WHERE clause (in addition to the primary key) instead of many as in the case of [ConcurrencyCheck] attribute. Note that SQL server timestamp / rowversion gets translated as a byte[] in .NET code.

Let's try to illustrate this with a simple example. We will use the Customers table of Northwind database for our example.

Create a new ASP.NET MVC project based on empty template. Open the web.config residing in the root folder and add a database connection string pointing to the Northwind sample database of SQL server. The following markup shows this connection string:

<connectionStrings>
  <add name="Northwind" 
       connectionString="data source=.;
         initial catalog=Northwind;integrated 
         security=true" 
       providerName="System.Data.SqlClient" />
</connectionStrings>

Now, add NuGet package for Entity Framework using the Manage NuGet packages option. Our example needs EntityFramework.dll and EntityFramework.SqlServer.dll assemblies that get added by the NuGet package.

Using [ConcurrencyCheck] attribute

Next, add a new class to the project and name it Customer. The following code shows the completed Customer class:

[Table("Customers")]
public class Customer
{
    [Key]
    public string CustomerID { get; set; }
    [ConcurrencyCheck]
    public string CompanyName { get; set; }
    [ConcurrencyCheck]
    public string ContactName { get; set; }
    [ConcurrencyCheck]
    public string Country { get; set; }
}

Notice that all the other properties except CustomerID are decorated with [ConcurrencyCheck] attribute.

Now add another class - NorthwindDbContext and code it as shown below:

public class NorthwindDbContext:DbContext
{
    public NorthwindDbContext():base("Northwind")
    {
    }
    public DbSet<Customer> Customers { get; set; }
}

The NorthwindDbContext class inherits from DbContext class. The constructer of the NorthwindDbContext calls the base class constructor and passes the Northwind connection string name (see web.config markup shown earlier). It also declares the Customers DbSet.

Now add HomeController to the Controllers folder and add two action methods to it. These actions are shown below:

public ActionResult Index()
{
    using (NorthwindDbContext db = new NorthwindDbContext())
    {
        Customer obj = db.Customers.Find("ALFKI");
        return View(obj);
    }
}

[HttpPost]
public ActionResult Index(Customer obj)
{
    using (NorthwindDbContext db = new NorthwindDbContext())
    {
        try
        {
            db.Entry<Customer>(obj).State = EntityState.Modified;
            db.SaveChanges();
        }
        catch(Exception ex)
        {
            ModelState.AddModelError("", ex.Message);
        }
        return View(obj);
    }
}

The first Index() action simply fetches a Customer whose CustomerID is ALFKI. This is done purely for the sake of testing. You can use some other CustomerID or even make a provision to select from a dropdown list. The fetched Customer is passed to the Index view as its mode.

The second Index() action is intended to handle POST requests and accepts Customer as its parameter. This parameter will be supplied by the model binding framework of ASP.NET MVC. Inside, we simply mark the entity State as Modified and call SaveChanges(). A try-catch block deals with exceptions thrown during the save operation (if any).

The markup of Index view is shown below:

@model ConcurrencyDemo.Models.Customer
...
<h1>Edit Existing Customer</h1>
@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
  <table border="1" cellpadding="10">
  <tr>
  <td>Customer ID :</td>
  <td>@Html.TextBoxFor(m => m.CustomerID, 
       new { @readonly = "readonly" })</td>
  </tr>
  <tr>
  <td>Company Name :</td>
  <td>@Html.TextBoxFor(m => m.CompanyName)</td>
  </tr>
  <tr>
  <td>Contact Name :</td>
  <td>@Html.TextBoxFor(m => m.ContactName)</td>
  </tr>
  <tr>
  <td>Country :</td>
  <td>@Html.TextBoxFor(m => m.Country)</td>
  </tr>
  <tr>
  <td colspan="2">
  <input type="submit" value="Update" />
  </td>
  </tr>
  </table>
}
@Html.ValidationSummary()
...
</html>

The Index view contain a <form> that submits to the Index() action you created earlier. It uses LabelFor() and TextBoxFor() HTML helpers to render a data entry screen for capturing the CompanyName, ContactName and Country. Errors (if any) are displayed by the ValidationSummary() helper. The following figure shows how Index view looks like at runtime:

Ok. Now you are ready to test your application.

Set a breakpoint in the try block and run the application. When the Index view is displayed in the browser modify some value (say Country) and hit Update button.

When the execution halts at the breakpoint, open Customers in Visual Studio IDE or SQL Server management studio and change the Country of the same record (CustomerID ALFKI in this case) to some other value. Now if you try to SaveChanges(), it will throw an exception as shown below:

Using [Timestamp] attribute

Now let's use [Timestamp] attribute instead of [ConcurrencyCheck] attribute. First, you will need to add a timestamp or rowversion column to the Customers table. The following figure shows how this is done:

As you can see, we added an extra column RowVersion and set its data type to rowversion (older versions of SQL server only had timestamp data type). To represent this field in the Customer entity, modify Customer class as shown below:

[Table("Customers")]
public class Customer
{
    [Key]
    public string CustomerID { get; set; }
    public string CompanyName { get; set; }
    public string ContactName { get; set; }
    public string Country { get; set; }

    [Timestamp]
    public byte[] RowVersion { get; set; }
}

As you can see, now you have RowVersion property of type byte[] and it is decorated with [Timestamp] attribute. Remember to remove the [ConcurrencyCheck] attributes from the previous example.

A small change is required on the Index view also. We display CustomerID, CompanyName, ContactName and Country in textboxes. So, model binding can bind them to the respective properties of Customer. We also need RowVersion value during the update operation. And we will store it in a hidden field. Modify the Index view as shown below:

@using (Html.BeginForm("Index", "Home", FormMethod.Post))
{
    @Html.HiddenFor(m=>m.RowVersion)
    <table border="1" cellpadding="10">
    ...
    ...
}

Now run the application again and test it as before. You should get the same exception as before.

That's it! Keep coding!!