Click here to Skip to main content
Click here to Skip to main content

Transaction Isolation in ADO.NET Entity Framework

, 15 May 2011
Rate this:
Please Sign up or sign in to vote.
This article presents an example on how to control the transaction isolation level in ADO.NET Entity Framework.

Introduction

This article presents an example on how to control the transaction isolation level in ADO.NET Entity Framework.

Background

In the last couple of decades, the IT industry saw two major advancements, the "Object Oriented Programming" and the "Entity Relation Model". The "ADO.NET Entity Framework", as well as other "Object Relation Mapping" tools, such as "Hibernate" and "NHibernate", represent the effort to bridge the two wonderful mainstream theoretical and practical human achievements.

Despite the fundamental differences between the perspectives of the "OOP" and "ERM" to view the world, the "ORM" tools have achieved a large degree of success, mainly due to the automatic code-generation capabilities. If you like the automatic code generation capabilities, the "ORM" tools may be good fits for you. But you need to be prepared to address some of the problems when using them. This article is to use a simple example to address one of the problems when we use "ADO.NET Entity Framework". The problem is how to control the transaction isolation level.

SExplorer.JPG

The attached Visual Studio solution is developed in Visual Studio 2010. The "EFTransactionExample" is a simple WPF "MVVM" application. The "EmployeeModel.edmx" file is the "Entity Model" generated by the "Entity Framework" based on a SQL server database. The "MainWindow.xaml" file is the main UI window for this WPF application. The "Model" and the "View Model" classes are in their corresponding folders. The "Utilities.cs" file implements some utility classes to help the "MVVM" bindings in this WPF application.

I will first introduce the SQL server database that we will be using in this example. When I worked on this example, I created the database in a SQL server 2008 running on my local computer. This article is not to teach how to use "ADO.NET Entity Framework". It assumes that you have some basic knowledge on Entity Framework, transaction isolation, and multi-threading. If you are not familiar with these subjects, you can easily find reference materials. In this example, I used windows authentication to connect to the database from the WPF application. If you want to run the example, you will need to make sure that your login name to the computer has sufficient permissions to make the database operations.

The SQL Server Database

To keep the example simple, I created a single table database.

EmployeeSalaryDB.JPG

If you want to run the attached example, you can create this database in your own environment using the following SQL script:

USE [master]
GO
 
IF  EXISTS (SELECT name FROM sys.databases
 WHERE name = N'EmployeeSalary')
BEGIN
 ALTER DATABASE [EmployeeSalary] SET SINGLE_USER WITH NO_WAIT
 DROP DATABASE [EmployeeSalary]
END
GO
 
CREATE DATABASE [EmployeeSalary]
GO
 
USE [EmployeeSalary]
GO
 
