I’m working on a .NET application where I need to manage transactions across multiple DbContext instances. My application uses a custom NuGet package that interacts with the database using Entity Framework Core. The NuGet package has its own DbContext and creates its own transaction scope. However, I need this to participate in a broader transaction scope initiated in the calling application to ensure all operations are atomic. Both DBContext using the same MSSQL Database.

Problem Details:
Context: My application creates a transaction scope using TransactionScope and performs operations across multiple DbContext instances.
NuGet Package Behavior: The NuGet package creates a new transaction scope using its own DbContext, which can lead to nested or distributed transactions, causing unexpected behavior.

My application:
`
public async Task ProcessDataAsync(ProcessDataDto processDataDto, Guid? entityId = null) {
using var transactionScope = transactionScopeFactory.CreateTransactionScope();

var newEntityId = await dataProvider.SaveEntityAsync(...);

await customNuGetPackage.FinalizeEntityAsync(newEntityId); // This triggers the NuGet package code

transactionScope.Complete(); }`

NuGet:

public async Task ExecAsync<T>(Func<T, Task> asyncAction, int timeoutInSec = ElproEventDbContext.DefaultTimeoutInSec) where T : DbContext { using var context = Create(timeoutInSec); var strategy = context.Database.CreateExecutionStrategy(); try { await strategy.ExecuteAsync(async () => await asyncAction.Invoke(context as T)); } catch (Exception ex) { logger.LogCritical(ex, "Exception while executing SQL statement"); throw; } }
`
protected virtual DbContext Create(int timeoutInSec)
{
var context = new ElproEventDbContext(logger, connectionStringProvider);
context.Database.SetCommandTimeout(timeoutInSec);

return context;
}`

What I tried:
I tried modifying the ExecAsync method in my custom NuGet package to manage transactions more effectively. Specifically, I checked if there was an existing transaction using context.Database.CurrentTransaction. If a transaction was already active, I expected the method to reuse it. If no transaction was active, I created a new one with context.Database.BeginTransaction().

`
public async Task ExecAsync(Func<T, Task> asyncAction, int timeoutInSec = 300) where T : DbContext
{
var context = Create(timeoutInSec);
var strategy = context.Database.CreateExecutionStrategy();
try
{
await strategy.ExecuteAsync(async () =>
{
using (var transaction =
context.Database.CurrentTransaction ?? context.Database.BeginTransaction())
{
await asyncAction.Invoke(context as T);

            if (context.Database.CurrentTransaction != null)
            {
                await context.Database.CurrentTransaction.CommitAsync();
            }
        }
    });
}
catch (Exception ex)
{
    logger.LogCritical(ex, "Exception while executing SQL statement");
    throw;
}
finally
{
    context?.Dispose();
}
}

`