Efcore: Question - How to create DbContext with existing DbConnection in a provider-agnostic way

[BalassaMarton]

I have a set of worker classes that must share the same DbTransaction. Following the instructions on the documentation page
https://docs.microsoft.com/en-us/ef/core/saving/transactions#cross-context-transaction-relational-databases-only
I was able to share the transaction - however this ties my classes to a particular provider (SqlServer) that is not suitable for my unit tests. What I need to accomplish:

• Create DbContexts from externally provided DbContextOptions
• Start and share a single DbTransaction between these contexts
• All without using any provider-specific API.

Is this possible in any way?

[ajcvickers]

@BalassaMarton Can you give some more details on how this approach is tied to SqlServer?

[BalassaMarton]

Consider something like this:

    public interface IWorker
    {
        void DoWork(Func<MyDbContext> dbFactory);
    }

    public class WorkerRunner
    {
        private readonly DbContextOptions<MyDbContext> _dbOptions;

        private readonly List<IWorker> _workers;

        public WorkerRunner(DbContextOptions<MyDbContext> dbOptions, List<IWorker> workers)
        {
            _dbOptions = dbOptions;
            _workers = workers;
        }

        public void RunWorkers()
        {
            using (var context = new MyDbContext(_dbOptions))
            {
                using (var tran = context.Database.BeginTransaction())
                {
                    foreach (var worker in _workers)
                        worker.DoWork(() =>
                        {
                            // This won't work
                            var db = new MyDbContext(_dbOptions);
                            // And this one will even throw exception when used with in-memory database (during unit testing)
                            db.Database.UseTransaction(tran.GetDbTransaction());
                            return context;
                        });

                    tran.Commit();
                }
            }
        }
    }

The options are provided through DI and already contain a connection string (or in-memory DB options, if running in a unit test). The only way I can share a transaction between two contexts is to also share the DbConnection - but I can't do that, because there's no core API that would let me copy the connection from one options object to another. Even if I know the provider, or write conditional code, UseSqlServer(SqlConnection) will throw an exception because the options object already has a connection string configured.

[sam-wheat]

Please see:

AdaptiveClient

AdaptiveClient.EntityFrameworkCore

AdaptiveClient Demo

The only way I can share a transaction between two contexts is to also share the DbConnection...

This is probably related to your DI container. AdaptiveClient uses Autofac and injects a single (shared) instance of DBContext into all services (workers) that request it.

Also, AdaptiveClient resolves components based on your connection string. So if your app targets SQL Server and MySQL and SQLite AdaptiveClient will look at your connection string and inject the correct platform-specific implementation for you.

[BalassaMarton]

@sam-wheat That's nice, but I specifically do __not__ want to share a single DbContext, but create an arbitrary amount of contexts that work in the same transaction, all without any provider-specific code. I actually came up with a solution and writing an article about it, but until then, see my repo
https://github.com/BalassaMarton/EFCore.TransactionExtensions

[sam-wheat]

That is certainly an interesting approach I would love to read your article. Please post a link here when you are done.

[ajcvickers]

@BalassaMarton I have marked #8494 for re-triage since that would make this scenario a lot easier. I would also be interested in reading your article--I have some ideas for working around this, but I'd like to see what you come up with first.

[BalassaMarton]

@ajcvickers I've posted it as a readme, I'd really appreciate if you guys could comment on it. There's great chance I still don't get everything about EF Core & transactions.
https://github.com/BalassaMarton/EFCore.TransactionExtensions

[ajcvickers]

@BalassaMarton There's a lot of text there--I skimmed some of it. Using D.I. to orchestrate the services is also what I was thinking. Leaving apart the transaction abstraction (which can be added), here's what I came up with to share a DbConnection or in-memory database with multiple context instances.

First, there is a service that handles the shared configuration:
```C#
public interface IContextConfigurer
{
void ConfigureContext(DbContextOptionsBuilder optionsBuilder);
}

public class SqlServerContextConfigurer : IContextConfigurer, IDisposable
{
private DbConnection _connection;
private readonly string _connectionString;

public SqlServerContextConfigurer(IConfiguration configuration)
{
    // TODO: Read connection string from config
    _connectionString = @"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0";
}

public void ConfigureContext(DbContextOptionsBuilder optionsBuilder)
{
    if (_connection == null)
    {
        _connection = new SqlConnection(_connectionString);
    }

    optionsBuilder.UseSqlServer(_connection);
}

public void Dispose()
{
    _connection?.Dispose();
    _connection = null;
}

}

public class InMemoryContextConfigurer : IContextConfigurer
{
private readonly string _databaseName;

public InMemoryContextConfigurer(IConfiguration configuration)
{
    // TODO: Read database name from config
    _databaseName = "MyDb";
}

public void ConfigureContext(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseInMemoryDatabase(_databaseName);
}

}

Now setup the DbContext to have this service constructor-injected:
```C#
public class BloggingContext : DbContext
{
    private readonly IContextConfigurer _configurer;

    public BloggingContext(IContextConfigurer configurer)
    {
        _configurer = configurer;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => _configurer.ConfigureContext(optionsBuilder);
}

Now, if the DbContext is registered as transient, then we can create multiple instances from the service provider and they will all get the same configuration. For example, in a test console app:
```C#
public static void Main()
{
var serviceProvider = new ServiceCollection()
.AddSingleton() // For test
.AddScoped()
.AddDbContext(ServiceLifetime.Transient)
.BuildServiceProvider();

using (var scope = serviceProvider.CreateScope())
{
    var context1 = scope.ServiceProvider.GetService<BloggingContext>();
    var context2 = scope.ServiceProvider.GetService<BloggingContext>();

    Debug.Assert(context1 != context2);
    Debug.Assert(context1.Database.GetDbConnection() == context2.Database.GetDbConnection());
}

}
```

[BalassaMarton]

Yes, it's a very similar approach. In both cases, an external service is configuring the DbContext, and in both cases it is a requirement of the service that the same instance has to create contexts for the same connection. I might even like your version better because it retains the ability to control where and how to instantiate the DbContext. Anyway, I'll work on my tool a little more and see where it gets me.