我来给你梳理下这个场景的落地方案，完全适配你本地Windows 10 + SQL Server Express的环境，核心思路是绕开Startup里固定连接字符串的限制——用一个主数据库存储租户与年度库的映射关系，用户选择后动态生成目标库的连接字符串，再让业务DbContext使用这个动态连接。具体步骤如下：

1. 配置主数据库（存储映射关系） #

首先需要一个专门的主库，用来存放COMPANY_CODE、COMPANY_YEAR和COMPANY_DBNAME的映射表。

• 在appsettings.json里配置主库连接字符串：

"ConnectionStrings": {
  "MasterTenantDb": "Server=.;Database=MasterTenantDb;Trusted_Connection=True;MultipleActiveResultSets=true"
}

• 在Startup.cs里注册主库DbContext并启用Session（用来存用户选择的租户信息）：

public void ConfigureServices(IServiceCollection services)
{
    // 注册主DbContext，用于查询租户-数据库映射
    services.AddDbContext<MasterTenantDbContext>(options =>
        options.UseSqlServer(Configuration.GetConnectionString("MasterTenantDb")));
    
    // 启用Session，存储用户选择的租户信息
    services.AddSession(options =>
    {
        options.IdleTimeout = TimeSpan.FromHours(1);
        options.Cookie.HttpOnly = true;
        options.Cookie.IsEssential = true;
    });

    services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}

public void Configure(IApplicationBuilder app, IHostingEnvironment env)
{
    // 其他中间件（异常处理、静态文件等）...
    app.UseSession(); // 必须放在UseMvc之前
    app.UseMvc(routes =>
    {
        routes.MapRoute(
            name: "default",
            template: "{controller=Home}/{action=Index}/{id?}");
    });
}

2. 定义主DbContext和映射实体 #

创建对应映射表的实体类和主库DbContext：

// 对应你的映射表实体
public class TenantDbMapping
{
    public string COMPANY_CODE { get; set; }
    public int COMPANY_YEAR { get; set; }
    public string COMPANY_DBNAME { get; set; }
}

// 主库DbContext
public class MasterTenantDbContext : DbContext
{
    public MasterTenantDbContext(DbContextOptions<MasterTenantDbContext> options) : base(options)
    {
    }

    public DbSet<TenantDbMapping> TenantDbMappings { get; set; }

    // 配置复合主键（COMPANY_CODE + COMPANY_YEAR）
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<TenantDbMapping>()
            .HasKey(m => new { m.COMPANY_CODE, m.COMPANY_YEAR });
    }
}

3. 让业务DbContext支持动态连接字符串 #

业务DbContext不能再依赖Startup里的固定连接，改成通过构造函数接收动态连接字符串：

public class BusinessDbContext : DbContext
{
    private readonly string _dynamicConnectionString;

    // 构造函数接收动态生成的连接字符串
    public BusinessDbContext(string connectionString)
    {
        _dynamicConnectionString = connectionString;
    }

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

    // 这里放你的业务实体DbSet，比如：
    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<Customer> Customers { get; set; }
}

4. 处理用户选择租户的逻辑 #

用户认证后，选择公司代码和年度，后端完成：查询映射关系→生成目标库连接→存入Session。

示例Controller代码：

public class TenantSelectionController : Controller
{
    private readonly MasterTenantDbContext _masterDbContext;
    private readonly IConfiguration _configuration;

    public TenantSelectionController(MasterTenantDbContext masterDbContext, IConfiguration configuration)
    {
        _masterDbContext = masterDbContext;
        _configuration = configuration;
    }

    // 显示租户选择页面
    public IActionResult Index()
    {
        // 拉取当前用户有权限的公司列表（结合你的认证逻辑过滤）
        var companies = _masterDbContext.TenantDbMappings.Select(m => m.COMPANY_CODE).Distinct().ToList();
        ViewBag.Companies = companies;
        return View();
    }

    // 提交选择的公司和年度
    [HttpPost]
    public async Task<IActionResult> Select(string companyCode, int companyYear)
    {
        // 查询对应的数据库映射（这里要加用户权限过滤，比如从Claims取用户ID）
        var dbMapping = await _masterDbContext.TenantDbMappings
            .FirstOrDefaultAsync(m => m.COMPANY_CODE == companyCode && m.COMPANY_YEAR == companyYear);

        if (dbMapping == null)
        {
            ModelState.AddModelError("", "选择的公司/年度不存在");
            return View("Index");
        }

        // 用SqlConnectionStringBuilder安全替换数据库名，生成目标连接字符串
        var masterConnBuilder = new SqlConnectionStringBuilder(_configuration.GetConnectionString("MasterTenantDb"));
        masterConnBuilder.InitialCatalog = dbMapping.COMPANY_DBNAME;
        var tenantConnString = masterConnBuilder.ToString();

        // 把连接字符串和租户信息存入Session
        HttpContext.Session.SetString("CurrentTenantConnString", tenantConnString);
        HttpContext.Session.SetString("CurrentCompanyCode", companyCode);
        HttpContext.Session.SetString("CurrentCompanyYear", companyYear.ToString());

        return RedirectToAction("Index", "Dashboard");
    }
}

