EF Core execute against different databases with identical schema

Question

EDIT: I also have a completely different approach where I have just about all of that crap commented out. I am able to call into my method and I configured the connect string to connect to the server, but not to the database. Then I want to connect to the different database depending on some data passed in (like the database name maybe?). Is there a way to call OnConfiguring on the fly so I can configure my connection string to be different each time I call my method?

I know some of you are going to look at this and roll your eyes with my stupidity, but we all had to start somewhere! I have a scenario with one database server but multiple databases all which share the same schema. Currently I am rewriting code for each database and it is a mess, so I am trying to clear it up. I have gotten myself pretty confused here so I am looking for advice from some of you gurus out there. I am a beginner here and trying to do something I find very advanced here so go easy on me. I will keep my examples with just two databases, but really there are 10+ databases that are all the same that I need to switch between often. My goal is to try to get rid of this 1, 2, 3, 4, 9, 10, etc stuff all over the place when I want to save or access a record, and have one thing controlling it all.

I created a base class for my database context

public partial class opkDataBaseContext : DbContext
{
    private DbContextOptions<opkData1Context> options1;
    private DbContextOptions<opkData2Context> options2;

    public opkDataBaseContext()
    {
    }

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

    public opkDataBaseContext(DbContextOptions<opkData1Context> options)
    {
        this.options1 = options;
    }

    public opkDataBaseContext(DbContextOptions<opkData2Context> options)
    {
        this.options2 = options;
    }

    public virtual DbSet<OrgUnits> OrgUnits { get; set; }



    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {


...
    }
       OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);


    public void CreateNewOrganization(CreateCompleteOrgViewModel model)
    {
        var nameParameter = new SqlParameter("@TopLevelOrgName", model.Name);
        var codeParameter = new SqlParameter("@Code", model.Code);
        var DbNameParameter = new SqlParameter("@DBName", model.DbCatalog);
        var debugParameter = new SqlParameter("@debug", "0");
        var firstNameParameter = new SqlParameter("@FirstName", model.FirstName);
        var lastNameParameter = new SqlParameter("@LastName", model.LastName);
        var userNameParameter = new SqlParameter("@Username", model.UserName);

        this.Database.ExecuteSqlRaw("CreateRootOrg @TopLevelOrgName, @Code, @DBName, @debug, @FirstName, @LastName, @Username",
            nameParameter, codeParameter, DbNameParameter, debugParameter, firstNameParameter, lastNameParameter, userNameParameter);

    }

Here is my Startup.cs

public void ConfigureServices(IServiceCollection services)
    {
        if (_env.IsProduction())
        {
            var opkCoreConnection = Configuration.GetConnectionString("opkCoreDatabase");
            services.AddDbContext<opkCoreContext>(options => options.UseSqlServer(opkCoreConnection));

            var opkData1Connection = Configuration.GetConnectionString("opkData1Database");
            services.AddDbContext<opkData1Context>(options => options.UseSqlServer(opkData1Connection));

            var opkData2Connection = Configuration.GetConnectionString("opkData2Database");
            services.AddDbContext<opkData2Context>(options => options.UseSqlServer(opkData2Connection));


            var opkDataLocalBaseConnection = Configuration.GetConnectionString("opkDataBaseDatabase");
            services.AddDbContext<opkDataBaseContext>(options => options.UseSqlServer(opkDataLocalBaseConnection));

Then I have one of these for each database:

    public partial class opkData1Context : opkDataBaseContext
{
    public opkData1Context()
    {
    }

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

My current error is:

"Error while validating the service descriptor 'ServiceType: Models.DataModels.opkDataBaseContext Lifetime: Scoped ImplementationType: Models.DataModels.opkDataBaseContext': Unable to activate type 'Models.DataModels.opkDataBaseContext'. The following constructors are ambiguous:\r\nVoid .ctor(Microsoft.EntityFrameworkCore.DbContextOptions'1[Models.DataModels.opkDataBaseContext])\r\nVoid .ctor(Microsoft.EntityFrameworkCore.DbContextOptions'1[Models.opkData1Context])"} System.Exception {System.InvalidOperationException}

I have been messing with this all day. First, am I even going down the right path or is this just a dumb idea? Second, any idea where I am going wrong? Thank you!

Answer 1

I have a scenario with one database server but multiple databases all which share the same schema

This is very common, even a best-practice, in Software-as-a-Service (SaaS) applications.

While this is not obvious, it turns out to be quite simple. You just need some way to pick the connection string at runtime, probably based on a combination of the config and something in the HttpContext (like the user's identity, the path, the host header, etc). Then you configure the DbContext for DI like this:

    public void ConfigureServices(IServiceCollection services)
    {
        services.AddHttpContextAccessor();

        services.AddDbContext<MyDbContext>((sp, opt) =>
        {
            var httpContext = sp.GetRequiredService<IHttpContextAccessor>();
            var config = sp.GetRequiredService<IConfiguration>();

            string connectionString = GetConnectionStringFromHttpContext(httpContext, config);

            opt.UseSqlServer(connectionString, o => o.UseRelationalNulls());

        });

        services.AddControllers();
    }

where GetConnectionStringFromHttpContext is a custom method that builds the connection string based on the config and the HttpContext.

Answer 2

You could take a look a feature introduced in EF Core 5, which can change the connection or connection string on an already initialized context