Allow 'unsharing' connection between contexts

[stevendarby]

If a context is created from a pooled factory and borrows a connection & transaction from another context instance, using SetDbConnection and UseTransaction, then I need to ensure that the connection/transaction is 'unborrowed' before disposing the context, otherwise it may be rented from the pool again with the connection still set.

Doing something like context.Database.SetDbConnection(null) throws if the existing connection is still open as because it interprets this as an attempt to dispose the existing connection. However, because the current connection isn't owned by that context, I think it should allow removing the connection without disposing it- or at least provide some other mechanism to do this. (Is there such a mechanism already?)

[stevendarby]

It's a bit contrived but below is a demo of what I mean.

You should get an exception running this - it can be one of many, including:
Invalid operation. The connection is closed.
The connection was not closed. The connection's current state is connecting.

You can 'fix' it by removing the await using for the secondaryContext - but of course this means it's never be returned to the pool. So-- looking for a way to return it to the pool safely.

Suppose PerformExtraWorkAsync is a fixed contract - within it, the secondary context won't know when the main one has finished and so can't close the connection itself.

using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.Extensions.DependencyInjection;
using System.Linq;
using System.Threading.Tasks;


var services = new ServiceCollection()
    .AddPooledDbContextFactory<DemoContext>(o => o.UseSqlServer())
    .BuildServiceProvider();

const string connectionString = "Data Source=(LocalDb)\\MSSQLLocalDB;Initial Catalog=PoolDemo;Integrated Security=SSPI;";

var factory = services.GetRequiredService<IDbContextFactory<DemoContext>>();

{
    await using var context = factory.CreateDbContext().WithConnectionString(connectionString);
    context.Database.EnsureDeleted();
    context.Database.EnsureCreated();
}

await Parallel.ForEachAsync(Enumerable.Range(0, 100),
    async (i, _) =>
    {
        await using var mainContext = factory.CreateDbContext().WithConnectionString(connectionString);

        await using var transaction = await mainContext.Database.BeginTransactionAsync();

        mainContext.Add(new Person { Name = $"Ronald {i}" });
        await mainContext.SaveChangesAsync();

        await PerformExtraWorkAsync(factory, mainContext, i);

        await transaction.CommitAsync();
    });

{
    await using var context = factory.CreateDbContext().WithConnectionString(connectionString);
    var people = await context.People.ToListAsync();
}

async Task PerformExtraWorkAsync(IDbContextFactory<DemoContext> dbContextFactory, DemoContext mainContext, int index)
{
    // Second context start
    await using var secondaryContext = dbContextFactory.CreateDbContext();
    secondaryContext.Database.SetDbConnection(mainContext.Database.GetDbConnection());
    await secondaryContext.Database.UseTransactionAsync(mainContext.Database.CurrentTransaction!.GetDbTransaction());

    secondaryContext.Add(new Person { Name = $"Ronald {index}'s mate" });
    await secondaryContext.SaveChangesAsync();
    // Second context end
}


public class DemoContext : DbContext
{
    public DbSet<Person> People { get; set; }

    public DemoContext(DbContextOptions<DemoContext> options) : base(options)
    {
    }

    public DemoContext WithConnectionString(string connectionString)
    {
        Database.SetConnectionString(connectionString);
        return this;
    }
}

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
}