Handling Concurrency with the Entity Framework in an ASP.NET MVC Application Chapter7

Concurrency Conflicts

A concurrency conflict occurs when one user displays an entity's data in order to edit it, and then another user updates the same entity's data before the first user's change is written to the database. If you don't set up the Entity Framework to detect such conflicts, whoever updates the database last overwrites the other user's changes. In many applications, this risk is acceptable: if there are few users, or few updates, or if isn't really critical if some changes are overwritten, the cost of programming for concurrency might outweigh the benefit. In that case, you don't have to configure the application to handle concurrency conflicts.

Pessimistic Concurrency (Locking)

If your application does need to prevent accidental data loss in concurrency scenarios, one way to do that is to use database locks. This is called pessimistic concurrency. For example, before you read a row from a database, you request a lock for read-only or for update access. If you lock a row for update access, no other users are allowed to lock the row either for read-only or update access, because they would get a copy of data that's in the process of being changed. If you lock a row for read-only access, others can also lock it for read-only access but not for update.
Managing locks has some disadvantages. It can be complex to program. It requires significant database management resources, and it can cause performance problems as the number of users of an application increases (that is, it doesn't scale well). For these reasons, not all database management systems support pessimistic concurrency. The Entity Framework provides no built-in support for it, and this tutorial doesn't show you how to implement it.

Optimistic Concurrency

The alternative to pessimistic concurrency is optimistic concurrency. Optimistic concurrency means allowing concurrency conflicts to happen, and then reacting appropriately if they do. For example, John runs the Departments Edit page, changes the Budget amount for the English department from $350,000.00 to $100,000.00. (John administers a competing department and wants to free up money for his own department.)

Before John clicks Save, Jane runs the same page and changes the Start Date field from 9/1/2007 to 1/1/1999. (Jane administers the History department and wants to give it more seniority.)

John clicks Save first and sees his change when the browser returns to the Index page, then Jane clicks Save. What happens next is determined by how you handle concurrency conflicts. Some of the options include the following:

• You can keep track of which property a user has modified and update only the corresponding columns in the database. In the example scenario, no data would be lost, because different properties were updated by the two users. The next time someone browses the English department, they'll see both John's and Jane's changes — a start date of 1/1/999 and a budget of $100,000.00. This method of updating can reduce the number of conflicts that could result in data loss, but it can't avoid data loss if competing changes are made to the same property of an entity. Whether the Entity Framework works this way depends on how you implement your update code. It's often not practical in a web application, because it can require that you maintain large amounts of state in order to keep track of all original values as well as new values. Maintaining large amounts of state can affect application performance because it either requires server resources or must be included in the web page itself (for example, in hidden fields).
  
• You can let Jane's change overwrite John's change. The next time someone browses the English department, they'll see 1/1/1999 and the restored $350,000.00 value. This is called a Client Wins or Last in Wins scenario. (The client's values take precedence over what's in the data store.) As noted in the introduction to this section, if you don't do any coding for concurrency handling, this will happen automatically.
  
• You can prevent Jane's change from being updated in the database. Typically, you would display an error message, show her the current state of the data, and allow her to reapply her changes if she still wants to make them. This is called a Store Wins scenario. (The data-store values take precedence over the values submitted by the client.) You'll implement the Store Wins scenario in this tutorial. This method ensures that no changes are overwritten without a user being alerted to what's happening.
  

Detecting Concurrency Conflicts

You can resolve conflicts by handling OptimisticConcurrencyException exceptions that the Entity Framework throws. In order to know when to throw these exceptions, the Entity Framework must be able to detect conflicts. Therefore, you must configure the database and the data model appropriately. Some options for enabling conflict detection include the following:

• In the database table, include a tracking column that can be used to determine when a row has been changed. You can then configure the Entity Framework to include that column in the Where clause of SQL Update or Delete commands.
  The data type of the tracking column is typically timestamp, but it doesn't actually contain a date or time value. Instead, the value is a sequential number that's incremented each time the row is updated. (Therefore the same type can be called rowversion in recent versions of SQL Server.) In an Update or Delete command, the Where clause includes the original value of the tracking column. If the row being updated has been changed by another user, the value in that column is different than the original value, so the Update or Delete statement can't find the row to update because of the Where clause. When the Entity Framework finds that no rows have been updated by the Update or Delete command (that is, when the number of affected rows is zero), it interprets that as a concurrency conflict.
  
• Configure the Entity Framework to include the original values of every column in the table in the Where clause of Update and Delete commands.
  As in the first option, if anything in the row has changed since the row was first read, the Where clause won't return a row to update, which the Entity Framework interprets as a concurrency conflict. This method is as effective as using a tracking column. However, for database tables that have many columns, this approach can result in very large Where clauses, and can require that you maintain large amounts of state. As noted earlier, maintaining large amounts of state can affect application performance because it either requires server resources or must be included in the web page itself. Therefore this approach generally not recommended, and it isn't the method used in this tutorial.
  

In the remainder of this tutorial you'll add a tracking property to the Department entity, create a controller and views, and test to verify that everything works correctly.

Note If you were implementing concurrency without a tracking column, you would have to mark all non-primary-key properties in the entity for concurrency tracking by adding the ConcurrencyCheck attribute to them. That change would enable the Entity Framework to include all columns in the SQL WHERE clause of UPDATE statements.

Adding a Tracking Property to the Department Entity

In Models\Department.cs, add a tracking property:

        [Timestamp]
        public Byte[] Timestamp { get; set; }

The Timestamp attribute specifies that this column will be included in the Where clause of Update and Delete commands sent to the database.

Creating a Department Controller

Create a Department controller and views the same way you did the other controllers, using the following settings:

In Controllers\DepartmentController.cs, add a using statement:

using System.Data.Entity.Infrastructure;

Change "LastName" to "FullName" everywhere in this file (four occurrences) so that the department administrator drop-down lists will contain the full name of the instructor rather than just the last name.
Replace the existing code for the HttpPost Edit method with the following code:

[HttpPost]
public ActionResult Edit(Department department)
{
    try
    {
        if (ModelState.IsValid)
        {
            db.Entry(department).State = EntityState.Modified;
            db.SaveChanges();
            return RedirectToAction("Index");
        }
    }
    catch (DbUpdateConcurrencyException ex)
    {
        var entry = ex.Entries.Single();
        var databaseValues = (Department)entry.GetDatabaseValues().ToObject();
        var clientValues = (Department)entry.Entity;
        if (databaseValues.Name != clientValues.Name)
            ModelState.AddModelError("Name", "Current value: "
                + databaseValues.Name);
        if (databaseValues.Budget != clientValues.Budget)
            ModelState.AddModelError("Budget", "Current value: "
                + String.Format("{0:c}", databaseValues.Budget));
        if (databaseValues.StartDate != clientValues.StartDate)
            ModelState.AddModelError("StartDate", "Current value: "
                + String.Format("{0:d}", databaseValues.StartDate));
        if (databaseValues.InstructorID != clientValues.InstructorID)
            ModelState.AddModelError("InstructorID", "Current value: "
                + db.Instructors.Find(databaseValues.InstructorID).FullName);
        ModelState.AddModelError(string.Empty, "The record you attempted to edit "
            + "was modified by another user after you got the original value. The "
            + "edit operation was canceled and the current values in the database "
            + "have been displayed. If you still want to edit this record, click "
            + "the Save button again. Otherwise click the Back to List hyperlink.");
        department.Timestamp = databaseValues.Timestamp;
    }
    catch (DataException)
    {
        //Log the error (add a variable name after Exception)
        ModelState.AddModelError(string.Empty, "Unable to save changes. Try again, and 

if the problem persists contact your system administrator.");
    }

    ViewBag.InstructorID = new SelectList(db.Instructors, "InstructorID", "FullName", 

department.InstructorID);
    return View(department);
}

The view will store the original timestamp value in a hidden field. When the model binder creates the department instance, that object will have the original Timestamp property value and the new values for the other properties, as entered by the user on the Edit page. Then when the Entity Framework creates a SQL UPDATE command, that command will include a WHERE clause that looks for a row that has the original Timestamp value.
If zero rows are affected by the UPDATE command, the Entity Framework throws a DbUpdateConcurrencyException exception, and the code in the catch block gets the affected Department entity from the exception object. This entity has both the values read from the database and the new values entered by the user:

var entry = ex.Entries.Single();
var databaseValues = (Department)entry.GetDatabaseValues().ToObject();
var clientValues = (Department)entry.Entity;

Next, the code adds a custom error message for each column that has database values different from what the user entered on the Edit page:

if (databaseValues.Name != currentValues.Name)
    ModelState.AddModelError("Name", "Current value: " + databaseValues.Name);
    // ...

A longer error message explains what happened and what to do about it:

ModelState.AddModelError(string.Empty, "The record you attempted to edit "
    + "was modified by another user after you got the original value. The"
    + "edit operation was canceled and the current values in the database "
    + "have been displayed. If you still want to edit this record, click "
    + "the Save button again. Otherwise click the Back to List hyperlink.");

Finally, the code sets the Timestamp value of the Department object to the new value retrieved from the database. This new Timestamp value will be stored in the hidden field when the Edit page is redisplayed, and the next time the user clicks Save, only concurrency errors that happen since the redisplay of the Edit page will be caught.
In Views\Department\Edit.cshtml, add a hidden field to save the Timestamp property value, immediately following the hidden field for the DepartmentID property:

        @Html.HiddenFor(model => model.Timestamp)

In Views\Department\Index.cshtml, replace the existing code with the following code to move row links to the left and change the page title and column headings to display FullName instead of LastName in the Administrator column:

@model IEnumerable<ContosoUniversity.Models.Department>

@{
    ViewBag.Title = "Departments";
}
<h2>Departments</h2>
<p>
    @Html.ActionLink("Create New", "Create")</p>
<table>
    <tr>
        <th></th>
        <th>Name</th>
        <th>Budget</th>
        <th>Start Date</th>
        <th>Administrator</th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.DepartmentID }) |
            @Html.ActionLink("Details", "Details", new { id=item.DepartmentID }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.DepartmentID })
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Name)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Budget)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.StartDate)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.Administrator.FullName)
        </td>
    </tr>
}
</table>

