Concurrency Handling In Entity Framework 6 (Database First Approach)

Introduction

In this article, we will learn how to handle data concurrency conflicts in Entity Framework 6 using database first approach. We will create a console application with Entity Framework database first approach and will handle data concurrency conflict with the optimistic approach. 

What concurrency conflict is?

Concurrency conflict is a situation where  two or more user trying to update same record and whoever updates the database last will overwrites the other user's change's. Application where this is not really critical if some changes are overwritten than no need to handle concurrency conflict. But what if there is a condition if the record is already been modified in the timespan you are about to update that record, than your record should not overwrites currently modified record without confirming. In that case we need to handle concurrency conflict.

There are two ways in which we can handle concurrency conflict.

1. Pessimistic Concurrency

Pessimistic concurrency to resolve concurrency conflict involves the use of database locks. if user wants to read a row from database, user request a lock for read only or for update access. if user lock a row for update access than other user will not be able to request a lock for eighter read only access or update access. But if user request lock for read only access than other users can also lock it for read only access but not for update access.

But managing locks can be complex to programe and can cause performance problems as the number of users of an application increases.

2. Optimistic Concurrency

In optimistic concurrency user does not request a lock while reading a row. When the user tries to update that row, the system must determine whether that record has been modified by another user since it was read.

In genral term or in genral language, we can explain it as, in optimistic concurrency control a version colum of timestamp type is being introduced in the table. Let suppose initially its value is zero ( in actual it stores value in form of bytes not in form of numeric). Now there are two user User1 and User2 who are about to update the same record. To update the record they need to first fetch the record. After fetching they both will get the record with same version value as zero. Now User1 has made the changes and clicked on the save button. While storing the data into table the database will first match the version value is same or not. As User1 have now version value zero which is same to the earlier fetched value zero so it will allow the data to be saved. Now the version value will be increamented to 1 automatically as it is of Timestamp type (Explained below in artical what Timestamp is).

Now User2 also done with his changes and hit the save button. Again database will check the version value, but when User2 has fetched the value to update, its version value was zero at that time, but as User1 has already updated the record so in table version value is changed to 1. So there will be no version match and database will not allow user to update the record. we will see this example through code below.

Optimistci concurrency gives a better performance as no lock required and locking of records requires additional server resource.

Using the code

We will be creating a console application to explore how concurrency conflict can be handled in Entity Framework database first approch. Steps involved are explained below:-

Steps

