How can I choose a database based on company id?

Question

I have exacly the same two databases (in the feature I can have them more). The first database has data of company X and the second database has data of company Y. Is there any solution how can I choose which database I want to use based on company id? Because now I have to create as many DBContext classes as I have databases although each database is the same. Is any other way or not?

// the first DbContext for company X:
    public class MyDatabase1 : DbContext, IMyDatabase
    {
            public MyDatabase1 (DbContextOptions<MyDatabase1> options)
                : base(options)
            {
            }
    
        public DbSet<Post> Posts { get; set; } = default!;
    }
    
// the second DbContext for company Y:
    public class MyDatabase2 : DbContext, IMyDatabase
    {
            public MyDatabase2 (DbContextOptions<MyDatabase2> options)
                : base(options)
            {
            }
    
        public DbSet<Post> Posts { get; set; } = default!;
    }
    
    public interface IMyDatabase
    {
        DbSet<Post> Posts { get; set; }
    }
    
    public class Post
    {
        public int PostId { get; set; }
        public string Title { get; set; }
    }

And then I have to register it in Program.cs:

builder.Services.AddDbContext<MyDatabase1>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MyDatabase1")));

builder.Services.AddDbContext<MyDatabase2>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("MyDatabase2")));

I use it in that way:

public class DatabaseStrategy
{
    private readonly MyDatabase1 _context1;
    private readonly MyDatabase2 _context2;

    public DatabaseStrategy(MyDatabase1 context1, MyDatabase2 context2)
    {
        _context1 = context1;
        _context2 = context2;
    }

    public IMyDatabase GetDatabase(int companyId)
    {
        if (companyId == 1)
        { 
            return _context1; 
        }
        else if (companyId == 2)
        {
            return _context2;
        }
        else
        {
            throw new Exception();
        }
    }
}

builder.Services.AddScoped<DatabaseStrategy>();

[ApiController]
[Route("[controller]")]
public class PostsController : ControllerBase
{
    private readonly DatabaseStrategy _databaseStrategy;

    public PostsController(DatabaseStrategy databaseStrategy)
    {
        _databaseStrategy = databaseStrategy;                
    }

    public ActionResult<List<Post>> Get()
    {
        var companyId = 1;
        var databaseStrategy = _databaseStrategy.GetDatabase(companyId);

        var result = databaseStrategy.Posts.ToList();
        return result;
    }
}

Answer 1

You need create single DbContext.cs class like below :

public class MyDatabase : DbContext, IMyDatabase
{
    public MyDatabase(DbContextOptions<MyDatabase> options)
        : base(options)
    {
    }

    public DbSet<Post> Posts { get; set; } = default!;
}

And in appsettings.json specify the two connection string like below:

{
    "ConnectionStrings": {
        "Company1": "Server=server1;Database=company1_db;User Id=user;Password=password;",
        "Company2": "Server=server2;Database=company2_db;User Id=user;Password=password;"
    }
}

Afterward, create DBContextFactory.cs class which relies on the company id like below:

public class DbContextFactory
{
    private readonly IConfiguration _configuration;
    private readonly IServiceProvider _serviceProvider;

    public DbContextFactory(IConfiguration configuration, IServiceProvider serviceProvider)
    {
        _configuration = configuration;
        _serviceProvider = serviceProvider;
    }

    public MyDatabase CreateDbContext(int companyId)
    {
        var optionsBuilder = new DbContextOptionsBuilder<MyDatabase>();
        var connectionString = GetConnectionString(companyId);
        optionsBuilder.UseSqlServer(connectionString);
        return new MyDatabase(optionsBuilder.Options);
    }

    private string GetConnectionString(int companyId)
    {
        return companyId switch
        {
            1 => _configuration.GetConnectionString("Company1"),
            2 => _configuration.GetConnectionString("Company2"),
            _ => throw new Exception("Invalid company ID")
        };
    }
}

Don't forget to register DbContextFactory.cs class in your program.cs like below

builder.Services.AddSingleton<DbContextFactory>();

Now you can modify your DatabaseStrategy.cs class to dynamically get the DbContext by passing the company ID like below :

public class DatabaseStrategy
{
    private readonly DbContextFactory _dbContextFactory;

    public DatabaseStrategy(DbContextFactory dbContextFactory)
    {
        _dbContextFactory = dbContextFactory;
    }

    public IMyDatabase GetDatabase(int companyId)
    {
        return _dbContextFactory.CreateDbContext(companyId);
    }
}

Finally, at the controller level expose the endpoint to pass List<Posts> via controller

[ApiController]
[Route("[controller]")]
public class PostsController : ControllerBase
{
    private readonly DatabaseStrategy _databaseStrategy;

    public PostsController(DatabaseStrategy databaseStrategy)
    {
        _databaseStrategy = databaseStrategy;                
    }

    [HttpGet]
    public ActionResult<List<Post>> Get(int companyId)
    {
        var database = _databaseStrategy.GetDatabase(companyId);
        var result = database.Posts.ToList();
        return result;
    }
}

Note: It's not the best practice to expose your DatabaseStrategy.cs directly in your controller. Therefore it's better to add an additional service level between DatabaseStrategy.cs and your API controller.