The connection is already in a transaction - Error on CreateTables EF Core

asp.net-core c# entity-framework entity-framework-core

Question

I want to insert a record in an existing table, using a context, and create new tables in the same transaction using a different context. The two contexts share the same connection, however when I want to execute the CreateTables function I get the following error:

The connection is already in a transaction and cannot participate in another transaction.

What is the cause of this, are there multiple transactions hidden somewhere? How can I execute these two changes in the same transaction?

The transaction:

using (var newTablesContext = new NewTablesContext(options, schema))
{
  using (var transaction = existingContext.Database.BeginTransaction())
  {
    try
    {
      newTablesContext.Database.UseTransaction(transaction.GetDbTransaction());
      RelationalDatabaseCreator databaseCreator =
        (RelationalDatabaseCreator)newTablesContext.Database.GetService<IDatabaseCreator>();
      databaseCreator.CreateTables();

      existingContext.Entities.Add(entity);
      if (existingContext.SaveChanges() < 1)
      {
        throw new Exception();
      }

      transaction.Commit();
    }
    catch (Exception e)
    {
      transaction.Rollback();
      return null;
    }
  }
}

The Startup.cs :

private Action<IServiceProvider, DbContextOptionsBuilder> SqlServerConfigurator(IServiceCollection services)
{
  var connectionString = Configuration.GetConnectionString("dbConnection");
  services.AddScoped(s => new SqlConnection(connectionString));

  return (locator, builder) =>
  {
    var connection = locator.GetRequiredService<SqlConnection>();

    builder.UseSqlServer(connection)
      .ReplaceService<IModelCacheKeyFactory, DbSchemaAwareModelCacheKeyFactory>();
  };
}

Inside the ConfigureServices :

var configSqlServer = SqlServerConfigurator(services);

services.AddDbContext<ExistingContext>(configSqlServer);
1
0
10/24/2019 10:20:53 AM

Popular Answer

You dont need to use this line

newTablesContext.Database.UseTransaction(transaction.GetDbTransaction());

Below is my code using async transaction. You can view as reference. Please let me know if you need any help.

using (var transaction = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled))
{
    try
    {
        var commentToUpdate = await _unitOfWork.Repository<Comment>().Query().Where(cm => cm.Id == commentId).SingleOrDefaultAsync();
        commentToUpdate.CommentStatus = commentStatus;
        await _unitOfWork.Repository<Comment>().UpdateAsync(commentToUpdate);
        transaction.Complete();
        return true;
    }
    catch (Exception)
    {
        _unitOfWork.Rollback();
        return false;
    }
}
0
6/17/2019 1:38:00 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow