Entity Framework Code First and TransactionScope exception

ChrisMH · Post by **ChrisMH** » Fri 15 Jul 2011 21:02

I am using dotConnect 5.30.185 for PostgreSQL and Entity Framework 4.1 Code First.

I have a situation where I need to use distributed transactions (TransactionScope) but am having some problems which I have isolated in the samples below.

I have two tables: asset and asset_type. asset has a foreign key, asset_type_id, which references asset.

There is a situation where, within a single distributed transaction, I will insert a record into asset_type followed by an insert into asset referencing the asset_type record just inserted.

If I do these operations using a raw connection, everything works fine. If I do them using a DbContext, I get a referential integrity exception during the second insert. If I remove the TransactionScope the DbContext example works fine.

I have included dbMonitor logs and one thing that jumps out at me in the DbContext version is that it appears the connection is opened twice and a transaction is started twice. I'm just guessing, but perhaps the second insert is occurring in a different transaction so doesn't have access to the first insert.

I don't know if this is your issue or a problem with Entity Framework or a problem with the way I'm using it. Any insight would be appreciated.

This works as expected:

Code: Select all

var connectionString = "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw";

using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = IsolationLevel.ReadCommitted }))
{
  using (var conn = new PgSqlConnection(connectionString))
  {
    conn.Open();

    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();
  }
}

This fails with a referential integrity exception:

Code: Select all

using (var trans = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions {IsolationLevel = IsolationLevel.ReadCommitted}))
{
  using (var context = new TestContext(new PgSqlConnection(connectionString), true))
  {
    var assetTypeId = context.Database
      .SqlQuery("INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id")
      .Single();

    var assetId = context.Database
      .SqlQuery(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();
}

TestContext doesn't do anything except remove any initialization and expose the constructor I want to use:

Code: Select all

  
internal class TestContext : DbContext
{
  static TestContext()
  {
    Database.SetInitializer(null);
  }

  public TestContext(DbConnection existingConnection, bool contextOwnsConnection)
    : base(existingConnection, contextOwnsConnection)
  {
  }
}

Log for DbConnection example:

Code: Select all

  7/15/2011 4:47:11 PM n/a dotConnect for PostgreSQL monitoring is started Complete
  7/15/2011 4:47:11 PM 0.000 Creating pool manager Complete
  7/15/2011 4:47:11 PM 0.016 Creating pool with connections string: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
  7/15/2011 4:47:11 PM 0.000 Creating object Complete
  7/15/2011 4:47:11 PM 0.156 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw;" Complete
  7/15/2011 4:47:11 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
  7/15/2011 4:47:11 PM 0.016 Execute: SELECT version() Complete
  7/15/2011 4:47:11 PM 0.000 Execute: show integer_datetimes Complete
  7/15/2011 4:47:11 PM 0.000 Execute: SET autocommit=true Complete
  7/15/2011 4:47:11 PM 0.000 Execute: SET datestyle=ISO Complete
  7/15/2011 4:47:11 PM 0.015 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  7/15/2011 4:47:11 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  7/15/2011 4:47:11 PM 0.000 Execute: BEGIN Complete
  7/15/2011 4:47:11 PM 0.000 Prepare: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
  7/15/2011 4:47:11 PM 0.016 Execute: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
  7/15/2011 4:47:11 PM 0.000 Prepare: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id  Complete
  7/15/2011 4:47:11 PM 0.031 Execute: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id  Complete
  7/15/2011 4:47:11 PM 0.000 Execute: PREPARE TRANSACTION 'a44d0fbb-4921-4065-b200-c640755a88c3' Complete
  7/15/2011 4:47:11 PM 0.015 Execute: COMMIT PREPARED 'a44d0fbb-4921-4065-b200-c640755a88c3' Complete
  7/15/2011 4:47:11 PM 0.000 Execute: COMMIT Complete
  7/15/2011 4:47:11 PM 0.141 Connection is returned to pool. Pool has 1 connection(s). Complete

Log for DbContext example:

Code: Select all

  7/15/2011 4:48:37 PM n/a dotConnect for PostgreSQL monitoring is started Complete
  7/15/2011 4:48:37 PM 0.015 Creating pool manager Complete
  7/15/2011 4:48:37 PM 0.000 Creating pool with connections string: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
  7/15/2011 4:48:37 PM 0.016 Creating object Complete
  7/15/2011 4:48:38 PM 0.172 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw;" Complete
  7/15/2011 4:48:38 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
  7/15/2011 4:48:38 PM 0.016 Execute: SELECT version() Complete
  7/15/2011 4:48:38 PM 0.000 Execute: show integer_datetimes Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET autocommit=true Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET datestyle=ISO Complete
  7/15/2011 4:48:38 PM 0.016 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  7/15/2011 4:48:38 PM 0.016 Execute: BEGIN Complete
  7/15/2011 4:48:38 PM 0.016 Prepare: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
  7/15/2011 4:48:38 PM 0.000 Execute: INSERT INTO overview.asset_type ( name ) VALUES( 'Unknown' ) RETURNING id Complete
  7/15/2011 4:48:38 PM 0.062 Open connection: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;" Complete
  7/15/2011 4:48:38 PM 0.000 Connect: "host=localhost;port=5432;database=test_client_alpha;user id=tcauser;password=tcapw" Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SELECT version() Complete
  7/15/2011 4:48:38 PM 0.000 Execute: show integer_datetimes Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET autocommit=true Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET datestyle=ISO Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SELECT oid FROM pg_type WHERE typname like 'geometry' Complete
  7/15/2011 4:48:38 PM 0.000 Execute: SET TRANSACTION ISOLATION LEVEL READ COMMITTED Complete
  7/15/2011 4:48:38 PM 0.000 Execute: BEGIN Complete
  7/15/2011 4:48:38 PM 0.000 Prepare: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id  Complete
  7/15/2011 4:48:38 PM 0.063 Execute: INSERT INTO overview.asset (asset_type_id, client_id, is_active, is_gps_active, is_virtual, default_lon, default_lat) VALUES (1, 'mid', TRUE, TRUE, FALSE, 0, 0 ) RETURNING id  Error

ChrisMH · Post by **ChrisMH** » Sat 16 Jul 2011 19:52

I'm pretty certain that the answer to this question is that that's the way Entity Framework works (opens a new connection for any statement that actually touches the database), I'm just looking for confirmation.

Thanks

AndreyR · Post by **AndreyR** » Mon 18 Jul 2011 11:55

You are correct in your assumption. However, there is a simple solution.
If you open the context connection explicitly, EF uses this connection instance for all DB operations during the entire context lifetime. So, add the following code after the context construction code:

Code: Select all

(context as IObjectContextAdapter).ObjectContext.Connection.Open();