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