EF Core multitenancy, different schema. EF Core does not change the table schema when the API is at runtime

I'm developing a multi-tenant ASP.NET Core Web API project and I use EF Core 8 as my ORM and SQL Server. The project supports several tenants and I need to create the databases at run time and change the schema from dbo to "the Brazilian cpf document number".

I already have a migration ready. When the project is running and I run Migrate(), it creates the database correctly, but does not change the schema. EF Core insists on maintaining dbo for the tables and does not obey the parameters that I inform.

Does anyone have any idea what it is? I've tried everything and I've been at it for over 5 months with no solution.

//Apply Migrations
public static void Test(this IServiceProvider serviceProvider, IConfiguration configuration, TenantRequestViewModel tenantRequestViewModel)
{
    using (var scope = serviceProvider.CreateScope())
    {
        var currentTenantRepo = scope.ServiceProvider.GetRequiredService<ICurrentTenantRepository>();

        if (!currentTenantRepo.SetTenantAsync(tenantRequestViewModel.DocumentNumber).Result) { { } }

        string dbName = DataBaseConst.DATABASE_NAME_ERP_VET + "-" + tenantRequestViewModel.DocumentNumber;
        string defaultConnectionString = configuration.GetConnectionString("DefaultConnection");
        string connectionString = currentTenantRepo.ConnectionString ?? defaultConnectionString.Replace(DataBaseConst.DATABASE_NAME_ERP_VET, dbName);

        var appDbContextOptionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        appDbContextOptionsBuilder.UseSqlServer(connectionString);

        var mediatorHandler = scope.ServiceProvider.GetRequiredService<IMediatorHandler>();

        using (var appDbContext = new AppDbContext(appDbContextOptionsBuilder.Options, currentTenantRepo, mediatorHandler, currentTenantRepo.Schema))
        {
            Console.WriteLine($"Current Schema: {currentTenantRepo.Schema}");

            if (appDbContext.Database.GetPendingMigrations().Any())
            {
                appDbContext.Database.Migrate();
            }
        }       
    }
}

// My Context
public sealed class AppDbContext : DbContext, IUnitOfWork
{
    private readonly IMediatorHandler _mediatorHandler;

    private readonly ICurrentTenantRepository _currentTenantRepository;
    public Guid CurrentTenantId { get; set; }
    public string? CurrentTenantConnectionString { get; set; }
    public string? CurrentSchema { get; set; }

    public AppDbContext(DbContextOptions<AppDbContext> options, ICurrentTenantRepository currentTenantRepository, IMediatorHandler mediatorHandler, string? schema = null) : base(options)
    {
        _mediatorHandler = mediatorHandler;
        _currentTenantRepository = currentTenantRepository;

        if (_currentTenantRepository is not null)
        {
            CurrentTenantId = _currentTenantRepository.TenantId;
            CurrentTenantConnectionString = _currentTenantRepository.ConnectionString;
            CurrentSchema = string.IsNullOrEmpty(schema) ? _currentTenantRepository.Schema : schema ;
        }

        //ChangeTracker.QueryTrackingBehavior = QueryTrackingBehavior.NoTracking;
        //ChangeTracker.AutoDetectChangesEnabled = false;
    }

    public DbSet<Customer> Customers { get; set; }
    {
        protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Ignore<ValidationResult>();
        modelBuilder.Ignore<Event>();
        modelBuilder.Ignore<CreateTenantRequest>();
        modelBuilder.Ignore<TenantResponse>();
        modelBuilder.Ignore<Tenant>();

        modelBuilder.HasDefaultSchema(CurrentSchema);
        
        if (!string.IsNullOrEmpty(CurrentSchema))
        {
            foreach (var entityType in modelBuilder.Model.GetEntityTypes())
            {
                entityType.SetSchema(CurrentSchema);
            }              
        }           

        foreach (var property in modelBuilder.Model.GetEntityTypes().SelectMany(
            e => e.GetProperties().Where(p => p.ClrType == typeof(string))))
            property.SetColumnType("varchar(100)");
            
        //Schema name passed to table configuration
        modelBuilder.ApplyConfiguration(new CustomerMap(CurrentSchema));

        base.OnModelCreating(modelBuilder);
    }

    // On Configuring -- dynamic connection string, fires on every request
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        string? tenantConnectionString = CurrentTenantConnectionString;

        if (!string.IsNullOrEmpty(tenantConnectionString)) // use tenant db if one is specified
        {
            _ = optionsBuilder.UseSqlServer(tenantConnectionString);
        }

        optionsBuilder.ReplaceService<IModelCacheKeyFactory, CustomModelCacheKeyFactory>();

        base.OnConfiguring(optionsBuilder);
    }
}

// CustomModelCacheKeyFactory
public class CustomModelCacheKeyFactory : IModelCacheKeyFactory
{
    public object Create(DbContext context, bool designTime)
    {
        var contextWithSchema = context as AppDbContext;
        // O TenantId ou schema específico pode ser utilizado como chave, junto com o designTime.
        return (contextWithSchema?.CurrentSchema, context.GetType(), designTime);
    }
}

