none
Entity Framework and TransactionScope - DTC promotion

    Question


  • Hi !

    I'm trying to do a simple transaction using TransactionScope.
    Inside I have two linq queries to get entities, using First<>(), which being used to create new entity.
    The problem occurs on the second query - I get an exception for DTC promotion.
    I thought it should use the same connection for both queries (no reason why not), but it seems that it tries to open another connection - then I get the DTC exception.

    Any ideas? please?

    Thanks,
    Shlomi





    Thursday, March 20, 2008 1:52 PM

Answers

  • By default the EF will open connections when needed and then close them again.  It actually uses the same connection, but the open and close and then re-open causes SQL Server versions prior to 2008 to promote the transaction to the DTC.  So, there's a simple fix for this:  Just open the connection yourself before the first operation.  If you do that, then the EF won't close it until either the context is disposed or you close it manually.

     

    Pattern:

     

    using (var context = new MyContext())

    {

      using (var txscope = new TransactionScope())

      {

        context.Connection.Open();

        // do query 1

        // do query 2

      }

    }

     

    Thursday, March 20, 2008 2:16 PM

All replies

  • By default the EF will open connections when needed and then close them again.  It actually uses the same connection, but the open and close and then re-open causes SQL Server versions prior to 2008 to promote the transaction to the DTC.  So, there's a simple fix for this:  Just open the connection yourself before the first operation.  If you do that, then the EF won't close it until either the context is disposed or you close it manually.

     

    Pattern:

     

    using (var context = new MyContext())

    {

      using (var txscope = new TransactionScope())

      {

        context.Connection.Open();

        // do query 1

        // do query 2

      }

    }

     

    Thursday, March 20, 2008 2:16 PM

  • Thanks Daniel - Now it works!

    Really appreciate your fast and good reply!

    Best regards,
    Shlomi

    Thursday, March 20, 2008 2:51 PM
  • It does not work for me.

    I modified my code as mentioned above and I am receiving same error. Error shows once is while.

    Thanks


    Thanks
    Tuesday, December 28, 2010 8:47 PM
  • On 12/28/2010 3:47 PM, Venkat Prasad wrote:
    > It does not work for me.
    >
    > I modified my code as mentioned above and I am receiving same error.
    > Error shows once is while.
    >
     
    Well Distributed Transaction Coordinator must be activated. And if it's
    running in a client/server environment, DTC must be running on the
    client and database server machines.
     
    Tuesday, December 28, 2010 10:40 PM