EF Core migrations and multi-schema database

Question

Apologies in advance for the long question :)

I'm developing a web API with postgresql as the database using EF Core 8.

For data isolation purposes I want to divide the database logically into multiple schemas.

At the moment I have 2, very basic, schemas "crm" and "ordering". Each one of them has its own DbContext.

Each DbContext has:

1. A connection string with SearchPath to specify the schema
2. A separate migration history table

I'm using code first with fluent API and the tables in each DbContext are associated with the relevant schema

There are no cross references between the tables in the DbContexts.

When I create migrations I specify the desired context using --context option, but the migration code that's created contains tables from both DbContexts covering both schemas/DbContexts.

Everything I read about EF Core says that this scenario is possible and is supported.

In my mind I expected each of them to only create migrations for the tables that are defined in the DbContext that I specified in the --context option.

For migration purposes I created an instance of IDesignTimeDbContextFactory<DbContext>, for each DbContext, as the connection string for migration is different than the development runtime connection string.

I did a lot of reading and tried various code combinations but none of them worked. I added the --verbose option to the migrations add command. I can see that it found the 2 DbContexts, as expected, and that it's only using the DbContext that I specified in the --context option, again as expected, but which is even more confusing.

Each DbContext has such a design time context factory: Note the "crm" SearchPath in the connection string and the migration history table

public class CrmDbContextFactory : IDesignTimeDbContextFactory<CrmDbContext>
{
    public CrmDbContext CreateDbContext(string[] args)
    {
        var optionsBuilder = new DbContextOptionsBuilder<CrmDbContext>();
        optionsBuilder
            .UseNpgsql(
                "Host=localhost;Port=5432;Database=myapp;Username=postgres;Password=postgres;SearchPath=crm;",
                builder => builder.MigrationsHistoryTable("__CrmMigrationsHistory", "crm"))
            .UseSnakeCaseNamingConvention();

        return new CrmDbContext(optionsBuilder.Options, new AssemblyProvider());
    }
}

The DbContext look like this:

public sealed class CrmDbContext : ApplicationDbContext
{
    public CrmDbContext(
        DbContextOptions options,
        IAssemblyProvider assemblyProvider) 
        : base(
            options, 
            assemblyProvider)
    {
    }
    
    public DbSet<UserProfileEntity> UserProfiles { get; set; }
}

And an entity type builder looks like this: Note the schema in the call to ToTable()

internal sealed class UserProfileDbConfiguration : IEntityTypeConfiguration<UserProfileEntity>
{
    public void Configure(EntityTypeBuilder<UserProfileEntity> builder)
    {
        builder.ToTable("user_profiles", "crm");

        builder.HasKey(p => p.Id);
        
        builder.Property(p => p.Id)
            .HasConversion(pId => (Guid)pId, value => (UserProfileEntityId)value);

        builder.Property(p => p.FirstName)
            .HasMaxLength(FirstName.MaxLength)
            .HasConversion(firstName => firstName.Value, value => new FirstName(value))
            .IsRequired();

        builder.Property(p => p.LastName)
            .HasMaxLength(LastName.MaxLength)
            .HasConversion(firstName => firstName.Value, value => new LastName(value))
            .IsRequired();

        // Removed other properties
        // ....
    }
}

The migration command is: Note the --context MyApp.App.Area.Crm.Data.CrmDbContext option

dotnet ef migrations add "Initial migration for crm schema"  --startup-project .\MyApp.Api\src\MyApp.Api.csproj --project .\MyApp.App\src\MyApp.App.csproj --context MyApp.App.Area.Crm.Data.CrmDbContext --output-dir .\Area\Crm\Data\Migrations --verbose

And this is the output of this command: Note the 2 Found DbContext logs and the one Using DbContext factory 'CrmDbContextFactory'. Using context 'CrmDbContext'. log