CREATE TABLE [dbo].[Employee](
 [ID] [int] NOT NULL,
 [Name] [varchar](200) NOT NULL,
 [Salary] [int] NOT NULL,
CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
([ID] ASC )
 WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
 IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON,
 ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
 
INSERT INTO [Employee] ([ID], [Name], [Salary])
VALUES (1, 'Barack Hussein Obama II', 100)

Upon a successful run of the script, you should be able to create a database [EmployeeSalary] in your server. You should also have a table [Employee] in this database. The [Employee] should have one row of data shown as the following:

SingleRowDB.JPG

This is all that we need on the database to proceed with our example. It is an ultra "simple" database, but it is "complex" enough for us to study "Transaction Isolation". Let us now assume that we have two users to access this database concurrently.

  • The CPO - a chief political officer
  • The CEO - a chief economical officer

Let us assume that both officers are happy about the jobs from the employee "Barack Hussein Obama II". Each wants to raise his salary by $50. Each user will first read the employee's current salary and add $50 to it and save the result back to the database. After both officers finish their work, the employee's correct salary should be $200. But the database operation from each user is not automatically "atomic". Each user will go through three steps to give the employee the raise.

  • Read the current salary from the database.
  • Add $50 to the current salary.
  • Save the result back to the database.

If the CPO first reads the current salary, but before he makes the change to the database, the CEO also reads the salary, both officers will get the current salary value $100. If each gives the employee a $50 raise from $100 and saves the result to the database, when they both complete, the employee's end salary shown in the database will be $150. This is obviously not the desired result. In order that "Barack Hussein Obama II" gets the correct raise that he deserves, we need to properly "isolate" the operations of the two officers. There are a couple of pre-defined "Transaction Isolation Levels". In this case, we will need the isolation level to be "Serializable".

Modern "Database Management Systems" have established some very efficient and mature transaction isolation mechanism. Since the "ADO.NET Entity Framework" is a layer of object oriented abstraction above the relational database, the transaction isolation in the Entity Framework is not as efficient as when we work on the SQL server directly. But it is still supported. Before we talk about how to control the transaction isolation in Entity Framework, I will show you how to create the "Entity Model" from the [EmployeeSalary] database in our project.

The Entity Model "EmployeeModel.edmx"

The "EmployeeModel.edmx" file in the project is generated by the "ADO.NET Entity Data Model" template.

EFTemplate.JPG

You can right click the project file "EFTransactionExample" in the solution explorer to bring up the "Add New Item" window and search for "Entity" to find this template. You can then follow the step by step instructions from the template to create the data model "EmployeeModel.edmx". In case that you are not familiar with Entity Framework, this is a good tutorial that you can take a look at. When you finish adding the data model, Entity Framework creates two classes in the code behind file for the "EmployeeModel.edmx" file.

  • The class "EmployeeSalaryEntities" is a sub-class of the "ObjectContext" class, which provides facilities for querying and working with entity data as objects.
  • The class "Employee" is a sub-class of the "EntityObject" class. It represents an employee in the [Employee] table.

We will be using the "EmployeeSalaryEntities" class to access the database. This class provides a couple of overloaded constructors. If we initiate this class by its default constructor, the database accesses will be using the connection string saved in the "App.Config" file by the Entity Data Model template when we create this data model. In this example, we will be using windows authentication in the connection string.

The Model Class of the Application

Built on the entity model created by the Entity Data Model template, the model class of this MVVM application is implemented in the "ApplicationModel.cs" file in the "Models" folder.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Transactions;
 
namespace EFTransactionExample.Models
{
    class ApplicationModel
    {
        // Get all the information for the employees
        public IEnumerable<Employee> GetEmployeeInformation()
        {
            EmployeeSalaryEntities context = new EmployeeSalaryEntities();
            return context.Employees.AsEnumerable<Employee>();
        }
 
        // Set an employee's salary to a value
        public void SetEmployeeSalary(int employeeID,
            string employeeName, int salary)
        {
            EmployeeSalaryEntities context = new EmployeeSalaryEntities();
            var employee = context.Employees.Where(e => e.ID == employeeID)
                .SingleOrDefault<Employee>();
 
            if (employee == null)
            {
                employee = new Employee()
                {
                    ID = employeeID,
                    Name = employeeName,
                    Salary = salary
                };
 
                context.Employees.AddObject(employee);
            }
            employee.Salary = salary;
            context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
        }
 
        public void RaiseEmployeeSalaryNonSerialized(int employeeID, int amount)
        {
            // Read employee information
            EmployeeSalaryEntities context = new EmployeeSalaryEntities();
            var employee = context.Employees.Where(e => e.ID == employeeID)
                .Single<Employee>();
 
            // sleep a while
            System.Threading.Thread.Sleep(5000);
 
            // Update the salary
            employee.Salary = employee.Salary + amount;
            context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
        }
 
        public void RaiseEmployeeSalarySerialized(int employeeID, int amount)
        {
            TransactionOptions options = new TransactionOptions();
            options.IsolationLevel = IsolationLevel.Serializable;
            using (TransactionScope scope
                = new TransactionScope(TransactionScopeOption.Required, options))
            using (EmployeeSalaryEntities context = new EmployeeSalaryEntities())
            {
                // Open the database connection explicitly
                context.Connection.Open();
 
                // Read employee information
                var employee = context.Employees.Where(e => e.ID == employeeID)
                    .Single<Employee>();
 
                // sleep a while
                System.Threading.Thread.Sleep(5000);
 
                // Update the salary
                employee.Salary = employee.Salary + amount;
                context.SaveChanges(SaveOptions.AcceptAllChangesAfterSave);
 
                scope.Complete();
            }
        }
    }
}

The "ApplicationModel" class has four methods:

  • The "GetEmployeeInformation" method retrieves the list of the employees in the database. In this example, we have only one employee "Barack Hussein Obama II".
  • The "SetEmployeeSalary" sets an employee's salary to a given value. If the employee does not exist, it will create the employee with the "employeeID" and "employeeName" passed in as parameters.
  • The "RaiseEmployeeSalaryNonSerialized" method will raise an employee's salary. This method does not explicitly specify the transaction isolation level.
  • The "RaiseEmployeeSalarySerialized" method is also used to raise an employee's salary. But we explicitly specified the transaction isolation level to be "Serializable". In order for the database to recognize that the read of the employee's starting salary and the update to the employee's salary are in a single transaction, we need to explicitly open the connection of the "EmployeeSalaryEntities" object. The connection will be closed when the "EmployeeSalaryEntities" is out of the scope.

In order to show you the importance of the transaction isolation in concurrent operations, I will let the "CPO" and "CEO" both read the starting salary $100, I created some artificial delay in the code to let the thread to sleep for 5 seconds. In the later code, you will see how the "Serializable" transaction isolation level makes sure that when both officers finish, the employee's salary is $200.

The Binding Utilities for the MVVM Pattern

To help building the MVVM application, I created two utility classes in the "Utilities.cs" file in the "BindingUtilities" folder.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.ComponentModel;
using System.Windows.Input;
 
namespace EFTransactionExample.BindingUtilities
{
    public abstract class ViewModelBase
        : DependencyObject, INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;
 
        protected void NotifyPropertyChanged(string propertyName)
        {
            if (PropertyChanged != null)
            {
                PropertyChanged(this, new PropertyChangedEventArgs(propertyName));
            }
        }
    }
 
    public class RelayCommand : ICommand
    {
        private readonly Action handler;
        private bool isEnabled;
 
        public RelayCommand(Action handler)
        {
            this.handler = handler;
        }
 
        public bool IsEnabled
        {
            get { return isEnabled; }
            set
            {
                if (value != isEnabled)
                {
                    isEnabled = value;
                    if (CanExecuteChanged != null)
                    {
                        CanExecuteChanged(this, EventArgs.Empty);
                    }
                }
            }
        }
 
        public bool CanExecute(object parameter)
        {
            return IsEnabled;
        }
 
        public event EventHandler CanExecuteChanged;
 
        public void Execute(object parameter)
        {
            handler();
        }
    }
}
  • The "ViewModelBase" class will be the base class of the View Model class of this application. It implements the "INotifyPropertyChanged" interface, so we can simply use the "NotifyPropertyChanged" method to inform the XAML view of the application when a binding "Property" is changed.
  • The "RelayCommand" will be used for command binding in this MVVM application.