5. 在业务逻辑中使用动态DbContext #

在需要访问业务库的地方，从Session取出连接字符串，创建BusinessDbContext实例：

public class DashboardController : Controller
{
    public async Task<IActionResult> Index()
    {
        var tenantConnString = HttpContext.Session.GetString("CurrentTenantConnString");
        if (string.IsNullOrEmpty(tenantConnString))
        {
            // 未选择租户时跳转到选择页面
            return RedirectToAction("Index", "TenantSelection");
        }

        // 创建业务DbContext并执行查询
        using (var dbContext = new BusinessDbContext(tenantConnString))
        {
            var recentInvoices = await dbContext.Invoices
                .OrderByDescending(i => i.CreateTime)
                .Take(10)
                .ToListAsync();
            
            return View(recentInvoices);
        }
    }
}

6. 进阶优化：用工厂模式封装DbContext创建 #

如果不想每次手动new BusinessDbContext，可以封装一个工厂类，结合依赖注入使用：

public interface IBusinessDbContextFactory
{
    BusinessDbContext Create();
}

public class BusinessDbContextFactory : IBusinessDbContextFactory
{
    private readonly IHttpContextAccessor _httpContextAccessor;
    private readonly MasterTenantDbContext _masterDbContext;
    private readonly IConfiguration _configuration;

    public BusinessDbContextFactory(IHttpContextAccessor httpContextAccessor, MasterTenantDbContext masterDbContext, IConfiguration configuration)
    {
        _httpContextAccessor = httpContextAccessor;
        _masterDbContext = masterDbContext;
        _configuration = configuration;
    }

    public BusinessDbContext Create()
    {
        var session = _httpContextAccessor.HttpContext.Session;
        var companyCode = session.GetString("CurrentCompanyCode");
        var companyYear = int.TryParse(session.GetString("CurrentCompanyYear"), out var year) ? year : 0;

        if (string.IsNullOrEmpty(companyCode) || companyYear == 0)
        {
            throw new InvalidOperationException("请先选择公司和年度");
        }

        var dbMapping = _masterDbContext.TenantDbMappings
            .FirstOrDefault(m => m.COMPANY_CODE == companyCode && m.COMPANY_YEAR == companyYear);

        if (dbMapping == null)
        {
            throw new InvalidOperationException("无效的租户信息");
        }

        var connBuilder = new SqlConnectionStringBuilder(_configuration.GetConnectionString("MasterTenantDb"));
        connBuilder.InitialCatalog = dbMapping.COMPANY_DBNAME;

        return new BusinessDbContext(connBuilder.ToString());
    }
}

在Startup里注册工厂：

services.AddHttpContextAccessor(); // 必须注册才能访问HttpContext
services.AddScoped<IBusinessDbContextFactory, BusinessDbContextFactory>();

之后在Controller里注入使用：

public class DashboardController : Controller
{
    private readonly IBusinessDbContextFactory _dbContextFactory;

    public DashboardController(IBusinessDbContextFactory dbContextFactory)
    {
        _dbContextFactory = dbContextFactory;
    }

    public async Task<IActionResult> Index()
    {
        try
        {
            using (var dbContext = _dbContextFactory.Create())
            {
                var recentInvoices = await dbContext.Invoices
                    .OrderByDescending(i => i.CreateTime)
                    .Take(10)
                    .ToListAsync();
                
                return View(recentInvoices);
            }
        }
        catch (InvalidOperationException ex)
        {
            return RedirectToAction("Index", "TenantSelection");
        }
    }
}

关键注意事项 #

• 权限控制：查询租户映射时必须结合当前用户的认证信息过滤，确保用户只能访问有权限的公司/年度。
• Session部署：生产环境多服务器部署时，要把Session改成分布式缓存（比如Redis），避免Session丢失。
• 连接安全：如果担心Session存连接字符串有风险，可以只存companyCode和companyYear，每次创建DbContext时去主库查询生成连接，即使Session被篡改也会因查不到映射报错。
• 资源释放：动态创建的DbContext要用using包裹，确保及时释放数据库连接，避免连接池耗尽。

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