Create an EF Core DbContext at runtime based on request parameters

Question

Background

We are building a web application using ASP.Net Core and Entity Framework Core 2.2

We are connecting to legacy databases. The setup is that there are 16 databases, all with the exact same schema, holding different contextual data. We cannot change this.

We have a requirement to connect to a specific database at runtime, based on request parameters.

Imagine that there is a database for every business under the parent company.

Imagine that each database has tables like Employee, Client and Shift (a shift that the employee has worked for a Client).

There is also a 'central' database which holds information common across all the other databases, like settings etc.

We have a requirement to list all the Employees across all businesses in a single list view.

We plan to retrieve this data by using a SQL View in the central database, which simply does a UNION across each of the other databases (if you have a suggestion on how to do this better, please share it).

CREATE VIEW dbo.v_all_employees AS 
    SELECT EmployeeId, Fullname, 1 AS BusinessId FROM BusinessA.dbo.Employees
    UNION ALL SELECT EmployeeId, Fullname, 2 AS BusinessId FROM BusinessB.dbo.Employees
    -- etc. etc.

We have a single set of Models which represent all the Entities (tables) across all the databases, since they share the exact same schema e.g. one Employee class, one Client class etc.

Use case

The user goes to a web page to see the list of Employees across all the businesses via this route:

http://example.org/employees

User then clicks on a 'Details' link for an individual employee to view further details, taking the user to the url:

http://example.org/employees/details?employeeId=123&businessId=xyz

What I am stuck on is how do we instantiate the business specific DbContext at runtime, given the BusinessId.

I have come up with three ways (and now a fourth thanks to @mylee) to achieve the desired result and am looking for feedback from the community.

Every option assumes that each DbContext will implement an interface which exposes all the DbSets and DbContext methods and then we'll use the Factory pattern to handle the determination of which DbContext implementation to use.

The first option: Simply have the Factory create the correct DbContext based on the request parameter 'bizId'.

However, this requires that each DbContext over-rides the OnConfiguring method and sets up the DbProvider - something that the dotnet Core framework does for us via its IoC container extension method AddDbContext:

public class ContextFactory : IContextFactory
{
    public IBIZContext GetContext(int bizId)
    {
        switch (bizId)
        {
            // Newing-up the DbContexts like this requires that the OnConfiguring method
            // for each context be present in each DbContext to setup the DbProvider
            // with the correct connection string.
            case 6:
                return new BIZ_AContext();
            case 7:
                return new BIZ_BContext();
            default:
                throw new Exception("Unexpected Business Id");
        }
    }
}

This issue with this is that I don't like how we're newing up the contexts here. It requires that we over-ride the OnConfiguring method in each context and have access to the Connection String.

The second option:

I would prefer to use the built in IoC Container which is setup in Startup.cs but this exhibits the Service Locator Anti-Pattern. Also, it leaks the HttpContext out of the Web project into the Infrastructure project (I'm using an Onion architecture):

public class ContextFactoryUsingLocator : IContextFactoryUsingLocator
{
    public IBIZContext GetContext(IHttpContextAccessor httpContextFactory, int bizId)
    {
        // Injecting the HttpContextAccessor gives us access to the IoC Container via RequestServices;
        // But using it here exhibits the Service Locator anti-pattern.
        // Perhaps its ok to use the Service Locator pattern within a Factory in this case?
        switch (bizId)
        {
            case 6:
                return (BIZ_AContext)httpContextFactory.HttpContext.RequestServices.GetService(typeof(BIZ_AContext));
            case 7:
                return (BIZ_BContext)httpContextFactory.HttpContext.RequestServices.GetService(typeof(BIZ_BContext));
            default:
                throw new Exception("Unexpected Business Id");
        }
    }
}

A third option

Inject each DbContext into the Factory and have the Factory simply return the correct instance:

public class ContextFactoryInjected : IContextFactoryInjected
{
    private readonly BIZ_AContext _bizAContext;
    private readonly BIZ_BContext _bizBContext;

    public ContextFactoryInjected(
        BIZ_AContext bizAContext, 
        // 14 other DbContext dependencies omitted here for brevity
        BIZ_BContext bizBContext)
    {
        // Injecting all 16 DbContexts into the Factory seems to counter the intention of the Factory since the IoC Container
        // would be managing the creation of all the instances; isn't that the responsibility of the Factory?

        // More importantly; wouldn't this have serious performance implications, creating 16 instances of a DbContext on every Request?
        _bizAContext = bizAContext;
        _bizBContext = bizBContext;
    }

    public IBIZContext GetContext(int bizId)
    {
        switch (bizId)
        {
            case 6:
                return _bizAContext;
            case 7:
                return _bizBContext;
            default:
                throw new Exception("Unexpected Business Id");
        }
    }
}

The fourth option Encapsulate the configuration of the DbContext within the Factory (this method was suggested by @mylee)

public class ContextFactoryConfigured : IContextFactoryConfigured
{
    public IBIZContext GetContext(int bizId)
    {
        switch (bizId)
        {
            // Newing-up the DbContexts like this encapsulates all the details required for the DbContext within the Factory
            case 6:
                var bizAOptionsBuilder = new DbContextOptionsBuilder<BizAContext>();
                bizAOptionsBuilder.UseSqlServer(Settings.BizAConnectionString);
                return new BizAContext(bizAOptionsBuilder.Options);
            case 7:
                var bizBOptionsBuilder = new DbContextOptionsBuilder<BizBContext>();
                bizBOptionsBuilder.UseSqlServer(Settings.BizBConnectionString);
                return new BizBContext(bizBOptionsBuilder.Options);
            default:
                throw new Exception("Unexpected Business Id");
        }
    }
}

Do you agree that option 2 exhibits the Service Locator anti-pattern i.e. is it right to say that the Factory depends on the objects its managing the creation of?

Do you think that option 4 is the best approach out of these, given that it is normally the responsibility of a Factory to 'new-up' its objects AND it doesn't result in a mixing of concerns (i.e. doesn't require the HttpContext ) AND it encapsulates all the details (e.g. ConnectionString) required to build the context within the Factory?

Or is there a way to achieve this using Dependency Injection without resulting in a mixing of concerns?

Or is there an even better way that I've not mentioned here?

Answer 1

We have the same problem with a legacy system using multiple databases of the same structure and came up with a solution that is similar to your option 4:

There is an interface and factory method to create a dbContext. It takes a connection string as a parameter:

public interface ICustomDbContextFactory<out T> where T: DbContext
{
    T CreateDbContext(string connectionString);
}

public class CustomDbContextFactory<T> : ICustomDbContextFactory<T>  where T: DbContext
{
    public T CreateDbContext(string connectionString)
    {
        var optionsBuilder = new DbContextOptionsBuilder<T>();
        optionsBuilder.UseSqlServer(connectionString);
        return System.Activator.CreateInstance(typeof(T), optionsBuilder.Options) as T;
    }
}

The factory is registered with the DI as a singleton:

services.AddSingleton<ICustomDbContextFactory<CustomDbContext>, CustomDbContextFactory<CustomDbContext>>();

And then you just use it whenever needed (need to be able to get the factory injected):

using (var dbContext = customDbContextFactory.CreateDbContext(connectionString))
{
   // use your dbContext here
}

I think it pretty much matches your nr. 4 solution (except for the fact that we have the logic to pull out the connection string separated from the factory) and we found it to be the cleanest solution of the problem we could figure out. Would like to hear what did you finish up implementing and if anyone has any better idea how to approach the problem.