I have a dedicated article "Data and Command Bindings for Silverlight MVVM Applications" to talk about the MVVM bindings, if you are interested, you can take a look at it.

The View Model

The view model of this application is created in the "MainWindowViewModel.cs" file in the "ViewModels" folder.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using EFTransactionExample.BindingUtilities;
using EFTransactionExample.Models;
using System.Threading;
using System.Windows;
using System.Windows.Threading;
 
namespace EFTransactionExample.ViewModels
{
    class MainWindowViewModel : ViewModelBase
    {
        // Thread safe locker
        static readonly object statuslocker = new object();
        static readonly object employeeInformationlocker
            = new object();
 
        // Private field
        int EmployeeID = 1;
        string EmployeeName = "Barack Hussein Obama II";
        int EmployeeOriginalSalary = 100;
 
        // Properties
        private IEnumerable<Employee> employeeInformation;
        public IEnumerable<Employee> EmployeeInformation
        {
            get { return employeeInformation; }
            private set
            {
                employeeInformation = value;
                NotifyPropertyChanged("EmployeeInformation");
            }
        }
 
        private void UpdateEmployeeInformation()
        {
            lock (employeeInformationlocker)
            {
                ApplicationModel model = new ApplicationModel();
 
                try
                {
                    EmployeeInformation = model.GetEmployeeInformation();
                }
                catch (Exception ex)
                {
                    string msg = "Unable to obtain employee information | "
                        + ex.Message;
                    UpdateStatusInformation(msg);
                }
            }
        }
 
