Optimistic Concurrency in Entity Framework Code First

• Download ConcurrencyCheck.zip - 8.1 MB
• Download ConcurrencyCheck(Packages_Excluded).zip - 10.4 KB

Introduction

This article describes different configurations that can be applied to handle optimistic concurrency using Entity Framework Code First.

Concurrency

In computer science, concurrency is a property of systems in which several computations are executing simultaneously, and potentially interacting with each other.

In web application, which is a multi-user environment, there is possibility of concurrency while saving the data in the database. The concurrency is broadly classified as two types: 1) Pessimistic Concurrency 2) Optimistic Concurrency 

1) Pessimistic Concurrency

Pessimistic concurrency in database involves locking of rows to prevent other users for modifying data in a way that affects the current user. 

In this approach, user performs an action by which a lock is applied and other users cannot perform the same action on that record until that lock is released.

2) Optimistic Concurrency

By contrast, in optimistic concurrency the row is not locked when a user is reading it. 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.

Using the code

Let's create a console application to explore the different options for handling the optimistic concurrency.

Steps

1. Using Visual Studio, create a console application(File ->New->Project->Console Application(From Visual C# Templates)) and name it as ConcurrencyCheck.
2. Add a new folder Models to the project. Add two class files: EducationContext.cs and Student.cs inside this folder.
3. Install EntityFramework Nuget package in this console application. Run the "Install-Package EntityFramework" command in Package Manager Console to do so. Alternatively you can install the same from "Nuget Package Manager" window. 

The following table shows different configurations for optimistic concurrency.

1) Convention

Entity Framework Code First doesn't have any convention for handling optimistic concurrency. You can use Data Annotation or Fluent API for handling optimistic concurrency.

2) Data Annotation

Code First uses [Timestamp] attribute for handling optimistic concurrency.

a) Modify the EducationContext.cs file as follows:

using System.Data.Entity;

namespace ConcurrencyCheck.Models
{
    class EducationContext : DbContext
    {
        public EducationContext()
            : base("EducationContext")
        {
        }

        public DbSet<Student> Students { get; set; }
    }
}

Here the base("EducationContext") instructs Code First to use the connection string having name "EducationContext" from the App.config file.

b) Modify Student.cs file as follows:

using System.ComponentModel.DataAnnotations;

namespace ConcurrencyCheck.Models
{
    public class Student
    {
        public int StudentId { get; set; }

        public string RollNumber { get; set; }

        public string FirstName { get; set; }

        public string LastName { get; set; }

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

Please note that there is a property RowVersion inside Student class which is of type byte[] and is assigned with [Timestamp] attribute for handling the optimistic concurrency,.

c) Change the connection string in the App.config file to point to a valid database:

<connectionStrings>
    <add name="EducationContext" providerName="System.Data.SqlClient" connectionString="Server=DUKHABANDHU-PC; Database=ConcurrencyCheck;Integrated Security=SSPI" />
</connectionStrings>

Here we have given the database name as ConcurrencyCheck which will be created by Code First when the application will run.

d) Modify the Program.cs file to drop and create the database every time when the application runs:

static void Main(string[] args)
{
    Database.SetInitializer(new DropCreateDatabaseAlways<EducationContext>());

    using (var context = new EducationContext())
    {
        context.Students.Add(new Student
        {
            FirstName = "Dukhabandhu",
            LastName = "Sahoo",
            RollNumber = "1"
        });

        context.SaveChanges();
    }

    Console.WriteLine("Database Created!!!");
    Console.ReadKey();
}

If you run the application, Code First will create the database ConcurrenCheck having two tables _MigrationHistory and Students.

If you see the RowVersion column in Students table(in SQL Sever) its data type is timestamp.

RowVersion and TimeStamp are the two terms used by different database providers for the same purpose. When a record in the Students table is created or updated, database updates the RowVersion value to a new value automatically. Even if you send value for RowVersion column, database(SQL Server) doesn't use that value for inserting or updating the row version.

The SQL generated when a new record added to the Students table:

exec sp_executesql N'INSERT [dbo].[Students]([RollNumber], [FirstName], [LastName])
VALUES (@0, @1, @2)
SELECT [StudentId], [RowVersion]
FROM [dbo].[Students]
WHERE @@ROWCOUNT > 0 AND [StudentId] = scope_identity()',N'@0 nvarchar(max) ,@1 nvarchar(max) ,@2 nvarchar(max) ',@0=N'1',@1=N'Dukhabandhu',@2=N'Sahoo'