Testing Optimistic Concurrency Handling

Run the site and click Departments:

Click an Edit hyperlink and then open a new browser window and go to the same URL in that window. The windows display the same information.

Change a field in the first browser window and click Save.

The browser shows the Index page with the changed value.

Change the same field to a different value in the second browser window.

Click Save in the second browser window. You see an error message:

Click Save again. The value you entered in the second browser is saved in the database and you see that value when the Index page appears.

Adding a Delete Page

For the Delete page, the Entity Framework detects concurrency conflicts in a similar manner. When the HttpGet Delete method displays the confirmation view, the view includes the original Timestamp value in a hidden field. That value is then available to the HttpPost Delete method that's called when the user confirms the deletion. When the Entity Framework creates the SQL DELETE command, it includes a WHERE clause with the original Timestamp value. If the command results in zero rows affected (meaning the row was changed after the Delete confirmation page was displayed), a concurrency exception is thrown, and the HttpGet Delete method is called with an error flag set to true in order to redisplay the confirmation page with an error message.
In DepartmentController.cs, replace the HttpGet Delete method with the following code:

public ActionResult Delete(int id, bool? concurrencyError)
{
    if (concurrencyError.GetValueOrDefault())
    {
        ViewBag.ConcurrencyErrorMessage = "The record you attempted to delete "
            + "was modified by another user after you got the original values. "
            + "The delete operation was canceled and the current values in the "
            + "database have been displayed. If you still want to delete this "
            + "record, click the Delete button again. Otherwise "
            + "click the Back to List hyperlink.";
    }

    Department department = db.Departments.Find(id);
    return View(department);
}

