Handling concurrency in Code First ASP.Net 5.0 MVC applications
In this post I am going to provide you with a hands on example on how to handle concurrency in an EF Code First ASP.Net MVC 5.0 application. Basically I want to show you how to handle conflicts when multiple users update the same entity at the same time.
You can find another post regarding concurrency with EF Database First and web forms here.
When we are talking about concurrency in an application where EF acts as the data access layer, our main focus is how to make sure that the integrity of the data in the database is ensured in a multi user connected environment.
Databases are very good in managing concurrent users. All the objects/entities are retrieved from the datastore into the memory. The changes we make are applied in the memory and then we flush the changes to the database.
But what happens if those entities have been changed from another user in the time it took us to update them in memory?Well someone will say, "Dude, you just have to use pessimistic concurrency and your problems are solved."
Well, I do not want to go down that road. Locks are applied to all related data when I use this type of concurrency management.
Remember we are in a multiuser enviroment. That means I want to have data integrity on the one hand but I also want performance to be at an acceptable level. With pessimistic concurrency you limit scalability and performance. The Entity Framework provides no built-in support for it.
So I will use optimistic concurrency which in plain words mean "Hm... I want to check if anyone has modified the data in the database while I did my own modifications in memory".
The very basic scenario in a multi-user environment where a concurency conflict takes place is 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.
I will use Visual Studio 2015 Enterprise edition,C# and EF Code First 6.1.3 version to build an MVC 5.0 application that
- will list information about a database that holds information about footballers
- will enable the user to create a new footballer
- will enable the user to edit an existing footballer
- will enable the user to delete an existing footballer
1) I am creating an ASP.Net Web Application, namely ConcurrencyViolationEFCodeFirst. I will use the ASP.Net 4.5.2 Empty (MVC template).I have an application with no models,views or controllers.
2) Now I will add a model to the application.I will use this model to manage the footballer's data-entities.
I will use Entity Framework as the data access technology. More specifically I will use the Code First paradigm. You might have come across the term POCO classes.This is exactly what I am going to use.If you want to learn more about Code First have a look in this post .
I need to install Entity Framework. I can do that from the Nuget Package Manager.
Then from the model class we will create our database.In the Solution Explorer right-click on the Models folder,select Add and then select Class.
Name the class Footballer.cs and add some properties to the class.My complete class follows
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
public DateTime JoinedClub { get; set; }
}
3) Let me explain what I am doing here. I will use the Footballer class to represent footballers in a database (which I am going to create). Each instance of a Footballer class will correspond to a row within a database table.Naturally each property of the Footballer class will map to a column in the table (which I am going to create).
We need to add some more code to a separate class the FootballerDBContext.cs
public class FootballerDBContext : DbContext
{
public DbSet<Footballer> Footballers { get; set; }
}
The FootballerDBContext is a database context class.This class is responsible for talking to the underlying database,storing and updating the data to the database.
We need to add this reference to the file
using System.Data.Entity;
4) Now we need to create the connection string.The only place we can do that is by opening the web.config file and adding the following lines of code (inside the <connectionStrings> section)
<add name="FootballerDBContext"
connectionString="Data Source=(LocalDb)\v11.0;AttachDbFilename=|DataDirectory|\Footballers.mdf;Integrated Security=True"
providerName="System.Data.SqlClient"
/>
As you can see from the connection string I am using LocalDB.
Have a look here in order to see what LocalDB is.
5) Now we need to access our model from a controller.This is going to be a simple class that retrieves the footballers data.
Right-click the Controllers folder and create a new FootballerController controller. We will use the scaffolding system of ASP.Net MVC to create our controllers class and its action methods for edit,list,create, delete and the views needed to dispay the data to the user. We will use the Scaffold MVC 5 Controller with views, using Entity Framework.
The model class would be:Footballer (ConcurrencyViolationEFCodeFirst.Models)
The Data Context class would be:FootballerDBContext (ConcurrencyViolationEFCodeFirst.Models)
6) The ASP.NET MVC 5 framework as you can see automatically creates the CRUD (create, read, update, and delete) action methods and views.We have a fully functional web application that lets you create, list, edit, and delete records.
7) Build and run your application.Navigate to the localhost/youport/footballer
8) You can create a new footballer object-record.This information will be saved in the LocalDB-Footballers.mdf database.It is created by EF when you first run the application.
9) Make sure you add more entries to the database through the view/application.
We have created a new record and stored it in the database.Click the Edit,Details and Delete links.We have all this functionality out of the box through the magic of scaffolding.
I urge you to have a look (place breakpoints as well) in the FootballerController.cs class file.
We pass a strongly typed object (Footballer) to the various views.
Have a look in the views inside the Views/Footballer folder.
In the Create.cshtml, Delete.cshtml, Details.cshtml, Edit.cshtml, and Index.cshtml Views , at the beginning of these files, you will see this line of code.
@model IEnumerable<ConcurrencyViolationEFCodeFirst.Models.Footballer>
By adding a @model statement at the top of the view file, we tell the view the type of object that the view should render.
10) When running the application and hitting the record (I have added a couple of rows in the database through the UI already) http://localhost:4809/footballer/Edit/1 you can edit the record.Make some changes in the record but before hitting "Save" open the same record in another browser - http://localhost:4809/footballer/Edit/1 and change some other value of the same record- entity and then hit the "Save" button in the first window/browser. The changes will be saved to the database. Hit "Save" in the other second browser window. You will then see that the changes you made initially have been lost and only the changes in the second browser window have been saved. You have lost data integrity. Basically your data is of no value. We will demonstrate below mechanisms where you will not lose your data and handle concurrency exceptions.
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.) If you don't do any coding for concurrency handling, this will happen automatically.
You can prevent changes from the second browser/windows/user from being updated in the database. Typically, you would display an error message, show the current state of the data, and allow him/her to reapply her changes if she/he 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.)
This method ensures that no changes are overwritten without a user being alerted to what's happening.
11) We 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.
Entity framework code first offers two approaches to detect concurrency violations:
- [ConcurrencyCheck] attribute
- [Timestamp] attribute
The first 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. That usually requires a lot of work.
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 then noone else modified that record and your UPDATE / DELETE succeeds.
If the values don't match then someone else has modified the values and your UPDATE / DELETE statement fails.
In the other approach we add a 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 Timestamp attribute specifies that this column will be included in the Where clause of Update and Dlelete commands sent to the database.
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. The SQL server timestamp / rowversion gets translated as a byte[] in .NET code.
12) We change the definition of the public class Footballer by adding the Timestamp attribute to the RowVersion column
public class Footballer
{
public int FootballerID { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public double Weight { get; set; }
public double Height { get; set; }
public DateTime JoinedClub { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; }
}
13) If we build and run the application we will receive an error
"The model backing the 'FootballerDBContext' context has changed since the database was created. Consider using Code First Migrations to update the database"
By adding a property you changed the database model, we need to do a migration. If you want to find out more about Code First Migrations .In the Package Manager Console (PMC), enter the following commands:
Enable-Migrations
Add-Migration RowVersion
Update-Database
14) Now if we look back through Server Explorer in the table we will see a Rowversion column. Now if we build and run the application we will receive no error. Have a look at the picture below.
15) Now we need to change the code inside the HttpPost Edit method with the following code:
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "FootballerID,FirstName,LastName,Weight,Height,JoinedClub,Rowversion")] Footballer footballer)
{
if (ModelState.IsValid)
{
try
{
db.Entry(footballer).State = System.Data.Entity.EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
catch (DbUpdateConcurrencyException ex)
{
ModelState.AddModelError("", "Unable to save changes. The record was modified by another user after you got the original value");
}
}
return View(footballer);
}
If no rows are affected by the UPDATE command (no rows have the original RowVersion value), the Entity Framework throws a DbUpdateConcurrencyException exception.
16) Now we build and run the application again.
When running the application and hitting the record (I have added a couple of rows in the database through the UI already) http://localhost:4809/footballer/Edit/1 you can edit the record.Make some changes in the record but before hitting "Save" ,open the same record in another browser(e.g Opera) - http://localhost:4809/footballer/Edit/1 and change some other values of the same record- entity and then hit the "Save" button in the first browser. You will not have an problems, the data is persisted to the database.
Then hit save in the second browser window. You will then see that the changes you made initially have been lost and only the changes in the second browser window have been saved.
Instead of seeing your changes in the second browser window persisted to the db you will receive the error below
"Unable to save changes. The record was modified by another user after you got the original value"
Now you cannot save data back to the datastore. You can go back to the list of records, get record 1 again from the database and edit/save the new values.
Hope it helps!!!