Improved Transactions in Entity Framework 6

Entity Framework 6.0 (Beta 1 as of this writing) contains new APIs that can enable you to manage your own transactions. Earlier we were using System.Transactions.dll to handle transactions in Entity Framework using TransactionScope. Entity Framework uses this transaction to save changes to the database. When the operation succeeds, the changes in the object context are accepted. When an Exception occurs while updating, the operation is performed up to two times.

You can see the syntax here

using (TransactionScope scope=new TransactionScope())
{
    //Code Here
}

Now in Entity Framework 6, we have two new APIs as described below -

1. UseTransaction() - This allows us to participate in an existing transaction. This means if the transaction has already been started somewhere and you want to execute all the commands of Entity framework under this transaction, then you can make use of UseTransaction().

2. BeginTransaction() - This will allow us to begin a transaction.

So let's start with Visual Studio 2013 RC1 and create a Console Application with the name EF6Transactions as shown below -

Now install Entity Framework 6.0 RC1 in our project using the NuGet package. Right click the project and click on the Manage NuGet packages context menu. Choose Include Prerelease and install Entity Framework as shown below -

Once the application is ready, add the following classes in your application. Here we are using Code First technique of Entity Framework to create a database and tables.

public class Customer
{
    [Key]
    public int CustomerID { get; set; }
    public string ContactName { get; set; }
    public string Address { get; set; }
    public string City { get; set; }
    public string State { get; set; }
    public string Country { get; set; }
    public List<Order> Orders { get; set; }
}

public class Order
{
    [Key]
    public int OrderID { get; set; }
    public DateTime OrderDate { get; set; }
    public DateTime RequiredDate { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public int CustomerID { get; set; }
}

Once you add the classes, now let’s add a Context class as shown below –

public class PurchaseOrderContext:DbContext
{
    public PurchaseOrderContext()
        : base("PurchaseOrderConnectionString")
    { }
   
    public PurchaseOrderContext(DbConnection CN,bool ownContext):base(CN,ownContext)
    {

    }
   
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }
}

After this, open App.config file and change the connection string as per your requirement. I have SQL Server 2012 installed on my machine and my connection string looks like below -

<connectionStrings>
    <add connectionString="Data Source=ADMIN-PC\MSSQL2012;Initial Catalog=PurchaseOrderDB; Integrated Security=true;" name="PurchaseOrderConnectionString" providerName="System.Data.SqlClient"/>
  </connectionStrings>

Important Note - Make sure you place this connection string after <ConfigSections/>. Otherwise you will get an error.

Let's add the following code in our Program.cs file and Main function -

class Program
{
    static void Main(string[] args)
    {
        TestingUseTransaction();
        Console.Read();
    }
   
    private static void TestingUseTransaction()
    {
        PurchaseOrderContext dataContext = new PurchaseOrderContext();
        dataContext.Customers.Add(new Customer()
        {
            ContactName = "John R.",
            Address = "E-Street",
            City = "London",
            State = null,
            Country = "UK",
        });
        dataContext.SaveChanges();
        var query = from cust in dataContext.Customers
                    select cust;
        foreach (var cust in query)
        {
            Console.WriteLine(cust.CustomerID + "----" + cust.ContactName);
        }
    }
}

Run your console application. It will create a database with the name "PurchaseOrderDB" with two tables "Customers" and "Orders" as shown below -

The output of console application will look like below -

Now let's make use of UserTransaction() method. Write the following code in our TestingUseTransaction() method. [First add a reference of System.Configuration.dll into our project to access the connection string programmatically.]

private static void TestingUseTransaction()
{
    using (SqlConnection CN = new SqlConnection(ConfigurationManager.ConnectionStrings["PurchaseOrderConnectionString"].ConnectionString))
    {
        CN.Open();
        using (SqlTransaction trans=CN.BeginTransaction())
        {
            try
            {
                //You can do some Command Object Coding!!
                using (PurchaseOrderContext dataContext = new PurchaseOrderContext(CN, false))
                {
                    dataContext.Database.UseTransaction(trans);
                    dataContext.Customers.Add(new Customer()
                                {
                                    ContactName = "John R.",
                                    Address = "E-Street",
                                    City = "London",
                                    State = null,
                                    Country = "UK",
                                });
                    dataContext.SaveChanges();
                }
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                Console.WriteLine(ex.InnerException);
            }
        }
    }
    PurchaseOrderContext dataContextFetch = new PurchaseOrderContext();
    var query = from cust in dataContextFetch.Customers
                select cust;
    foreach (var cust in query)
    {
        Console.WriteLine(cust.CustomerID + "----" + cust.ContactName);
    }
}

In the above method, we are inserting a record into Customers table. We have started and opened the connection explicitly and we have forced the Data Context of Entity Framework to use this connection by calling the constructor of our PurchaseOrderConetxt class.

We have started the transaction explicitly and then are asking our data context to participate in the existing transaction with the following line of code -

dataContext.Database.UseTransaction(trans);

If everything goes well, the transaction will be committed, else it will be rollback (see the code above)

Now let's make use of BeginTransaction of Entity framework. Add another method with the name TestingBeginTransaction() and write the following code in the method -

private static void TestingBeginTransaction()
{
    using (PurchaseOrderContext dataContext = new PurchaseOrderContext())
    {
        using (var trans = dataContext.Database.BeginTransaction())
        {
            try
            {
                dataContext.Customers.Add(new Customer()
                {
                    ContactName = "John R.",
                    Address = "E-Street",
                    City = "London",
                    State = null,
                    Country = "UK",
                });
                dataContext.SaveChanges();
                trans.Commit();
            }
            catch (Exception ex)
            {
                trans.Rollback();
                Console.WriteLine(ex.InnerException);
            }
        }
    }
   
    PurchaseOrderContext dataContextFetch = new PurchaseOrderContext();
    var query = from cust in dataContextFetch.Customers
                select cust;
    foreach (var cust in query)
    {
        Console.WriteLine(cust.CustomerID + "----" + cust.ContactName);
    }
}

In the above method, the context has its own connection and it has started the transaction. Now run your project and see the output.

Conclusion

In this short article, we have seen the improvements in Entity Framework 6.0 Transactions. We have made use of UseTransaction() method which allows the context to participate into an already running transaction. We also used BeginTransaction() to start a new transaction.

This article has been editorially reviewed by Suprotim Agarwal.

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

Author

Pravinkumar, works as a freelance trainer and consultant on Microsoft Technologies. He is having over 10 years of experience in IT and is also a Microsoft Certified Trainer(MCT). He has conducted various corporate trainings on all versions of .NET Technologies including .NET, SharePoint Server, Microsoft SQL Server, Silverlight, ASP.NET, Microsoft PerformancePoint Server 2007 (Monitoring). He is passionate about learning new technologies from Microsoft. You can contact Pravinkumar at dabade[dot]pravinkumar [attherate] gmail[dot]com