How to use transaction in Entity Framework

In previous posts, how to insert new records using entity framework and how to update records using entity framework. Those two processes are foundation blocks of most of the data operations we all perform in an application. The examples that showed in those posts were very simple involving one record for one table. In more complex work flow processes you have more than one database operations that need to take place to accomplish the intended outcome. When you have multiple records and tables being manipulated, you have to ensure ACID nature of transactions. There are very few cases I have come across where one could afford to have change in one table succeed if another operation in the same chain fail.

In this post I will show how to use transactions in Entity Framework. If you have dealt with transactions in SQL server queries or for that matter any database operations, you will notice that when using Entity Framework the concept stays the same. You perform the following steps.

• Create a transaction
• Perform database operations with in scope of that transaction
• Commit or rollback transaction depending on out come of database operation

When you are using Entity Framework, transactions are handled by Transaction framework. You will not find reference to those namespaces in EntityFramework assembly. Those references exist in System.Transactions assembly. Lets see how you will use transactions in entity framework.

Assembly reference and namespace

Add references to System.Transactions.dll assembly in your project. And now include System.Transactions namespace in your code file.

Create new transaction

Key to using transaction is creating a transaction or logically speaking create a transaction scope which will enclose all the data operations that need to be included in one single transaction. Use TransactionScope class to create new instance of implicit transaction scope for operations.

using System.Transactions;

var txScope = new TransactionScope();
using (txScope){ ......  }

Perform database operations

With in scope of transaction perform all the data operations that I talked about in previous posts.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
}                        

Commit Transaction

After all the database operations have completed and succeeded, then you can commit transaction by calling Complete method on TransactionScope object.

try
{
 dataContext.Projects.Add(project);
 dataContext.SaveChanges();
 txScope.Complete();
}

If you are looking for method like Rollback, you will not find it. No, this does not mean that transaction can not be rolled back using Entity Framework. The framework is designed to commit a transaction only after Complete method is called. If you do not want to commit a transaction, you can explicitly call Dispose on TransactionScope object or wait till TransactionScope object goes out of scope.

Here is complete code snippet from one of my sample projects.

protected void OnAdd(object sender, EventArgs e)
{
  if (!ValidateContracts())
  {
    ErrorMessage_Label.Visible = true;
    return;
  }

  var project = new Project()
  {
   ProjectName = AntiXss.HtmlEncode(ProjectName_TextBox.Text.Trim()),
    ProjectDescription = AntiXss.HtmlEncode(ProjectDescription_TextBox.Text.Trim()),
    Active = true,
    Deleted = false,
    ClientId = _thisClient.ClientId,
    CreateDate = DateTime.UtcNow
  };

  var dataContext = new PMDataContext(Global.ConnectionString);
  using (dataContext)
  {
   var txScope = new TransactionScope();
   using (txScope)
   {
    try
    {
     dataContext.Projects.Add(project);
     dataContext.SaveChanges();
     txScope.Complete();
    }
    catch (UpdateException upEx)
    {
     //TODO: log this update exception
     System.Diagnostics.Debug.WriteLine(upEx.Message);
    }
    catch (Exception ex)
    {
     //TODO: log thjis general exception
     System.Diagnostics.Debug.WriteLine(ex.Message);
    }
   }
  }
}