dotnet exec --depsfile C:\source\repos\MyApp\Backend\MyApp.Api\src\bin\Debug\net8.0\MyApp.Api.deps.json --additionalprobingpath C:\Users\userh\.nuget\packages --additionalprobingpath "C:\Program Files (x86)\Microsoft Visual Studio\Shared\NuGetPackages" --runtimeconfig C:\source\repos\MyApp\Backend\MyApp.Api\src\bin\Debug\net8.0\MyApp.Api.runtimeconfig.json C:\Users\userh\.dotnet\tools\.store\dotnet-ef\8.0.0\dotnet-ef\8.0.0\tools\net8.0\any\tools\netcoreapp2.0\any\ef.dll migrations add "Initial migration for crm schema" --context MyApp.App.Area.Crm.Data.CrmDbContext --output-dir .\Area\Crm\Data\Migrations --assembly C:\source\repos\MyApp\Backend\MyApp.Api\src\bin\Debug\net8.0\MyApp.App.dll --project C:\source\repos\MyApp\Backend\MyApp.App\src\MyApp.App.csproj --startup-assembly C:\source\repos\MyApp\Backend\MyApp.Api\src\bin\Debug\net8.0\MyApp.Api.dll --startup-project C:\source\repos\MyApp\Backend\MyApp.Api\src\MyApp.Api.csproj --project-dir C:\source\repos\MyApp\Backend\MyApp.App\src\ --root-namespace MyApp.App --language C# --framework net8.0 --nullable --working-dir C:\source\repos\MyApp\Backend --verbose
Using assembly 'MyApp.App'.
Using startup assembly 'MyApp.Api'.
Using application base 'C:\source\repos\MyApp\Backend\MyApp.Api\src\bin\Debug\net8.0'.
Using working directory 'C:\source\repos\MyApp\Backend\MyApp.Api\src'.
Using root namespace 'MyApp.App'.
Using project directory 'C:\source\repos\MyApp\Backend\MyApp.App\src\'.
Remaining arguments: .
Finding DbContext classes...
Finding IDesignTimeDbContextFactory implementations...
Finding application service provider in assembly 'MyApp.Api'...
Finding Microsoft.Extensions.Hosting service provider...
Using environment 'Development'.
Using application service provider from Microsoft.Extensions.Hosting.
Found DbContext 'CrmDbContext'.
Found DbContext 'OrderingDbContext'.
Finding DbContext classes in the project...
Using DbContext factory 'CrmDbContextFactory'.
Using context 'CrmDbContext'.
Finding design-time services referenced by assembly 'MyApp.Api'...
Finding design-time services referenced by assembly 'MyApp.App'...
No referenced design-time services were found.
Finding design-time services for provider 'Npgsql.EntityFrameworkCore.PostgreSQL'...
Using design-time services from provider 'Npgsql.EntityFrameworkCore.PostgreSQL'.
Finding IDesignTimeServices implementations in assembly 'MyApp.Api'...
No design-time services were found.
Writing migration to 'C:\source\repos\MyApp\Backend\MyApp.App\src\Area\Crm\Data\Migrations\20240104194613_Initial migration for crm schema.cs'.
Writing model snapshot to 'C:\source\repos\MyApp\Backend\MyApp.App\src\Area\Crm\Data\Migrations\CrmDbContextModelSnapshot.cs'.
'CrmDbContext' disposed.
Done. To undo this action, use 'ef migrations remove'

Yet the migration looks like this: Note the 2 schemas and the table creation from both schemas crm.user_profiles and ordering.appointments

        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.EnsureSchema(
                name: "ordering");

            migrationBuilder.EnsureSchema(
                name: "crm");

            migrationBuilder.CreateTable(
                name: "appointments",
                schema: "ordering",
                columns: table => new
                {
                    id = table.Column<Guid>(type: "uuid", nullable: false),
                    client_entity_id = table.Column<Guid>(type: "uuid", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("pk_appointments", x => x.id);
                });

            migrationBuilder.CreateTable(
                name: "user_profiles",
                schema: "crm",
                columns: table => new
                {
                    id = table.Column<Guid>(type: "uuid", nullable: false),
                    first_name = table.Column<string>(type: "character varying(50)", maxLength: 50, nullable: false),
                    last_name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
                },
                constraints: table =>
                {
                    table.PrimaryKey("pk_user_profiles", x => x.id);
                });
        }

I'm scratching my head and can't see what I'm missing. Most of the questions I found about schemas are for multi-tenancy, where this isn't an issue.

My goal is to be able to manage each of the schemas separately in their own migrations

Answer 1

The answer should be in the OnModelCreating override of your custom base ApplicationDbContext class, which unfortunately you haven't shown, so I have to make guesses, but most likely it is using ApplyConfigurationsFromAssembly method or similar, which registers all entity types for the specified context. Note that having / not having DbSet in the context (they are optional anyway) does not mean the context does / does not include entity.

As explained in Including types in the model:

By convention, types that are exposed in DbSet properties on your context are included in the model as entities. Entity types that are specified in the OnModelCreating method are also included, as are any types that are found by recursively exploring the navigation properties of other discovered entity types.

Here "Entity types that are specified in the OnModelCreating method" means any entity type passed as generic type argument to modelBuilder.Entity<TEntity>() method, or generic type argument of IEntityTypeConfiguration<TEntity> implementing class passed to modelBuilder.ApplyConfiguration. Or from modelBuilder.ApplyConfigurationsFromAssembly which calls the latter for each type configuration class in the assembly, which I guess is your case.

So to solve the issue, use optional filter argument of ApplyConfigurationsFromAssembly to select only entity types applicable for the context being configured. Or remove base class code and explicitly use ApplyConfiguration calls in each derived context OnModelCreating override.

Also look carefully at navigation properties of the model classes for cross referencing entities from the other context(s) as they will be automatically included in the model and migrations, potentially without taking in account their type configuration so at the end you would have wrong model. And model is what controls EF query and other operations behaviors at runtime, not the actual database schema.

In general this the main drawback of "bounded context" concept from DDD. The model classes in a context must really be separate with no references to entity classes outside the context. Reference means both single reference like public Blog Blog { get; set; } and element of a collection like public ICollection<Post> Posts { get; set; } - in both cases, Blog and Post will be included in the model of the same context as the containing entity. All that recursively.