为什么使用EF代码的插入首先会在TransactionScope中失败?

本文关键字：TransactionScope 失败 EF 代码插入为什么 | 更新日期: 2023-09-27 18:02:18

我有一种情况，我在一个表(asset_type)中创建了一条记录，并通过第二个表(资产)中的外键引用它。在这种情况下，这两个插入都发生在同一个TransactionScope中。

当使用原始DbConnection时，插入成功:

conn.ConnectionString = "host=localhost;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();
}

然而，如果我切换到使用DbContext类，第二次插入(到资产)失败，违反了外键约束，就像第一次插入(到asset_type)没有发生一样:

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

如果我删除TransactionScope, DbContext示例将正常执行。