I am using dependency injection with Entity Framework Core and the context is created through a scope factory:
using (var scope = this._scopeFactory.CreateScope())
var context = scope.ServiceProvider.GetRequiredService<thecontext>();
//run code here
A transaction is started after all the objects are created:
using (var trans = context.Database.BeginTransaction())
This works well but during the process I need to insert records by running a stored procedure that is outside the transaction. In other words even if the transaction is aborted I still need the results of this stored procedure insert to persist. In addition any inserts from the 'isolated' stored procedure must be available to the process running under the transaction as well as outside the current scope.
The normal method of executing the stored procedure is by getting the connection and attaching the transaction to a new command. But I need to either use a new connection that is outside the current scope so it is not bound by the transaction or perhaps there is another way?
string sql = $"theProc";
var cnn = _context.Database.GetDbConnection();
if (cnn.State == ConnectionState.Closed)
DbCommand cmd = cnn.CreateCommand();
cmd.CommandText = sql;
cmd.CommandType = CommandType.StoredProcedure;
if (_context.Database.CurrentTransaction != null)
cmd.Transaction = _context.Database.CurrentTransaction.GetDbTransaction();
I ended up doing something like:
var cnnstr = _context.Database.GetConnectionString();
var isolatedCnn = new SqlConnection(cnnstr);
This works but is ugly for a number of reasons so I'd like to find a better solution.