        private string statusInformation;
        public string StatusInformation
        {
            get { return statusInformation; }
            private set
            {
                statusInformation = value;
                NotifyPropertyChanged("StatusInformation");
            }
        }
 
 
        private void UpdateStatusInformation(string msg)
        {
            lock (statuslocker)
            {
                StatusInformation = msg;
            }
        }
 
        private void InitiateViewModel()
        {
            UpdateEmployeeInformation();
        }
 
        // Commands
        public RelayCommand ResetSalaryCommand { get; private set; }
        private void ResetSalary()
        {
            ApplicationModel model = new ApplicationModel();
            model.SetEmployeeSalary(EmployeeID, EmployeeName, EmployeeOriginalSalary);
            EmployeeInformation = model.GetEmployeeInformation();
            UpdateStatusInformation("Reset employee salary completed ..");
        }
 
        public RelayCommand RaiseSalaryNonSerializedCommand { get; private set; }
        private void RaiseSalaryNonSerialized()
        {
            System.Threading.Thread
                CPOThread = new System.Threading.Thread(
                new System.Threading.ParameterizedThreadStart(WorkerNonSerialized));
            CPOThread.Name = "CPO Thread";
 
            System.Threading.Thread
                CEOThread = new System.Threading.Thread(
                new System.Threading.ParameterizedThreadStart(WorkerNonSerialized));
            CEOThread.Name = "CEO Thread";
 
            CPOThread.Start(new object[3] { 1, 50, 0 });
            CEOThread.Start(new object[3] { 1, 50, 2500 });
        }
 
        private void WorkerNonSerialized(object parameters)
        {
            object[] parameterArray
                = (object[])parameters;
            int employeeID = (int)parameterArray[0];
            int amount = (int)parameterArray[1];
            int sleepBeforeStart = (int)parameterArray[2];
 
            Thread.Sleep(sleepBeforeStart);
            UpdateStatusInformation(Thread.CurrentThread.Name + " Started ..");
            try
            {
                ApplicationModel model = new ApplicationModel();
                model.RaiseEmployeeSalaryNonSerialized(employeeID, amount);
                EmployeeInformation = model.GetEmployeeInformation();
            }
            catch (Exception ex)
            {
                string msg = "Unable to raise the salary for the employee | "
                    + ex.Message;
                UpdateStatusInformation(msg);
                return;
            }
 
            UpdateEmployeeInformation();
            UpdateStatusInformation(Thread.CurrentThread.Name + " Finished ..");
        }
 
        public RelayCommand RaiseSalarySerializedCommand { get; private set; }
        private void RaiseSalarySerialized()
        {
            System.Threading.Thread
                CPOThread = new System.Threading.Thread(
                new System.Threading.ParameterizedThreadStart(WorkerSerialized));
            CPOThread.Name = "CPO Thread";
 
            System.Threading.Thread
                CEOThread = new System.Threading.Thread(
                new System.Threading.ParameterizedThreadStart(WorkerSerialized));
            CEOThread.Name = "CEO Thread";
 
            CPOThread.Start(new object[3] { 1, 50, 0 });
            CEOThread.Start(new object[3] { 1, 50, 2500 });
        }
 
