MVC 5 with EF 6 in Visual Basic - Handling Concurrency

4.71 (17 votes)

This tutorial is the tenth in a series of 12 that teach you how to build MVC 5 applications using Entity Framework for data access and Visual Basic. In earlier tutorials you learned how to update data. This tutorial shows how to handle conflicts when multiple users update the same entity at the same time. You'll change the web pages that work with the Department entity so that they handle concurrency errors.

The original tutorial series, produced by Tom Dykstra and Rick Anderson ( @RickAndMSFT ) was written using the C# language. My versions keep as close to the originals as possible, changing only the coding language. The narrative text is largely unchanged from the original and is used with permission from Microsoft.

This tutorial series teaches you how to create ASP.NET MVC 5 applications using the Entity Framework 6 and Visual Studio 2013 Express for Web. This series uses the Code First workflow. For information about how to choose between Code First, Database First, and Model First, see Entity Framework Development Workflows.

The tutorial series comprises 12 sections in total. They are intended to be followed sequentially as each section builds on the knowledge imparted in the previous sections. Progress through the sections is reflected in a Visual Studio Express for Web project download that accompanies each section which features the web application that you build through the series.

Download the code

The code for this section is available here. Save the .zip file to a convenient location and then extract the contents. Make sure you have an edition of Visual Studio 2013 installed (Express for Web, Professional, Premium or Ultimate) and double click the .sln file. Once the project is opened in your IDE, press Shift+Ctrl+B to build the solution. This will ensure that all packages are restored from Nuget and may take a while depending on your Internet connection speed.

The navigation path through the series is as follows:

  1. Creating an Entity Framework Data Model
  2. Implementing Basic CRUD Functionality
  3. Sorting, Filtering and Paging
  4. Connection Resiliency and Command Interception
  5. Code First Migrations and Deployment
  6. Creating a More Complex Data Model
  7. Reading Related Data
  8. Updating Related Data
  9. Async and Stored Procedures
  10. Handling Concurrency
  11. Implementing-Inheritance
  12. Advanced Entity Framework Scenarios

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 enable the detection of 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 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. 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 £0.00.

MVC5 With EF6

Before John clicks Save, Jane runs the same page and changes the Start Date field from 2007-09-01 to 2014-06-07.

MVC5 With EF6

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 2014-06-07 and a budget of £0.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 property values for an entity 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) or in a cookie.

  • You can let Jane's change overwrite John's change. The next time someone browses the English department, they'll see 2014-06-07 and the restored £350,000.00 value. This is called a Client Wins or Last in Wins scenario. (All values from the client 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 SQLUpdate or Delete commands.

    The data type of the tracking column is typically rowversion. The rowversion value is a sequential number that's incremented each time the row is updated. In an Update or Delete command, the Where clause includes the original value of the tracking column (the original row version) . If the row being updated has been changed by another user, the value in the rowversion 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. 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. Therefore this approach is generally not recommended, and it isn't the method used in this tutorial.

    If you do want to implement this approach to concurrency, you have to mark all non-primary-key properties in the entity you want to track concurrency for by adding the ConcurrencyCheck attribute to them. That change enables the Entity Framework to include all columns in the SQL Where clause of Update statements.

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

Add an Optimistic Concurrency  Property to the Department Entity

In Models\Department.vb, add a tracking property named RowVersion:

Imports System.ComponentModel.DataAnnotations
Imports System.ComponentModel.DataAnnotations.Schema

Namespace Models
    Public Class Department
        Public Property DepartmentID As Integer

        <StringLength(50, MinimumLength:=3)>
        Public Property Name As String

        <DataType(DataType.Currency)>
        <Column(TypeName:="money")>
        Public Property Budget As Decimal

        <DataType(DataType.Date)>
        <DisplayFormat(DataFormatString:="{0:yyyy-MM-dd}", ApplyFormatInEditMode:=True)>
        <Display(Name:="Start Date")>
        Public Property StartDate As DateTime

        Public Property InstructorID As Integer?

        <Timestamp>
        Public Property RowVersion As Byte()

        Public Overridable Property Administrator As Instructor
        Public Overridable Property Courses As ICollection(Of Course)

    End Class