The method accepts an optional parameter that indicates whether the page is being redisplayed after a concurrency error. If this flag is true, error message text is sent to the view using a ViewBag property.
Replace the code in the HttpPost Delete method (named DeleteConfirmed) with the following code:

[HttpPost, ActionName("Delete")]
public ActionResult DeleteConfirmed(Department department)
{
    try
    {
        db.Entry(department).State = EntityState.Deleted;
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    catch (DbUpdateConcurrencyException)
    {
        return RedirectToAction("Delete",
            new System.Web.Routing.RouteValueDictionary { { "concurrencyError", true } });
    }
    catch (DataException)
    {
        //Log the error (add a variable name after Exception)
        ModelState.AddModelError(string.Empty, "Unable to save changes. Try again, 

and if the problem persists contact your system administrator.");
        return View(department);
    }
}

In the scaffolded code that you just replaced, this method accepted only a record ID:

        public ActionResult DeleteConfirmed(int id)

You've changed this parameter to a Department entity instance created by the model binder. This gives you access to the Timestamp property value in addition to the record key.

        public ActionResult DeleteConfirmed(Department department)

If a concurrency error is caught, the code redisplays the Delete confirmation page and provides a flag that indicates it should display a concurrency error message.
In Views\Department\Delete.cshtml, replace the scaffolded code with the following code to make some formatting changes and add an error message field:

@model ContosoUniversity.Models.Department

@{
    ViewBag.Title = "Delete";
}
<h2>Delete</h2>
<p class="error">@ViewBag.ConcurrencyErrorMessage</p>
<h3>Are you sure you want to delete this?</h3>
<fieldset>
    <legend>Department</legend>

    <div class="display-label">
        @Html.LabelFor(model => model.Name)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Name)
    </div>

    <div class="display-label">
        @Html.LabelFor(model => model.Budget)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Budget)
    </div>

    <div class="display-label">
        @Html.LabelFor(model => model.StartDate)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.StartDate)
    </div>

    <div class="display-label">
        @Html.LabelFor(model => model.InstructorID)
    </div>
    <div class="display-field">
        @Html.DisplayFor(model => model.Administrator.FullName)
    </div>