You can see the query not only inserts a new record but also returns the value of the RowVersion.

The actual concurrency check occurs when UPDATE and DELETE operation takes place. See below how the concurrency check is happening when updating and deleting a record from Students table.

UPDATE SQL

exec sp_executesql N'UPDATE [dbo].[Students]
SET [RollNumber] = @0 WHERE (([StudentId] = @1) AND ([RowVersion] = @2))
SELECT [RowVersion] FROM [dbo].[Students]
WHERE @@ROWCOUNT > 0 AND [StudentId] = @1',N'@0 nvarchar(max) ,@1 int,@2 binary(8)',@0=N'2',@1=1,@2=0x00000000000007D1

See the WHERE condition, it compares both the StudentId(Primary Key) and RowVersion when updating the record.

DELETE SQL

exec sp_executesql N'DELETE [dbo].[Students]
WHERE (([StudentId] = @0) AND ([RowVersion] = @1))',N'@0 int,@1 binary(8)',@0=1,@1=0x00000000000007D1

Here also before deleting the record Code First creates query to compare both the identifier(i.e primary key StudentId) and row version(RowVersion field) for optimistic concurrency.

3) Fluent API

The fluent API uses IsRowVersion() method for configuring optimistic concurrency.

To test the fluent API configuration, remove the [Timestamp] attribute from the RowVersion property of Students class and override OnModelCreating() method inside the EducationContext class as follows:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>().Property(s => s.RowVersion).IsRowVersion();
    base.OnModelCreating(modelBuilder);
}

Configuration for Non-Timestamp fields

Without keeping dedicated columns for concurrency check, you can still handle the concurrency. There are some databases which don't support column of RowVersion/Timestamp type. In these type scenarios, you can configure one or more fields for concurrency check using Data Annotation or Fluent API configuration.

1) Data Annotation

Modify the Student class as follows to use the [ConcurrencyCheck] Data Annotation attribute:

public class Student
{
    public int StudentId { get; set; }

    [ConcurrencyCheck]
    public string RollNumber { get; set; }

    public string FirstName { get; set; }

    public string LastName { get; set; }
}

When the application runs, Code First creates the Students table(see the below image). The database doesn't do anything special for the [ConcurrencyCheck] attribute in the RollNumber column.

But when any modification/change happens to the Students table, Code First takes care of the concurrency check. Read next how Code First creates UPDATE and DELETE queries taking concurrency check into consideration.

UPDATE SQL

exec sp_executesql N'UPDATE [dbo].[Students]
SET [RollNumber] = @0
WHERE (([StudentId] = @1) AND ([RollNumber] = @2))
',N'@0 nvarchar(max) ,@1 int,@2 nvarchar(max) ',@0=N'2',@1=1,@2=N'1'

Note the WHERE condition. It compares both the StudentId(Primary Key) and RollNumber when updating the record.

DELETE SQL

exec sp_executesql N'DELETE [dbo].[Students]
WHERE (([StudentId] = @0) AND ([RollNumber] = @1))',N'@0 int,@1 nvarchar(max) ',@0=1,@1=N'2'

While a record from the Students table is deleted, it also checks the StudentId and RollNumber column values. If the RollNumber column value is changed since it was read, and you are now updating that record then you will get an OptimisticConcurrencyException.

2) Fluent API

Use Code First's IsConcurrencyToken() method to handle concurrency for non-timestamp fields.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<Student>().Property(s => s.RollNumber).IsConcurrencyToken();
    base.OnModelCreating(modelBuilder);
}

Points of Interest

To test the concurrency effect, add code to update an existing record from the Students table like follows:

var student = context.Students.FirstOrDefault(u => u.StudentId == 1);

if (student != null)
{
    student.RollNumber = "2";
    context.SaveChanges();
}

Add a break point in Visual Studio on the context.SaveChanges(); line. Before the SaveChanges() method is executed, modify the Students table record in database where the StudentId =1.

UPDATE Students SET RollNumber = '123' WHERE StudentId = 1;

Now if you will execute the next line i.e context.SaveChanges() then you will get an exception as follows:

DbUpdateConcurrencyException:

Store update, insert, or delete statement affected an unexpected number of rows (0). Entities may have been modified or deleted since entities were loaded. Refresh ObjectStateManager entries.

The DbUpdateConcurrencyException is thrown because the record is already modified since it was read. 

Conclusion

In this article we learned how to configure the Entity Framework Code First to handle the optimistic concurrency either by keeping dedicated fields in the table or by adding special data annotation attributes or Fluent API configuration.