TransactionScope and ADO.NET Entity Framework

Question

Hi All,

I would like to use TransactionScope with ADO.NET Entity Framework. Here is my sample code:

                using (TransactionScope ts = new TransactionScope())
                {
                    using (JobModel jobModel = new JobJobModel())
                    {
                        var jobs = jobModel .CreateQuery<JOB>("select value j from JOB as j WHERE j.Name like '%a%'");
                        foreach (JOB j in jobs)
                        {
                            System.Console.WriteLine(j.ID);
                        }
                    }

                    //using (JobModel jobModel = new JobModel())
                    //{
                    //    var jobs = jobModel .CreateQuery<JOB>("select value j from JOB as j WHERE j.Name like '%b%'");
                    //    foreach (JOB j in jobs)
                    //    {
                    //        System.Console.WriteLine(j.ID);
                    //    }
                    //}

                    //using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                    //{
                    //    sqlConnection.Open();

                    //    SqlCommand sqlCommand = new SqlCommand("SELECT * FROM JOB", sqlConnection);
                    //    sqlCommand.ExecuteNonQuery();
                    //}

                    ts.Complete();
                }

When I uncomment second JobModel I get an exception 'Communication with the underlying transaction manager has failed.' (Distributed Transaction).
Same story when I want to use old ADO and ADO.NET Entity (sqlConnection.Open() part).

Is there any way to avoid distributed transaction and get my code working? Or maybe I'm missing something?
Maybe there is a way to tell old ADO, ADO.NET Entity that I'm responsible for opening and closing connection and they should not do this for me.

Regards,
Grzela

Answer 1

You can avoid using a distributed transaction by managing your own EntityConnection and passing this EntityConnection to your ObjectContext:

 

Code Snippet

EntityConnection conn = new EntityConnection(ConnectionString);

using (TransactionScope ts = new TransactionScope())
                {
                    using (JobModel jobModel = new JobJobModel(conn))
                    {
                        var jobs = jobModel .CreateQuery<JOB>("select value j from JOB as j WHERE j.Name like '%a%'");
                        foreach (JOB j in jobs)
                        {
                            System.Console.WriteLine(j.ID);
                        }
                    }

                    // Re-use the connection here
                    using (JobModel jobModel = new JobModel(conn))
                    {
                        var jobs = jobModel .CreateQuery<JOB>("select value j from JOB as j WHERE j.Name like '%b%'");
                        foreach (JOB j in jobs)
                        {
                            System.Console.WriteLine(j.ID);
                        }
                    }

                    //using (SqlConnection sqlConnection = new SqlConnection(connectionString))
                    //{
                    //    sqlConnection.Open();

                    //    SqlCommand sqlCommand = new SqlCommand("SELECT * FROM JOB", sqlConnection);
                    //    sqlCommand.ExecuteNonQuery();
                    //}

                    ts.Complete();
                }

 

 

Jeff

Answer 2

The problem of Transaction promoted to DTC has been extensibly tracked in the following threads.

 

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=580828&SiteID=1&mode=1

http://forums.microsoft.com/msdn/showpost.aspx?postid=113669&siteid=1&sb=0&d=1&at=7&ft=11&tf=0&pageid=1

 

In short: This has been fixed in SQL Srvr 2008. Your code will not change to pass around connection all over the place.

Yes, this was a big server side change.

 

Thanks

 

Otpi