End Namespace

The Timestamp attribute specifies that this column will be included in the Where clause of Update and Delete commands sent to the database. The attribute is called Timestamp because previous versions of SQL Server used a SQL timestamp data type before the SQL rowversion replaced it. The .Net type for rowversion is a byte array. 

If you prefer to use the fluent API, you can use the IsConcurrencyToken method to specify the tracking property, as shown in the following example:

Protected Overrides Sub OnModelCreating(ByVal modelBuilder As DbModelBuilder)
    modelBuilder.Conventions.Remove(Of PluralizingTableNameConvention)()
    modelBuilder.Entity(Of Course)() _
        .HasMany(Function(c) c.Instructors).WithMany(Function(i) i.Courses) _
        .Map(Function(t) t.MapLeftKey("CourseID") _
        .MapRightKey("InstructorID") _
         .ToTable("CourseInstructor"))
     modelBuilder.Entity(Of Department)().MapToStoredProcedures()
     modelBuilder.Entity(Of Department)() _
         .Property(Function(p) p.RowVersion).IsConcurrencyToken()
End Sub

By adding a property you changed the database model, so you need to do another migration. In the Package Manager Console (PMC), enter the following commands:

Add-Migration RowVersion 
Update-Database

Modify the Department Controller

In Controllers\DepartmentController.vb, add an Imports statement:

Imports System.Data.Entity.Infrastructure

In the DepartmentController.vb file, change all four occurrences of "LastName" to "FullName" so that the department administrator drop-down lists will contain the full name of the instructor rather than just the last name.

ViewBag.InstructorID = New SelectList(db.Instructors, "ID", "FullName", department.InstructorID)

Replace the existing code for the HttpPost Edit method with the following code:

<HttpPost()>
<ValidateAntiForgeryToken()>
Async Function Edit(<Bind(Include:="DepartmentID,Name,Budget,StartDate,RowVersion,InstructorID")> ByVal department As Department) As Task(Of ActionResult)
    Try
        If ModelState.IsValid Then
            db.Entry(department).State = EntityState.Modified
            Await db.SaveChangesAsync()
            Return RedirectToAction("Index")
        End If
    Catch ex As DbUpdateConcurrencyException
        Dim entry = ex.Entries.Single()
        Dim clientValues = DirectCast(entry.Entity, Department)
        Dim databaseEntry = entry.GetDatabaseValues()
        If databaseEntry Is Nothing Then
            ModelState.AddModelError(String.Empty, "Unable to save changes. The department was deleted by another user.")
        Else
            Dim databaseValues = DirectCast(databaseEntry.ToObject(), Department)

            If databaseValues.Name <> clientValues.Name Then
                ModelState.AddModelError("Name", "Current value: " & databaseValues.Name)
            End If
            If databaseValues.Budget <> clientValues.Budget Then
                ModelState.AddModelError("Budget", "Current value: " & String.Format("{0:c}", databaseValues.Budget))
            End If
            If databaseValues.StartDate <> clientValues.StartDate Then
                ModelState.AddModelError("StartDate", "Current value: " & String.Format("{0:d}", databaseValues.StartDate))
            End If
            If databaseValues.InstructorID <> clientValues.InstructorID Then
                ModelState.AddModelError("InstructorID", "Current value: " & db.Instructors.Find(databaseValues.InstructorID).FullName)
            End If
            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 cancelled 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.RowVersion = databaseValues.RowVersion
        End If
    Catch dex As RetryLimitExceededException
        'Log the error 
        ModelState.AddModelError(String.Empty, "Unable to save changes. Try again, and if the problem persists contact your system administrator.")
    End Try

    ViewBag.InstructorID = New SelectList(db.Instructors, "ID", "FullName", department.InstructorID)
    Return View(department)
End Function

The view will store the original RowVersion value in a hidden field. When the model binder creates the department instance, that object will have the original RowVersion 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 RowVersion value.

If no rows are affected by the UPDATE command (no rows have the original RowVersion value),  the Entity Framework throws a DbUpdateConcurrencyException exception, and the code in the catch block gets the affected Department entity from the exception object.

Dim entry = ex.Entries.Single()

