What's a good pattern to create DbContext when dynamic data determines connection string

Question

I get a data set from another web api. It may contain multiple records from different clients. I have to transform these data and write them to the client's database. Each client has their own database. The schema of the client databases are exactly the same.

I created a DbContext factory that creates a new instance of DbContext based on the client the app is currently working on.

public ClientDbContext CreateClientDbContext(string clientNumber)
{
      var optionsBuilder = new DbContextOptionsBuilder<ClientDbContext>();
      var clientConnection = string.Format(_configuration.GetConnectionString("clientConnection"), clientNumber);
      optionsBuilder.UseSqlServer(clientConnection);

      clientDbContext clientDbContext = new ClientDbContext(optionsBuilder.Options);

      return clientDbContext;
}

I use the factory this way:

foreach (var case in caseList)
{
    var clientDbContext = await _clientDbContextFactory.CreateClientDbContext(case.ClientNumber);
    _clientRepository = new ClientRepository(clientDbContext);
    var updatedCase = /// transform case here
    await _clientRepository.CreateCases(updatedCase);
}

Is there an optimal why of doing this? It's possible that several rows of data will have the same client, so I would like to reuse the same ClientDbContext.

Answer 1

You can move your logic for creating ClientContext into another responsible class (according to SOLID principles), something like DbContextFactory and store there created DbContext's for each client. Like this:

public class DbContextFactory
{
    private readonly IConfiguration _configuration;
    private readonly Dictionary<string, ClientDbContext> _clientContexts = new Dictionary<string, ClientDbContext>();

    public DbContextFactory(IConfiguration configuration)
    {
        _configuration = configuration;
    }

    public ClientDbContext GetOrCreateClientContext(string clientNumber)
    {
        // if you have context already created - return it
        if (_clientContexts.ContainsKey(clientNumber))
            return _clientContexts[clientNumber];

        var optionsBuilder = new DbContextOptionsBuilder<ClientDbContext>();
        var clientConnection = string.Format(_configuration.GetConnectionString("clientConnection"), clientNumber);
        optionsBuilder.UseSqlServer(clientConnection);

        var clientDbContext = new ClientDbContext(optionsBuilder.Options);
        _clientContexts[clientNumber] = clientDbContext;

        return clientDbContext;
    }
}

Then in your worker class you can group your data by ClientNumber, for each client create (or get already created) DbContext and repository, then do data update.

public class Worker
{
    private readonly DbContextFactory _factory;

    public Worker(DbContextFactory factory)
    {
        _factory = factory;
    }

    public async Task DoWorkAsync()
    {
        // group by ClientNumber
        var groupedCases = caseList.GroupBy(x => x.ClientNumber);

        foreach (var groupedCase in groupedCases)
        {
            // For each client create context and repository
            var clientContext = _factory.GetOrCreateClientContext(groupedCase.Key);
            var clientRepository = new ClientRepository(clientContext);

            foreach (var @case in groupedCases)
            {
                var updatedCase = // transform case here
                await clientRepository.CreateCases(updatedCase);
            }
        }
    }
}

You can use dependency injection or just create these clases like that:

var factory = new DbContextFactory(yourConfiguration);
var worker = new Worker(factory);
await worker.DoWorkAsync();