Database.EnsureCreated() force create multiple schemas

[aleksvujic]

We are using .NET Core 3.1, Microsoft.EntityFrameworkCore v3.1.9 and Npgsql.EntityFrameworkCore.PostgreSQL v3.1.4. We have 2 database schemas and corresponding DbContext files:

schema	DbContext
test1	Test1DbContext.cs
test2	Test2DbContext.cs

Test1DbContext.cs:

public partial class Test1DbContext : DbContext
{
	public virtual DbSet<AppModules> AppModules { get; set; }
	public virtual DbSet<AppOffice> AppOffice { get; set; }
	public virtual DbSet<AppRoles> AppRoles { get; set; }
}

Test2DbContext.cs:

public partial class Test2DbContext : DbContext
{
	public virtual DbSet<CacuInter> CacuInter { get; set; }
	public virtual DbSet<Caseevents> Caseevents { get; set; }
	public virtual DbSet<Caseftpses> Caseftpses { get; set; }
}

Database models are defined like this:

[Table("app_modules", Schema = "test1")]
public partial class AppModules
{
	[Key]
	[Column("id")]
	public int Id { get; set; }
	
	[Required]
	[Column("name")]
	[StringLength(25)]
	public string Name { get; set; }
}

When we start integration tests, we want to delete the database and recreate both schemas (test1 and test2). This is the code snippet that we have currently:

private void PrepareDatabase(Test1DbContext test1DbContext, Test2DbContext test2DbContext)
{
	// this drops the *whole* database, not just the `test1` schema
	test1DbContext.Database.EnsureDeleted();
	
	// create `test1` schema
	test1DbContext.Database.EnsureCreated();
	
	// nothing happens here because EF Core thinks that the database has already been created
	test2DbContext.Database.EnsureCreated();
}

We start with test1DbContext.Database.EnsureDeleted() which drops the whole database. Then, we create schema test1 with test1DbContext.Database.EnsureCreated(). At the end, we also want to create schema test2 with test2DbContext.Database.EnsureCreated(), but nothing happens because EF Core thinks that the database has already been created.

How can I force EF Core to create both schemas?

[roji]

Cross-posted as https://stackoverflow.com/questions/73774260/database-ensurecreated-force-create-multiple-schemas

[ajcvickers]

@aleksvujic context.GetService<IRelationalDatabaseCreator>().CreateTables() should do what you want.

[aleksvujic]

@ajcvickers Some tables from schema test1 reference tables from schema test2 with foreign keys. When I call test1DbContext.GetService<IRelationalDatabaseCreator>().CreateTables(), it also creates referenced tables from schema test2. When test2DbContext.GetService<IRelationalDatabaseCreator>().CreateTables() is called, it throws the following exception for the tables that were already created by previous call:

Npgsql.PostgresException: '42P07: relation "<xxx>" already exists'

This is the code that I have currently. Pay attention to the comments.

private void PrepareDatabase(Test1DbContext test1DbContext, Test2DbContext test2DbContext)
{
	// this drops the *whole* database, not just the `test1` schema
	test1DbContext.Database.EnsureDeleted();
	
	// create `test1` schema and related tables from schema `test2`
	test1DbContext.Database.EnsureCreated();
	
	// try to create tables from `test2` schema - exception: some of them already exist,
	// they were created with test1DbContext.Database.EnsureCreated()
	test2DbContext.GetService<IRelationalDatabaseCreator>().CreateTables();
}

What can I do if tables from one schema (test1 in my case) reference the tables from another one (test2 in my case)?

[ajcvickers]

@aleksvujic The only thing we have that can handle that is Migrations.

[aleksvujic]

@ajcvickers Can you please elaborate on your answer or possibly provide a code example of how we can achieve what we would like?

[ajcvickers]

@aleksvujic I don't think there is anything in EF Core that will do what you want. What I am saying is that generating migrations which can be configured to not include certain tables and edited as needed, and then applied separately for each context, would work. It might be possible to generate the migrations and then apply them at runtime with Database.Migrate. But there is nothing that I am aware of that will do this without generating migrations first.