        private void WorkerSerialized(object parameters)
        {
            object[] parameterArray
                = (object[])parameters;
            int employeeID = (int)parameterArray[0];
            int amount = (int)parameterArray[1];
            int sleepBeforeStart = (int)parameterArray[2];
 
            Thread.Sleep(sleepBeforeStart);
 
            UpdateStatusInformation(Thread.CurrentThread.Name + " Started ..");
 
            int NoOfTries = 0;
            ApplicationModel model = new ApplicationModel();
            try
            {
                model.RaiseEmployeeSalarySerialized(employeeID, amount);
            }
            catch (Exception ex)
            {
                NoOfTries++;
 
                if (NoOfTries < 3)
                {
                    string msg = Thread.CurrentThread.Name
                       + " Failed to update the salary for the employ, re-trying ...";
                    UpdateStatusInformation(msg);
                }
                else
                {
                    string msg = "Unable to raise the salary for the employee" +
                        ", we have tried 3 times | " + ex.Message;
                    UpdateStatusInformation(msg);
                    return;
                }
 
                model.RaiseEmployeeSalarySerialized(employeeID, amount);
            }
 
            UpdateEmployeeInformation();
            UpdateStatusInformation(Thread.CurrentThread.Name + " Finished ..");
        }
 
        private void WireCommands()
        {
            ResetSalaryCommand = new RelayCommand(ResetSalary);
            ResetSalaryCommand.IsEnabled = true;
 
            RaiseSalaryNonSerializedCommand
                = new RelayCommand(RaiseSalaryNonSerialized);
            RaiseSalaryNonSerializedCommand.IsEnabled = true;
 
            RaiseSalarySerializedCommand
                = new RelayCommand(RaiseSalarySerialized);
            RaiseSalarySerializedCommand.IsEnabled = true;
        }
 
        public MainWindowViewModel()
        {
            InitiateViewModel();
            WireCommands();
        }
    }
}

Built on the "ApplicationModel" class and the binding utility classes, this View Model class implemented two public properties:

  • The "EmployeeInformation" property will be used to display the information for the employees in the database.
  • The "StatusInformation" property will be used to display the information when the "CPO" and "CEO" give raise to the employee.

It also implemented three commands.

  • The "ResetSalaryCommand" will reset the salary of the employee "Barack Hussein Obama II" to $100.
  • The "RaiseSalaryNonSerializedCommand" starts two threads. One thread represents the "CPO" and the other thread represents the "CEO". Each calls the "RaiseEmployeeSalaryNonSerialized" method to give the employee the salary raise. The "CPO" thread starts immediately, but the "CEO" thread will sleep for 2.5 seconds before it starts the database operations. Remember that there is a 5 seconds gap in the "RaiseEmployeeSalaryNonSerialized" method between the read and the update operations. Both threads will be getting $100 as the starting salary. Since we did not enforce the "Serializable" transaction isolation, when they both finish, the employee's final salary will be $150.
  • The "RaiseSalarySerializedCommand" is very similar to the "RaiseSalaryNonSerializedCommand" except that "Serializable" database operations are enforced. When both "CPO" and "CEO" read the starting salary and both try to make change to it, both will need to obtain an "Exclusive Lock". Since they both already obtained a "Shared Lock" on the employee when they make the read, we have a "Deadlock". The SQL Server will pick a victim to resolve the "Deadlock", so the other thread can proceed to update the database. Since we know that one of the threads will be the victim, so we will let it to try to raise the salary again. When it tries again, the other thread should have finished giving the raise, the starting salary will be $150, and when it finishes, the employee will get the well deserved end salary $200.

The XAML View of the Application

The XAML view of this MVVM application is implemented in the "MainWindow.xaml" file.