1. Using Visual Studio, create a console application(File ->New->Project->Console Application(From Visual C# Templates)) and name it as ConcurrencyConflicthandelExample.

2. Install EntityFramework Nuget package in this console application. Run the "Install-Packag EntityFramework" command in Package Manager Console to do so. Alternatively you can install the same from Tools-> Nuget Package Manager->Manage Nuget Packages for Solution.

After installing Entity Framework you can expand Refrences and check for Entity Framework dll's being added there.

3.Now right click on project name and click Add->New Item. From there choose Data-> ADO.NET Entity Data Model.  Name it EmployeeManagement. Click on Add.

After clicking on Add button Entity Data Model Wizard will be opened. As we are working here on Entity Framework database first approach choose EF Desiggner from Database. and click Next.

On cliking Next there will be a option of Choose Your Data Connection. Click on new Connection here. Connection Properties dialog box will be opened. Choose your Data source. Choose your server name of database. Choose log on type. After choosing log on type and providing necessary credential, Connect to a Database option will be enabled. In the option box it will list all the database present on that server. Choose your database name.You can test your connection status by clickingg on Test connection option. Now click ok.

After clicking on Ok button all the fields over wizard will be filled. The field which is marked with black is the database we are going to use in our application. The field marked with red is a full connection string which will be used in configuration file. The field which is marked in yellow is the context class file name which is responsible for entracting with database. By default Entity Framework will append Entity to the name of database choosen and that will be your context file name. But if you want you can change it also. Click Next.

After clicking on Next Choose Your Database Objects And Settings option will be there. Expand the table option and choose all the table you want to work on. Click Finish.

On click of Finish your all set up is ready. Entity Framework will genrate all the tt files,context files, edmx files. on click of finish you will see a warning dialoug box promtup saying ''Running this text template can pottentially harm your computer.Do not run it if you obtained it from an untrusted source." This warning is just because of your tt files are being changed internally. Just click ok for it. you will see your edmx files are ready with all the relationships. 

This is all code setup. Now we will perform some action on what our main agenda is that is concurrency conflict handling.

5. Open the Table in Database in which you want to handle the concurrency conflict. In my case it is Department table.

6. Add a new column with name versionholder (you can give any Suitable name). choose its datatype as timestamp. Timestamp exposes automatically genrated, unique binary number within a database having storage size of 8 bites.

7. Now open .edmx file of your project in visual studio. update the table diagram of Department by right clicking any where in the .edmx file and selecting Update Model from Database option.

You will notice in department model there is no versionholder column yet which we have currently added in database.

8. On clicking update model a update wizard dialog box will be open. go to the refresh option and select table and than table name.

Now you will see , the column we have added in our Database is now being added to our .edmx file too.

Note* 

If in any case you want to rename the added Versionholder column or existing column from the database than in updating edmx model you might face problem of having two property in the model. First property will be earlier one and second will be with updated name and .cs file will also contain two property. In that case just delete the edmx model by right clicking on the head of the model and select option "Delete from the model". After deleting the model, again Add that model by right clicking on .edmx file anywhere and selecting "Update  Model from the database ". Here from the update wizard, choose the Add option and select tabel name from the table option which you have deleted currently.

9. Now right click on the newly added property and choose Properties option.

10. From the properties under the Genral set Concurrency Mode to Fixed from None. Setting concurrency mode to Fixed only make the version value to auto increament with every update.

This was the whole setup to demonstrate the example explained in defination of optimistic concurrency above. Now we will see it by code how it works. 

Below is the whole code structure to handle concurrency conflict. i will explain the code below.

namespace ConcurrencyConflictHandelExample
{
    class Program
    {
        static void Main(string[] args)
        {
            Department User1 = null;
            Department User2 = null;

            //First User Brings The Data
            using (TestDBEntities context = new TestDBEntities())
            {
                User1 = context.Departments.Find(2);

            }
            //Second User Brings the same Data
            using (TestDBEntities context = new TestDBEntities())
            {
                User2 = context.Departments.Find(2);

            }
            //User1 updating the record
            using (TestDBEntities context = new TestDBEntities())
            {
                context.Departments.Attach(User1);
                User1.Credits += 1;
                context.SaveChanges();

            }
            //User1 updating the same record
            using (TestDBEntities context = new TestDBEntities())
            {
                context.Departments.Attach(User2);
                User2.Credits += 2;
                context.SaveChanges();

            }

            Console.Read();
        }
    }
}

Firstly we created two user who will update the department data.

            Department User1 = null;
            Department User2 = null;

Now using context classes both the user will fetch the same record of department table.

           //First User Brings The Data
            using (TestDBEntities context = new TestDBEntities())
            {
                User1 = context.Departments.Find(2);

            }

            //Second User Brings the same Data
            using (TestDBEntities context = new TestDBEntities())
            {
                User2 = context.Departments.Find(2);

            }

Here both the user will get the same data of department with same version value. Let suppose here version value both user got is zero.

Now User1 will update the department record.

            //User1 updating the record
            using (TestDBEntities context = new TestDBEntities())
            {
                context.Departments.Attach(User1);
                User1.Credits += 1;
                context.SaveChanges();

            }

Here waht happens is while saving Entity Framework will first match the version value of this updated record with the existing record. As no updates till now has been done so there will be no change in the version value and the comparision will be true and data will be saved. you can check below what querry is being genrated by the Entity Framework doing this opration.

Now User2 will update the department data.

            //User2 updating the same record
            using (TestDBEntities context = new TestDBEntities())
            {
                context.Departments.Attach(User2);
                User2.Credits += 2;
                context.SaveChanges();

            }

As User2 still have the version value zero so when it will go for saving data after doing manupulation the version value will not match because it has been changed when User1 has updated the record. we will see it in sql profiler what version value is there in querry being genrated by Entity Framework.

You can see the Credits  value is different i.e 43 as set by User2 but  version value is same as of User1. But the actual value of version is being changed so it will not match and throw a 'DbUpdateConcurrencyException'.

We can use this exception to warn second user on screen that record has been modified . This is how we can handle concurrency conflict.

But what if there is requirement like not to show the warning in case of concurrency conflict but to handle it with in the code itself. Yes we can do it too. Let see this through code.

Idea here is getting the current database value and setting them as the original values for the entity.

            //User1 updating the same record
            using (TestDBEntities context = new TestDBEntities())
            {
                context.Departments.Attach(User2);
                User2.Credits +=2;
                try
                {
                    context.SaveChanges();
                }
                catch(DbUpdateConcurrencyException ex)
                {
                    var value = ex.Entries.Single();
                    value.OriginalValues.SetValues(value.GetDatabaseValues());
                    context.SaveChanges();
                   
                }

            }

Now it will save the value without throwing exception.

Conclusion

In this artical we learned how to cofigure the Entity Framework Database First Approach and handling concurrency conflict by optimistic approach.