none
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
    Monday, August 18, 2008 7:34 PM

Answers

  • 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

    Monday, August 18, 2008 7:39 PM
    Moderator

All replies

  • 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

    Monday, August 18, 2008 7:39 PM
    Moderator
  • 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

     

     

    Monday, August 18, 2008 8:39 PM