<Window x:Class="EFTransactionExample.MainWindow"
        xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
        xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
        FontFamily="Calibri"
        Title="MainWindow" Height="350" Width="525">
    
    <Grid Margin="10">
        <Grid.RowDefinitions>
            <RowDefinition Height="auto" />
            <RowDefinition Height="auto" />
            <RowDefinition Height="*" />
            <RowDefinition Height="auto" />
        </Grid.RowDefinitions>
        
        <TextBlock Text="Entity Framework Transaction Isolation Level Experiment"
                   Margin="10, 0, 0, 10"
                   FontSize="16" FontWeight="SemiBold" Foreground="Brown" />
        <Grid Grid.Row="1">
            <Grid.ColumnDefinitions>
                <ColumnDefinition Width="*" />
                <ColumnDefinition Width="*" />
                <ColumnDefinition Width="*" />
            </Grid.ColumnDefinitions>
 
            <Button Grid.Column="0" Content="Re-set Salary"
                    Margin="10,0,5,0"
                    Command="{Binding ResetSalaryCommand}" />
            <Button Grid.Column="1" Content="Raise Salary"
                    Margin="5,0,5,0" 
                    Command="{Binding RaisetSalaryNonSerializedCommand}" />
            <Button Grid.Column="2" Content="Raise Salary Serialized"
                    Command="{Binding RaisetSalarySerializedCommand}"
                    Margin="5,0,10,0"/>
        </Grid>
        
        <Border Grid.Row="2" BorderThickness="1" BorderBrush="Blue"
                CornerRadius="5" Margin="5">
            <DataGrid ItemsSource="{Binding EmployeeInformation}"
                  IsReadOnly="True" AutoGenerateColumns="False" Margin="10">
                <DataGrid.Columns>
                    <DataGridTextColumn Header="ID" Width="50"
                                    Binding="{Binding ID}" />
                    <DataGridTextColumn Header="Name" Width="300"
                                    Binding="{Binding Name}" />
                    <DataGridTextColumn Header="Salary" Binding="{Binding Salary}" />
                </DataGrid.Columns>
            </DataGrid>
        </Border>
 
        <TextBlock Grid.Row="3" Text="{Binding StatusInformation}"
                   Margin="10, 0, 0, 0" />
    </Grid>
</Window>

This simple XAML view has the following functional components:

  • A "DataGrid" bound to the "EmployeeInformation" property in the View Model to display the information for the employees.
  • A "TextBlock" bound to the "StatusInformation" in the View Model to display the information when the "CPO" and "CEO" making the raise for the employ.
  • Three "Button" controls. Each is bound to the corresponding command in the view model.

Run the Application

Now we finish this simple example, let us test run it. When the application launches, we can see the start salary of the employee is $100.

RunAppStart.JPG

Click the "Raise Salary" button, we can see that both "CPO" and "CEO" start to give raise to the employee. When they finish, the employee's final salary is $150.

RunAppNonSerialized.JPG

Click the "Re-set Salary" button to re-set the employee's salary to $100 and click the "Raise Salary Serialized" button, we will see that one of the officer will fail in the first try, but when he finishes, the final salary of the employee is $200, which is the correct raise that the employee deserves.

RunAppSerialized.JPG

Conclusion

When concluding this article, I will present a very well known method to achieve the same result. If we do not to use "ADO.NET Entity Framework", but to work on the relational database directly using "Stored Procedures". The stored procedure to guarantee the correct transaction isolation level is the following:

CREATE PROCEDURE dbo.RaiseEmployeeSalary
 @EmployID INT,
 @Amount INT
AS
BEGIN
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
 UPDATE Employee SET Salary = Salary + @Amount WHERE ID = @EmployID
END

