Applying EF Core migrations per tenant schema

Question

I'm working with .NET Core 3.1 and EntityFramework Core 3.1.3. I'm trying to implement tenant data separation using DB schemas. I've read this. I'm aware it's a little bit outdated, so I've adjusted.

I've created an implementation of DbContext:

public class AppDataContext : DbContext
{
    private readonly ITenantProvider _tenantProvider;

    public AppDataContext(DbContextOptions<AppDataContext> options, ITenantProvider tenantProvider) : base(options)
    {
        _tenantProvider = tenantProvider;
    }

    public DbSet<Book> Books { get; set; }
    public DbSet<Comics> Comics { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // Tenant schema mapping
        var tenant = _tenantProvider.GetTenantString();
        modelBuilder.HasDefaultSchema(tenant);
    }

    public static void ApplyMigrations(string connectionString, string tenant)
    {
        var optionsBuilder = new DbContextOptionsBuilder<AppDataContext>();
        optionsBuilder.UseSqlServer(connectionString, x => x.MigrationsHistoryTable("__EFMigrationsHistory", tenant));
        var ctx = new AppDataContext(optionsBuilder.Options, StaticTenantProvider.WithTenant(tenant));

        ctx.Database.Migrate();
    }
}

ITenantProvider is a registered scoped service in .NET Core DI. This AppDataContext is registered like so:

services.AddDbContext<AppDataContext>((ctx, opt) =>
{
   opt.UseSqlServer(Configuration["SqlConnectionString"]);
});

Now, my idea would be, that anytime I want to provision another tenant, I would make call like this (database instance already exists and may have some tables/schemas already):

var connString = GetConnString();       // Where does connection string and tenant name come from is not important
var tenantName = GetTenantName();
AppDataContext.ApplyMigrations(connString, tenantName);

And after this I would have a new unique DB schema with all tables all set up for the Tenant.

Unfortunately, it's not working. Tables are still being created for default "dbo" schema (I'm using SqlServer).

I've started looking around the internet, first I've found this. It looked a bit odd for me, but tried anyway:

public static void ApplyMigrations(string connectionString, string tenant)
{
    var optionsBuilder = new DbContextOptionsBuilder<AppDataContext>();
    optionsBuilder.UseSqlServer(connectionString);
    var ctx = new AppDataContext(optionsBuilder.Options, StaticTenantProvider.WithTenant(tenant));

    var command = $"IF (NOT EXISTS (SELECT * FROM sys.schemas WHERE name = N'{tenant}')) " +
                  "BEGIN" +
                  $"  EXEC ('CREATE SCHEMA {tenant}');" +
                  "END";
    ctx.Database.ExecuteSqlRaw(command);
    ctx.Database.Migrate();
}

Didn't work. I could see that the schema got created (using SSMS), but the tables still landed in "dbo".

Then I thought, that maybe it has problems with locating the Migration History table, so I tried MigrationsHistoryTable like this:

optionsBuilder.UseSqlServer(connectionString, x => x.MigrationsHistoryTable("__EFMigrationsHistory", tenant));

But no, Migration History table did get created in new schema, but all other tables still were in "dbo".

What am I missing? Or maybe it's not possible to use EF Migrations and Schema separation at the same time?

Thanks in advance.

EDIT: To make things clear: I'm using Code First approach. There are already generated migrations for AppDataContext.

The "little" problem is that EF Core does not support automatic migrations which are essential for EF code you are linking to. In EF Core migrations are created with `Add-Migration` commands and are embedded (compiled) in the app code. The schema name is also embedded in the migration. OnModelCreating` affects only the runtime mapping or the next `Add-Migration` command, but not the existing migrations. — Ivan Stoev, May 31 '20 at 18:22
I do know how to create migration. I assumed that's understood explicite in the question. What do you mean by automatic migrations? How can schema name be embedded within migration? What would be the purpose for HasDefaultSchema() or fluent table mappings then? — Michał Górnicki, May 31 '20 at 21:33
I understand you must have read several posts in trying to achieve this. I found the following: stackoverflow.com/questions/19458943/… The romiller article you added in your post made me curious, and I will keep my eyes open. — Stephen Raman, Jun 01 '20 at 13:35
Maybe this will help: https://github.com/dotnet/efcore/issues/4004 and specifically this: https://github.com/dotnet/efcore/issues/4583 — Alex, Jun 02 '20 at 03:08
Rory, I cannot share the migrations' code. However, all the migrations were auto-generated, originally without specifying any schema name. — Michał Górnicki, Jul 03 '20 at 15:32
Thanks, Alex. Unfortunately, most of the code examples are applicable to EF Core 3.1, as a lot changed in the EF API between the versions. Do you know how to adapt these examples to work with 3.1? — Michał Górnicki, Jul 03 '20 at 15:33
@MichałGórnicki have you found a solution to this problem? Thanks in advance. — officer, Oct 28 '20 at 14:43

score 0 · Answer 1 · answered Dec 30 '21 at 07:08

You need to tell EF where to put each table by providing the schema in the configuration's IEntityTypeConfiguration.ToTable(...) overload.

First define a configuration for the entity:

public class SpecialSchemaEntityConfiguration : IEntityTypeConfiguration<SpecialSchemaEntity>
{
    private readonly string _schema;

    public SpecialSchemaEntityConfiguration(string schema) => this._schema = schema;

    public void Configure(EntityTypeBuilder<SpecialSchemaEntity> builder)
    {
        builder.ToTable("SpecialSchemaEntity", this._schema);
    }
}

Then, tell EF to use that configuration when building out the table:

public class AppDataContext : DbContext
{
    private readonly string _schema = "special_schema";

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
         modelBuilder.ApplyConfiguration(new SpecialSchemaEntityConfiguration(this._schema));
    }
}

Applying EF Core migrations per tenant schema

1 Answers1