{
  "documentNumber": "03350593097",
  "workspaceName": "My_Work_1", 
  "phoneNumber": "27996356704", 
  "email": "jalberromano@msn.com",
  "password": "123456",
  "confirmPassword": "123456",  
  "subscriptionPlanId": "0335a5fe-4754-4ed0-1434-08dd1937d5aa",//after starting the API get the ID from the table
  "cultureCode": "pt-BR",
  "isolated": true
}

The project is big and I can't post the entire source here. You can download it.

I have created a small console application demonstrating how to configure EF Core to generate migrations for different schemas while still leveraging the default schema's migration generation. The classes are organized logically, and their dependencies will be explained later.

Program.cs Example: Combining Everything

internal class Program
{
    static void Main(string[] args)
    {
        var connectionString = "Server=localhost,1419;Database=SOMultitenancy;User Id=sa;Password=Password12!;Encrypt=true;TrustServerCertificate=true";

        using var context1 = CreateTenantContext(connectionString, "tenant1");
        using var context2 = CreateTenantContext(connectionString, "tenant2");
        using var context3 = CreateTenantContext(connectionString, "tenant3");
        
        context1.Database.Migrate();
        context2.Database.Migrate();  
        context3.Database.Migrate();

        // At this point we have all tenants configured and all tables are created

        var o1 = context1.Orders.ToArray();
        var o2 = context2.Orders.ToArray();
        var o3 = context3.Orders.ToArray();
    }

    private static AppDbContext CreateTenantContext(string connectionString, string? tenantName)
    {
        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder
            .UseSqlServerMultiTenant(connectionString, tenantName, AppDbContextModelConfiguration.BuildModel)
            .LogTo(s => Console.WriteLine(s));

        var context = new AppDbContext(optionsBuilder.Options);
        return context;
    }
}

Define test context

public class AppDbContext : DbContext
{
    public DbSet<Customer> Customers { get; set; }
    public DbSet<Order> Orders { get; set; }


    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }
}

public class Customer
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<Order> Orders { get; set; }
}

public class Order
{
    public int Id { get; set; }
    public DateTime OrderDate { get; set; }
    public decimal Amount { get; set; }
    public int CustomerId { get; set; }
    public Customer Customer { get; set; }
}

Define AppDbContextFactory which helps to generate migrations for Console applications.

DON'T forget to install package Microsoft.EntityFrameworkCore.Tools

public class AppDbContextFactory : IDesignTimeDbContextFactory<AppDbContext>
{
    /// <summary>
    /// Creates a new instance of AppDbContext for design-time tools like migrations.
    /// </summary>
    /// <param name="args">Command-line arguments.</param>
    /// <returns>An instance of AppDbContext.</returns>
    public AppDbContext CreateDbContext(string[] args)
    {
        // Define the connection string
        var connectionString = "Server=localhost,1419;Database=SOMultitenancy;User Id=sa;Password=Password12!;Encrypt=true;TrustServerCertificate=true";

        // Configure DbContextOptions
        var optionsBuilder = new DbContextOptionsBuilder<AppDbContext>();
        optionsBuilder
            .UseSqlServerMultiTenant(connectionString, null, AppDbContextModelConfiguration.BuildModel);

        return new AppDbContext(optionsBuilder.Options);
    }
}

Model Configuration

You should move model configuring into this class

public static class AppDbContextModelConfiguration
{
    public static readonly ConcurrentDictionary<string, IModel> ModelCache = new();

    public static ModelBuilder CreateModelBuilder()
    {
        // Create the ModelBuilder with the SQL Server conventions
        var modelBuilder = new ModelBuilder(ModelBuildingHelper.GetSqlServerConventionSet());

        return modelBuilder;
    }

    public static IModel BuildModel(string? schemaName = default)
    {
        schemaName ??= string.Empty;

        if (ModelCache.TryGetValue(schemaName, out var model))
            return model;

        var modelBuilder = CreateModelBuilder();

        if (!string.IsNullOrEmpty(schemaName))
            modelBuilder.HasDefaultSchema(schemaName);

        ConfigureModel(modelBuilder);
        model = modelBuilder.FinalizeModel();

        ModelCache.TryAdd(schemaName, model);

        return model;
    }

    private static void ConfigureModel(ModelBuilder modelBuilder)
    {
        // Configure Customer entity
        modelBuilder.Entity<Customer>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.Name).IsRequired().HasMaxLength(100);
            entity.HasMany(e => e.Orders)
                .WithOne(o => o.Customer)
                .HasForeignKey(o => o.CustomerId);
        });

        // Configure Order entity
        modelBuilder.Entity<Order>(entity =>
        {
            entity.HasKey(e => e.Id);
            entity.Property(e => e.OrderDate).IsRequired();
            entity.Property(e => e.Amount).HasColumnType("decimal(18,2)");
        });
    }
}

Helper for SQL Server Conventions

public static class ModelBuildingHelper
{
    private static readonly object _lock = new object();
    private static ConventionSet? _sqlServerConventionSet = null;