It is as simple as it is. In most of the times, simplicity also means higher quality, less testing and "Unit testing", and easier maintenance. It also means that software engineers can be more focused on the tasks given to them, but not to worry about how the code is written. If you go back to take a look at the "RaiseEmployeeSalarySerialized" method that achieves the same result, you should find that the stored procedure is indeed simpler. The "RaiseEmployeeSalarySerialized" method used quite a few technologies and programming techniques, such as "TransactionScope", "Using Statement", "IDisposable", and "Linq", but the stored procedure has only one "T-SQL" statement. You should also know that to raise the salary, the client will need to have two trips to the database if you use Entity Framework for the read and the update. If you use the stored procedure, you need only one trip to the database. Performance wise, using Entity Framework is not as efficient as using the stored procedure. Another problem is that you will need to grant the access right to your application program at the table level when you take advantage of the Entity Framework. I know that a lot of database administrators may not like it.

You may be thinking that I do not like "ADO.NET Entity Framework". No, I like all technologies and all of them have advantages and disadvantages. It is up to us to choose among them for our applications. "ADO.NET Entity Framework" does come with the nice automatic code generation capability. If you are a fan of the multi-tiered architecture, we can take advantage of the automatically generated code to build our data access layer to save us some typing. But you should be aware that in certain cases, you may be forced to write a lot more code to use the automatically generated code compared with not to use it at all. I will have to say that Entity Framework does not exclude us from using stored procedures. But you will have to know exactly how the Entity Framework calls the stored procedures. Otherwise, you may not always get the result that you expect.

The "Object Oriented Programming" and the "Entity Relation Model" are the two wonderful mainstream theoretical and practical human achievements. Because they have some fundamental differences in the way how they view the world. Bridging them is not a simple technical matter. The human kind may need some fundamental theoretical improvements to find the intrinsic relations. I hope we can achieve this soon, if we can achieve it at all.

Points of Interest

  • This article presented an example on how to control the transaction isolation level in ADO.NET Entity Framework.
  • If you are new to the IT field and new to the database, you may be scared that your user fails to access your database because you set a higher level of the transaction isolation. In fact, the cost of letting your users fail is much less than if you let them succeed, but create wrong results. Your users will be happy about your letting them fail and try it again if you can explain to them why you insisted upon the transaction isolation.
  • In most of database updates, if you find that your transaction isolation level does block some users, the correct decision is not to relax the isolation level. In fact, you may need to think about raising the isolation level, because you have a good indication that your users are accessing some shared data and you need to find the right transaction isolation to protect it. To solve the problem, you should be thinking of re-designing your business flow, so the users can work on the data without other users to interrupt them. In any case, fixing a messed up database with wrong data is a very difficult task.
  • The relational databases have been mature enough to address most of the business problems for transactional applications. Over time, we have accumulated a lot of effective technologies/methods to address the problems. If the "ORP" tools prevent you from using them and create unnecessary difficulties for you. Considering not to use a "ORP" tool is not a bad choice. If "ORM" tools do provide some advantages, go ahead to use them. You should be able to find ways to address the problems ahead of you.
  • I have a fairly short time to write this article, I realize that there may be some problems in this article. The code may not be following the good coding standards, the exception handling may not be able to trap all the exceptions, etc. I hope you can ignore these problems. Anyway, controlling the transaction isolation level in Entity Framework is possible and this is a running example.
  • You may be asking what the default transaction isolation level is in Entity Framework. After some research, I find that the Entity Framework does not provide the transaction isolation control by itself. It relies on the data source to control it. When we use SQL server, if we do not specify it, the transaction isolation level is "Read Committed".
  • In most of the applications, it is not likely that you will hit the concurrency issue presented here. In fact, we have to properly time the operations of the officers in the two threads to create the issue. But we can never rule out the possibility that the two officers will make the database operations at the same time. It is always a good idea to always properly protect your data using transaction isolation.
  • I hope you like my postings and I hope this article can help you one way or the other.

History

  • First revision - 15 May 2011

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Dr. Song Li

United States United States
I have been working in the IT industry for some time. It is still exciting and I am still learning. I am a happy and honest person, and I want to be your friend.

Comments and Discussions

 
QuestionGreat project Vietnam tour | vietnam travel Pinprofessionalhoangtinh1214-Jul-13 6:14 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 15 May 2011
Article Copyright 2011 by Dr. Song Li
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid