如何基于公司ID选择数据库？避免重复创建DbContext类

我目前有两个结构完全相同的数据库（未来可能会增加更多），分别存储公司X和公司Y的数据。现在需要实现基于公司ID选择对应数据库的方案，但当前得为每个数据库创建一个DbContext类，哪怕它们结构完全一致，请问有没有其他替代方案？

现有实现代码 #

现有DbContext定义 #

// 公司X对应的DbContext
public class MyDatabase1 : DbContext, IMyDatabase
{
    public MyDatabase1(DbContextOptions<MyDatabase1> options)
        : base(options)
    {
    }

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

// 公司Y对应的DbContext
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; }
}

Program.cs服务注册 #

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

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

现有数据库选择逻辑 #

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 dbContext = _databaseStrategy.GetDatabase(companyId);

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

方案1：单一DbContext+动态切换连接字符串 #

不需要为每个数据库创建独立DbContext，仅用一个通用DbContext，根据公司ID动态加载对应连接字符串。

1. 改造通用DbContext #

public class MyDatabase : DbContext, IMyDatabase
{
    private readonly string _connectionString;

    // 通过构造函数注入目标连接字符串
    public MyDatabase(string connectionString)
    {
        _connectionString = connectionString;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
            optionsBuilder.UseSqlServer(_connectionString);
        }
    }

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

2. 创建DbContext工厂类 #

负责根据公司ID匹配连接字符串并生成DbContext实例：

public class MyDatabaseFactory
{
    private readonly IConfiguration _config;

    public MyDatabaseFactory(IConfiguration config)
    {
        _config = config;
    }

    public IMyDatabase Create(int companyId)
    {
        // 约定连接字符串键为"Company{ID}Db"，比如Company1Db、Company2Db
        var connKey = $"Company{companyId}Db";
        var connString = _config.GetConnectionString(connKey);

        if (string.IsNullOrEmpty(connString))
        {
            throw new InvalidOperationException($"未找到公司ID {companyId}对应的连接字符串");
        }

        return new MyDatabase(connString);
    }
}

3. 注册服务 #

builder.Services.AddScoped<MyDatabaseFactory>();

4. 实际使用 #

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

    public PostsController(MyDatabaseFactory dbFactory)
    {
        _dbFactory = dbFactory;                
    }

    public ActionResult<List<Post>> Get()
    {
        var companyId = 1;
        // 使用using管理DbContext生命周期，避免内存泄漏
        using var dbContext = _dbFactory.Create(companyId);

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

方案2：泛型基类DbContext（兼顾类型区分与代码复用） #

如果需要保留DbContext的类型标识，但不想重复编写DbSet等代码，可以用泛型基类减少冗余。

1. 定义泛型基类 #

public abstract class MyDatabaseBase<TContext> : DbContext, IMyDatabase
    where TContext : DbContext
{
    protected MyDatabaseBase(DbContextOptions<TContext> options) : base(options)
    {
    }

    // 所有共用的DbSet都定义在基类中
    public DbSet<Post> Posts { get; set; } = default!;
}

// 每个公司的DbContext只需继承基类，无需重复写DbSet
public class MyDatabase1 : MyDatabaseBase<MyDatabase1>
{
    public MyDatabase1(DbContextOptions<MyDatabase1> options) : base(options)
    {
    }
}

public class MyDatabase2 : MyDatabaseBase<MyDatabase2>
{
    public MyDatabase2(DbContextOptions<MyDatabase2> options) : base(options)
    {
    }
}

2. 优化数据库选择逻辑（支持动态扩展） #

通过配置文件映射公司ID与DbContext类型，避免硬编码：

public class DatabaseStrategy
{
    private readonly IServiceProvider _serviceProvider;
    private readonly Dictionary<int, Type> _companyDbMap;

    public DatabaseStrategy(IServiceProvider serviceProvider, IConfiguration config)
    {
        _serviceProvider = serviceProvider;
        // 从appsettings读取公司ID与DbContext类型的映射
        _companyDbMap = config.GetSection("CompanyDbContextMap")
            .Get<Dictionary<int, Type>>() ?? new();
    }

    public IMyDatabase GetDatabase(int companyId)
    {
        if (!_companyDbMap.TryGetValue(companyId, out var dbType))
        {
            throw new InvalidOperationException($"未找到公司ID {companyId}对应的数据库上下文");
        }

        return (IMyDatabase)_serviceProvider.GetRequiredService(dbType);
    }
}

3. appsettings.json配置示例 #

{
  "CompanyDbContextMap": {
    "1": "YourNamespace.MyDatabase1",
    "2": "YourNamespace.MyDatabase2"
  },
  "ConnectionStrings": {
    "MyDatabase1": "...",
    "MyDatabase2": "..."
  }
}

4. 服务注册与使用 #

注册逻辑和原有方式一致，使用时无需修改控制器代码。

方案对比 #

内容的提问来源于stack exchange，提问作者Aleksander Chelpski