EF Core 7.0基于Schema的多租户：生成通用迁移脚本方案问询

核心思路 #

绕过EF Core默认硬编码Schema的逻辑，通过自定义迁移生成器+脚本动态替换机制，实现单版本迁移脚本适配所有租户，生成时用占位符标记Schema，执行时注入具体租户的Schema名称。

具体实现步骤 #

1. 配置DbContext动态Schema #

在DbContext中通过构造函数注入Schema名称，避免硬编码：

public class TenantDbContext : DbContext
{
    private readonly string _tenantSchema;

    public TenantDbContext(DbContextOptions<TenantDbContext> options, string tenantSchema)
        : base(options)
    {
        _tenantSchema = tenantSchema;
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // 为所有实体指定动态Schema
        modelBuilder.HasDefaultSchema(_tenantSchema);
        // 其他实体配置逻辑...
    }
}

2. 自定义迁移生成器替换Schema占位符 #

继承NpgsqlMigrationsSqlGenerator，重写涉及Schema的操作生成逻辑，将临时Schema替换为统一占位符：

public class TenantMigrationsSqlGenerator : NpgsqlMigrationsSqlGenerator
{
    private const string TempSchema = "__TEMP_TENANT_SCHEMA__";
    private const string Placeholder = "{TenantSchema}";

    public TenantMigrationsSqlGenerator(MigrationsSqlGeneratorDependencies dependencies, INpgsqlOptions npgsqlOptions)
        : base(dependencies, npgsqlOptions)
    {
    }

    // 处理Schema创建操作
    protected override void Generate(CreateSchemaOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        var originalSchema = operation.Name;
        operation.Name = TempSchema;
        base.Generate(operation, model, builder);
        operation.Name = originalSchema;

        var command = builder.GetLastCommand();
        command.CommandText = command.CommandText.Replace(TempSchema, Placeholder);
    }

    // 处理表创建操作
    protected override void Generate(CreateTableOperation operation, IModel model, MigrationCommandListBuilder builder)
    {
        var originalSchema = operation.Schema;
        operation.Schema = TempSchema;
        base.Generate(operation, model, builder);
        operation.Schema = originalSchema;

        var command = builder.GetLastCommand();
        command.CommandText = command.CommandText.Replace(TempSchema, Placeholder);
    }

    // 按需重写其他涉及Schema的操作，比如AddForeignKey、CreateIndex等
}

3. 注册自定义迁移生成器 #

在设计时DbContext工厂中替换默认的迁移生成器服务：

public class DesignTimeTenantDbContextFactory : IDesignTimeDbContextFactory<TenantDbContext>
{
    public TenantDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<TenantDbContext>();
        optionsBuilder.UseNpgsql("Host=localhost;Database=multi_tenant_db;Username=postgres;Password=postgres")
            .ReplaceService<IMigrationsSqlGenerator, TenantMigrationsSqlGenerator>();

        // 设计时传入临时Schema，后续会被替换为占位符
        return new TenantDbContext(optionsBuilder.Options, "__TEMP_TENANT_SCHEMA__");
    }
}

4. 生成通用迁移脚本 #

执行EF Core命令生成迁移和脚本：

dotnet ef migrations add InitialTenantSchema
dotnet ef migrations script -o tenant_migration.sql

生成的脚本中所有Schema引用都会被替换为{TenantSchema}占位符。

5. 执行迁移时注入租户Schema #

编写执行逻辑，读取通用脚本并替换占位符为具体租户Schema后执行：

public async Task ApplyTenantMigration(string connectionString, string tenantSchema)
{
    var baseScript = await File.ReadAllTextAsync("tenant_migration.sql");
    var tenantScript = baseScript.Replace("{TenantSchema}", tenantSchema);

    await using var conn = new NpgsqlConnection(connectionString);
    await conn.OpenAsync();
    await using var cmd = new NpgsqlCommand(tenantScript, conn);
    await cmd.ExecuteNonQueryAsync();
}

注意事项 #

• 需覆盖所有涉及Schema的迁移操作生成逻辑，避免遗漏硬编码的Schema名称
• 此方案属于EF Core迁移系统的自定义扩展，需自行维护版本兼容性
• 后续新增迁移时，重复步骤4、5即可，无需为每个租户单独创建迁移

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