This object has the new values entered by the user in its Entity property, and you can get the values read from the database by calling the GetDatabaseValues method.

 

Dim clientValues = DirectCast(entry.Entity, Department) 
Dim databaseEntry = entry.GetDatabaseValues()

The GetDatabaseValues method returns Nothing if someone has deleted the row from the database; otherwise, you have to cast the object returned to the Department class in order to access the Department properties.

If databaseEntry Is Nothing Then 
    ModelState.AddModelError(String.Empty, "Unable to save changes. The department was deleted by another user.")

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 cancelled 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 RowVersion value of the Department object to the new value retrieved from the database. This new RowVersion 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.vbhtml, add a hidden field to save the RowVersion property value, immediately following the hidden field for the DepartmentID property:

@Using (Html.BeginForm())
    @Html.AntiForgeryToken()
    
    @<div class="form-horizontal">
        <h4>Department</h4>
        <hr />
        @Html.ValidationSummary(true)
        @Html.HiddenFor(Function(model) model.DepartmentID)
        @Html.HiddenFor(Function(model) model.RowVersion)

Testing Optimistic Concurrency Handling

Run the site and click Departments:

MVC5 With EF6

Right click the Edit hyperlink for the English department and select Open in new tab, then click the Edit hyperlink for the English department. The two tabs display the same information.

MVC5 With EF6

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

MVC5 With EF6

The browser shows the Index page with the changed value.

MVC5 With EF6

Change a field  in the second browser tab and click Save.

MVC5 With EF6

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

MVC5 With EF6

Click Save again. The value you entered in the second browser tab is saved along with the original value of the data you changed in the first browser. You see the saved values when the Index page appears.

MVC5 With EF6

Updating the Delete Page

For the Delete page, the Entity Framework detects concurrency conflicts caused by someone else editing the department in a similar manner. When the HttpGet Delete method displays the confirmation view, the view includes the original RowVersion 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 RowVersion 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. It's also possible that zero rows were affected because the row was deleted by another user, so in that case a different error message is displayed.

In DepartmentController.vb, replace the HttpGet Delete method with the following code:

Async Function Delete(ByVal id As Integer?, ByVal concurrencyError As Boolean?) As Task(Of ActionResult)
    If IsNothing(id) Then
        Return New HttpStatusCodeResult(HttpStatusCode.BadRequest)
    End If
    Dim department As Department = Await db.Departments.FindAsync(id)
    If IsNothing(department) Then
        If concurrencyError = True Then
            Return RedirectToAction("Index")
        End If
        Return HttpNotFound()
    End If
    If concurrencyError.GetValueOrDefault() Then
        If IsNothing(department) Then
            ViewBag.ConcurrencyErrorMessage = "The record you attempted to delete " & _
                    "was deleted by another user after you got the original values. " & _
                    "Click the Back to List hyperlink."
        Else
            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."
        End If
    End If
    Return View(department)
End Function

The method accepts an optional parameter that indicates whether the page is being redisplayed after a concurrency error. If this flag is true, an error message is sent to the view using a ViewBag property.

Replace the code in the HttpPost Delete method (named DeleteConfirmed) with the following code:

<HttpPost()>
<ValidateAntiForgeryToken()>
Async Function Delete(ByVal department As Department) As Task(Of ActionResult)
    Try
        db.Entry(department).State = EntityState.Deleted
        Await db.SaveChangesAsync()
        Return RedirectToAction("Index")
    Catch ex As DbUpdateConcurrencyException
        Return RedirectToAction("Delete", New With {.concurrencyError = True, .id = department.DepartmentID})
    Catch dex As DataException
        'Log the error 
        ModelState.AddModelError(String.Empty, "Unable to delete. Try again, and if the problem persists contact your system administrator.")
        Return View(department)
    End Try
End Function

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

Async Function DeleteConfirmed(ByVal id As Integer) As Task(Of ActionResult)

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

Async Function Delete(ByVal department As Department) As Task(Of ActionResult)

