Why does an insert using EF code first fail within a TransactionScope?

I have a situation where I create a record in one table (asset_type) and reference it via a foreign key in a second table (asset). Both of these inserts, in this situation, occur within the same TransactionScope.

When using a raw DbConnection, the inserts are successful:

conn.ConnectionString = "host=lo开发者_StackOverflowcalhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw";    

using (var trans = new TransactionScope())
{
  conn.Open();
  conn.EnlistTransaction(Transaction.Current);

  var cmd = conn.CreateCommand();
  cmd.CommandText = "INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id";
  var assetTypeId = (int)cmd.ExecuteScalar();

  cmd.CommandText = string.Format("INSERT INTO overview.asset "
                                  + "(asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) "
                                  + "VALUES ({0}, 'mid', TRUE, TRUE, FALSE, 0, 0 ) "
                                  + "RETURNING id ", assetTypeId);
  var assetId = (int)cmd.ExecuteScalar();

  trans.Complete();
}

However, if I switch to using a DbContext class, the second insert (into asset) fails with a foreign key constraint violation as though the first insert (into asset_type) did not happen:

conn.ConnectionString = "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw";

using (var trans = new TransactionScope())
{
  using (var context = new TestContext(conn, false))
  {
    var assetTypeId = context.Database
      .SqlQuery<int>("INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id")
      .Single();

    var assetId = context.Database
      .SqlQuery<int>(string.Format("INSERT INTO overview.asset "
                                    + "(asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) "
                                    + "VALUES ({0}, 'mid', TRUE, TRUE, FALSE, 0, 0 ) "
                                    + "RETURNING id ", assetTypeId))
      .Single();
    trans.Complete();
  }
}

If I remove the TransactionScope, the DbContext example executes normally.

I have tried playing with the IsolationLevel settings (ReadCommitted, ReadUncommitted) with no success.

I realize that I don't need a TransactionScope in this example. This is part of a larger chunk of code that involves interaction with multiple databases and requires a distributed transaction.

My database is PostgreSQL and I am using DevArt's dotConnect .NET drivers.

Does anyone have any insight into why the DbContext example does not work?

Managing Connections and Transactions:

The Entity Framework opens connections only when required, for example to execute a query or to call SaveChanges, and then closes the connection when the operation is complete.

• Calling any of the following methods opens the connection:
• SaveChanges or Refresh on ObjectContext.
• FirstOrDefault, or First on ObjectQuery.
• Load on EntityCollection.
• Load on EntityReference.
• Any Language-Integrated Query (LINQ) method or ObjectQuery query builder method, such as Where, OrderBy, or Select.

And then it opens another one connection, transaction scope throw exception. You must setup the Distributed Transaction Coordinator (can't say is it real or not for the PostgreSQL).

If it is real, then, after setup the DTC simply open your conn object inside the scope.