    public static ConventionSet GetSqlServerConventionSet()
    {
        if (_sqlServerConventionSet == null)
        {
            lock (_lock)
            {
                if (_sqlServerConventionSet == null)
                {
                    var serviceProvider = new ServiceCollection()
                        .AddSqlServer<DbContext>("no-connection")
                        .BuildServiceProvider();
                    var context = serviceProvider
                        .GetRequiredService<DbContext>();
                    var conventionSetBuilder = context.GetInfrastructure()
                        .GetRequiredService<IProviderConventionSetBuilder>();
                    _sqlServerConventionSet = conventionSetBuilder.CreateConventionSet();
                }
            }
        }

        return _sqlServerConventionSet;
    }
}

Multi-Tenancy Configuration Extension

public static class MultiTenancyDbContextOptionsExtensions
{
    public static DbContextOptionsBuilder UseSqlServerMultiTenant(this DbContextOptionsBuilder optionsBuilder, string connectionString, string? schemaName, Func<string?, IModel> buildModel)
    {
        optionsBuilder
            .UseSqlServer(connectionString, o => o.MigrationsHistoryTable("__EFMigrationsHistory", !string.IsNullOrEmpty(schemaName) ? schemaName : null))
            .UseModel(buildModel(schemaName))
            .ReplaceService<IMigrationsSqlGenerator, MultitenancySqlServerMigrationsSqlGenerator>();

        // Ignore pending model changes warning
        if (!string.IsNullOrEmpty(schemaName))
            optionsBuilder.ConfigureWarnings(w => w.Ignore(RelationalEventId.PendingModelChangesWarning));

        return optionsBuilder;
    }
}

Overriding Migrations for Schema-Specific Operations

public class MultitenancySqlServerMigrationsSqlGenerator(
    MigrationsSqlGeneratorDependencies dependencies,
    ICommandBatchPreparer commandBatchPreparer)
    : SqlServerMigrationsSqlGenerator(dependencies, commandBatchPreparer)
{

    public override IReadOnlyList<MigrationCommand> Generate(
        IReadOnlyList<MigrationOperation> operations,
        IModel? model = null,
        MigrationsSqlGenerationOptions options = MigrationsSqlGenerationOptions.Default)
    {
        return base.Generate(RewriteOperations(operations, model, options), model, options);
    }

    private IReadOnlyList<MigrationOperation> RewriteOperations(
        IReadOnlyList<MigrationOperation> migrationOperations,
        IModel? model,
        MigrationsSqlGenerationOptions options)
    {
        string? defaultSchema = null;

        bool IsDefaultSchema(string? schemaName)
        {
            return schemaName == null || schemaName == defaultSchema;
        }

        var schema = Dependencies.CurrentContext.Context.Model.GetDefaultSchema();

        if (schema == null || schema == defaultSchema)
            return migrationOperations;

        foreach (var operation in migrationOperations)
        {
            switch (operation)
            {
                case CreateTableOperation createTableOperation:
                    if (IsDefaultSchema(createTableOperation.Schema))
                    {
                        createTableOperation.Schema = schema;

                        foreach (var ck in createTableOperation.CheckConstraints.Where(ck => IsDefaultSchema(ck.Schema)))
                        {
                            ck.Schema = schema;
                        }

                        foreach (var uk in createTableOperation.UniqueConstraints.Where(uk => IsDefaultSchema(uk.Schema)))
                        {
                            uk.Schema = schema;
                        }

                        foreach (var fk in createTableOperation.ForeignKeys)
                        {
                            if (IsDefaultSchema(fk.Schema))
                            {
                                fk.Schema = schema;
                                fk.PrincipalSchema = schema;
                            }
                        }
                    }
                    break;
                case TableOperation tableOperation:
                    if (IsDefaultSchema(tableOperation.Schema)) 
                        tableOperation.Schema = schema;
                    break;
                case DropTableOperation dropTableOperation:
                    if (IsDefaultSchema(dropTableOperation.Schema)) 
                        dropTableOperation.Schema = schema;
                    break;
                case RenameTableOperation renameTableOperation:
                    if (IsDefaultSchema(renameTableOperation.Schema)) 
                        renameTableOperation.Schema = schema;
                    break;
                case ColumnOperation columnOperation:
                    if (IsDefaultSchema(columnOperation.Schema)) 
                        columnOperation.Schema = schema;
                    break;
                case CreateIndexOperation createIndexOperation:
                    if (IsDefaultSchema(createIndexOperation.Schema)) 
                        createIndexOperation.Schema = schema;
                    break;
                case DropIndexOperation dropIndexOperation:
                    if (IsDefaultSchema(dropIndexOperation.Schema))
                        dropIndexOperation.Schema = schema;
                    break;
                case EnsureSchemaOperation ensureSchemaOperation:
                    if (IsDefaultSchema(ensureSchemaOperation.Name)) 
                        ensureSchemaOperation.Name = schema;
                    break;
                default:
                    //TODO: Add more operations
                    break;
            }
        }

        return migrationOperations;
    }
}

Final Notes

Once everything is correctly set up, you can generate migrations for the default schema and reuse them for each tenant's schema dynamically. This approach ensures that each tenant has its own migration history table and isolated schema configuration.