You have also changed the action method name from DeleteConfirmed to Delete. The scaffolded code named the HttpPost Delete method DeleteConfirmed to give the HttpPost  method a unique signature. ( The CLR requires overloaded methods to have different method parameters.) Now that the signatures are unique, you can stick with the MVC convention and use the same name for the HttpPost and HttpGet delete methods.

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.vbhtml, replace the scaffolded code with the following code that adds an error message field and hidden fields for the DepartmentID and RowVersion properties. The changes are highlighted.

@ModelType ContosoUniversity.Models.Department
@Code
    ViewBag.Title = "Delete"
End Code

<h2>Delete</h2>

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

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>Department</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            Administrator
        </dt>

        <dd>
            @Html.DisplayFor(Function(model) model.Administrator.FullName)
        </dd>

        <dt>
            @Html.DisplayNameFor(Function(model) model.Name)
        </dt>

        <dd>
            @Html.DisplayFor(Function(model) model.Name)
        </dd>

        <dt>
            @Html.DisplayNameFor(Function(model) model.Budget)
        </dt>

        <dd>
            @Html.DisplayFor(Function(model) model.Budget)
        </dd>

        <dt>
            @Html.DisplayNameFor(Function(model) model.StartDate)
        </dt>

        <dd>
            @Html.DisplayFor(Function(model) model.StartDate)
        </dd>

    </dl>
    @Using (Html.BeginForm())
        @Html.AntiForgeryToken()
        @Html.HiddenFor(Function(model) model.DepartmentID)
        @Html.HiddenFor(Function(model) model.RowVersion)
        
        @<div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    End Using
</div>

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:

<dt>
    Administrator
</dt>
<dd> 
    @Html.DisplayFor(Function(model) model.Administrator.FullName) 
</dd>

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

@Html.HiddenFor(Function(model) model.DepartmentID) 
@Html.HiddenFor(Function(model) model.RowVersion)

Run the Departments Index page. Right click the Delete hyperlink for the English department and select Open in new tab, then in the first tab click the Edit hyperlink for the English department.

In the first window, change one of the values, and click Save :

MVC5 With EF6

The Index page confirms the change.

MVC5 With EF6

In the second tab, click Delete.

MVC5 With EF6

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

MVC5 With EF6

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

Summary

This completes the introduction to handling concurrency conflicts. For information about other ways to handle various concurrency scenarios, see Optimistic Concurrency Patterns and Working with Property Values on MSDN. The next tutorial shows how to implement table-per-hierarchy inheritance for the Instructor and Student entities.

You might also like...

Date Posted:
Last Updated:
Posted by:
Total Views to date: 16475

0 Comments

Recent Comments

Cyrus 26/05/2017 06:00
In response to Razor Pages - The Elevator Pitch
There are some concern about razor pages performance. Is it faster or slower than MVC? would you a a...

Cyrus 26/05/2017 05:44
In response to Razor Pages - Understanding Handler Methods
well done, thank you....

Satyabrata Mohapatra 23/05/2017 11:41
In response to Razor Pages - Understanding Handler Methods
Nice and easy !! Great post....

Cyrus 16/05/2017 19:55
In response to Razor Pages - Getting Started With The Preview
There is something wrong related to microsoft.dotnetcore.mvc.taghelpers! if you remove it from page...

Cyrus 16/05/2017 10:18
In response to Razor Pages - Getting Started With The Preview
well done mike, it was very useful, I really appreciate that....

Satyabrata Mohapatra 16/05/2017 07:21
In response to Razor Pages - Getting Started With The Preview
Finally!!!! web pages in asp.net core!!! Super excited !!!! Thank u sir for sharing.....Awaiting on...

Daniele 14/03/2017 10:24
In response to Working With Zip Files In ASP.NET MVC
is it possible give to the user a progress bar of the zipping process? Thanks in advance. ...

Suraj 13/03/2017 22:20
In response to Working With Zip Files In ASP.NET MVC
Very nice article. Thanks....

Satyabrata Mohapatra 19/02/2017 03:01
In response to Free SSL Certificates On IIS With LetsEncrypt
Thanks for sharing. Learned a lot !!...

Gfw 03/02/2017 09:48
In response to Free SSL Certificates On IIS With LetsEncrypt
I have used WinSimple for about the last 9 months - works great. One thing that you want to make of...