</fieldset>
@using (Html.BeginForm()) {
    @Html.HiddenFor(model => model.DepartmentID)
    @Html.HiddenFor(model => model.Timestamp)
    <p>
        <input type="submit" value="Delete" /> |
        @Html.ActionLink("Back to List", "Index")
    </p>
}

This code adds an error message between the h2 and h3 headings:

<p class="error">@ViewBag.ConcurrencyErrorMessage</p>

It replaces LastName with FullName in the Administrator field:

<div class="display-label">
    @Html.LabelFor(model => model.InstructorID)</div>
<div class="display-field">
    @Html.DisplayFor(model => model.Administrator.FullName)</div>

Finally, it adds hidden fields for the DepartmentID and Timestamp properties after the Html.BeginForm statement:

@Html.HiddenFor(model => model.DepartmentID)
@Html.HiddenFor(model => model.Timestamp)

Run the Departments Index page and open a second browser window with the same URL.
In the first window, click Edit on a department and change one of the values, but don't click Save yet:

In the second window, select Delete on the same department. The Delete confirmation page appears.

Click Save in the first browser window. The Index page confirms the change.

Now click Delete in the second browser. You see the concurrency error message, and the Department values are refreshed with what's currently in the database.

If you click Delete again, you're redirected to the Index page, which shows that the department has been deleted.