Entity Framework & Concurrency

ADO.NET Entity Framework is the Object Relational Mapping(ORM) framework from Microsoft which enables us to map .NET classes to objects(tables,views,stored procedures..etc) in a relational database.While studying any ORM framework it’s important to know about, how it handles concurrency and ensures data consistency.In this post we will look into what options ADO.NET Entity Framework provides for ensuring this consistency.

We will start with this simple code snippet which fetches data from the Course table and prints in the Console.

static void Main(string[] args)
{
    using(SchoolEntities school = new SchoolEntities())
    {
        var c = (from course in school.Courses
                      where course.CourseID==2021
                      select course).Single<Course>(); 

                Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
    }
    Console.Read();
} 

This program prints :

Course ID: 2021 , Course Name: Composition

Now let’s add some code to update this record of the Course table.

static void Main(string[] args)
{
    using(SchoolEntities school = new SchoolEntities())
    {
        var c = (from course in school.Courses
                      where course.CourseID==2021
                      select course).Single<Course>(); 

        Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
        //Do Something
         Thread.Sleep(1000);
         c.Title = "English Composition";
         school.SaveChanges(); 

    }
    Console.Read();
} 

Here I have added a Thread.Sleep to introduce  a delay between data fetch and data save.During execution time when program halts at this step I fired the following SQL from SQL Server Management Studio.

 
UPDATE    Course SET       Title = 'XX'  WHERE     (CourseID = 2021) 

On execution of this program there is no exception and in the database Course Title column is updated with the value “English Composition“.

Using SQL Profiler we can see that the following SQL is fired:

 
exec sp_executesql N'update [dbo].[Course]
set [Title] = @0
where ([CourseID] = @1)
',N'@0 nvarchar(100),@1 int',@0=N'English Composition',@1=2021 

So what’s happening? Where are the concurrency checks?

By default Entity Framework does not check for concurrency.It supports an optimistic concurrency model and concurrency needs to be specifically enabled at individual property level of an entity class.

The model(.edmx file) when opened using text editor we will see that the model runtime contains three sections be Storage Model(SSDL),Conceptual Model(CSDL) and Mappings(MSDL) as shown below:

 

We need to set the ConcurrencyMode attribute for the property Title of Course entity in the Conceptual Model section to Fixed as shown below:

 

  Now if we execute the program and fire the update statement in between we will get a OptimisticConcurrencyException with the following message:

{“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.”}

So what happened now? To get an understanding of this situation we need to take a look at the update statement being fired:

 
exec sp_executesql N'update [dbo].[Course]
set [Title] = @0
where (([CourseID] = @1) and ([Title] = @2))
',N'@0 nvarchar(100),@1 int,@2 nvarchar(100)',@0=N'English Composition',@1=2021,@2=N'Composition' 

Here the WHERE Clause of the UPDATE contains the Title column and checks for the previous value.In between this column is updated with ‘XX’ so this statement fails to update any rows and throws the exception.

We can handle this exception and either accept the value stored in .NET object or database.The framework provides the following method of ObjectContext class to do so:

• public void Refresh(RefreshMode refreshMode,Object entity)
  • RefreshMode enumeration supports two values:
    • ClientWins – The data stored in .NET object is retained and written back to database on next call of SaveChanges method.
    • StoreWins – The data stored in .NET object is refreshed with data from database.
  • The entity object which is refreshed

  The following code uses ClientWins option:

 
static void Main(string[] args)
{
     using(SchoolEntities school = new SchoolEntities())
     {
         var c = (from course in school.Courses
                       where course.CourseID==2021
                       select course).Single<Course>(); 

         Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
         //Do Something
          Thread.Sleep(1000);
          c.Title = "English Composition";
          try
          {
              school.SaveChanges();
          }
          catch (OptimisticConcurrencyException oex)
          {
              school.Refresh(System.Data.Objects.RefreshMode.ClientWins,c);
              Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
              school.SaveChanges();
          } 

     }
     Console.Read();
} 

After execution of this program (including the update statement fired in between from SQL Management Studio) the value of Title column is updated to “English Composition”.

The following SQL Statements were executed in background:

During call of First SaveChanges

 
exec sp_executesql N'update [dbo].[Course]
set [Title] = @0
where (([CourseID] = @1) and ([Title] = @2))
',N'@0 nvarchar(100),@1 int,@2 nvarchar(100)',@0=N'English Composition',@1=2021,@2=N'Composition' 

This statement is already discussed.

During call of Refresh

 
SELECT 
[Extent1].[CourseID] AS [CourseID], 
[Extent1].[Title] AS [Title], 
[Extent1].[Credits] AS [Credits], 
[Extent1].[DepartmentID] AS [DepartmentID]
FROM [dbo].[Course] AS [Extent1]
WHERE [Extent1].[CourseID] = 2021 

This select statement fetches the latest Course record from database.

During call of next SaveChanges

 
exec sp_executesql N'update [dbo].[Course]
set [Title] = @0, [Credits] = @1, [DepartmentID] = @2
where (([CourseID] = @3) and ([Title] = @4))
',N'@0 nvarchar(100),@1 int,@2 int,@3 int,@4 nvarchar(100)',@0=N'English Composition',@1=3,@2=2,@3=2021,@4=N'XX'

This update statement is bit different from the previous one as it updates all the columns and uses the latest value of Title in the WHERE clause. 

 

The following code uses the StoreWins option:

 
static void Main(string[] args)
{
     using(SchoolEntities school = new SchoolEntities())
     {
         var c = (from course in school.Courses
                       where course.CourseID==2021
                       select course).Single<Course>(); 

         Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
         //Do Something
          Thread.Sleep(1000);
          c.Title = "English Composition";
          try
          {
              school.SaveChanges();
          }
          catch (OptimisticConcurrencyException oex)
          {
              school.Refresh(System.Data.Objects.RefreshMode.StoreWins,c);
              Console.WriteLine("Course ID: {0} , Course Name: {1}", c.CourseID, c.Title);
          } 

     }
     Console.Read();
} 

Here the .NET object is refreshed with values from database and the program prints out:

Course ID: 2021 , Course Name: Composition
Course ID: 2021 , Course Name: XX

Before we end this discussion I would like to point out one more thing related to the update statement used to check optimistic concurrency.Here all the columns on which concurrency is enabled is included in the WHERE clause and this might degrade the performance of the SQL to a great extent.So it’s advisable to use a version column with Timestamp datatype as mentioned here