.NET Core app with multiple database contextes does not generate tables in all DbContexts

Question

I have made an OData server with .NET core 3.1.

In this application I have 2 database contexts. One of them for the app itself (AppDbContext) and one for the account management and authentication (AccountDbContext).

I have a ServiceExtensions like this:

public static class ServiceExtensions
{
            
    public static void ConfigureMySqlContext(this IServiceCollection services, IConfiguration configuration)
    {
        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseMySql(configuration.GetConnectionString("MysqlConnection"),
                mysqlOptions => {
                    mysqlOptions.ServerVersion(new ServerVersion(new Version(5, 4, 3), ServerType.MySql));
                });
        });

        services.AddDbContext<AccountDbContext>(options =>
        {
            options.UseMySql(configuration.GetConnectionString("MysqlConnection"),
                mysqlOptions => {
                    mysqlOptions.ServerVersion(new ServerVersion(new Version(5, 4, 3), ServerType.MySql));
                });
        });

    }
        
}

And in the startup.cs I will call the ConfigureMySqlContext function as following:

public class Startup
{
        public Startup(IConfiguration configuration)
        {
            Configuration = configuration;
        }

        public IConfiguration Configuration { get; }

        // This method gets called by the runtime. 
        // I use this method to add services to the container.
        public void ConfigureServices(IServiceCollection services)
        {

            services.ConfigureMySqlContext(Configuration);
        }
}

public static class Program
{
    public static void Main(string[] args)
    {
        CreateWebHostBuilder(args)
            .Build()
            .CreateDbIfNotExists()
            .Run();
    }

    private static IHost CreateDbIfNotExists(this IHost host)
    {
        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;

            var accountDbContext = services.GetRequiredService<AccountDbContext>();
            accountDbContext.Database.EnsureCreated();

            var appDbContext = services.GetRequiredService<AppDbContext>();
            appDbContext.Database.EnsureCreated();
                
        }

        return host;
    }
}

The aim of this code is, to generate the needed tables in both contexts inside the database. The problem is, when I publish this application to the server, and when the app will run for the first time, it will only generate and initiate the tables that are in the AppDbContext. Why? Because it is placed before AccountDbContext.

If I change the ConfigureMySqlContext function as follow and publish the app again:

public static class ServiceExtensions
{
            
    public static void ConfigureMySqlContext(this IServiceCollection services, IConfiguration configuration)
    {

        services.AddDbContext<AccountDbContext>(options =>
        {
            options.UseMySql(configuration.GetConnectionString("MysqlConnection"),
                mysqlOptions => {
                    mysqlOptions.ServerVersion(new ServerVersion(new Version(5, 4, 3), ServerType.MySql));
                });
        });

        services.AddDbContext<AppDbContext>(options =>
        {
            options.UseMySql(configuration.GetConnectionString("MysqlConnection"),
                mysqlOptions => {
                    mysqlOptions.ServerVersion(new ServerVersion(new Version(5, 4, 3), ServerType.MySql));
                });
        });       
    }        
}

Then it will be the tables inside the AccountDbContext which will be initiated in the database after the first run.

Answer 1

The issue is that at it's core, EnsureCreated has very simple semantics, which can be modeled as:

if (!database.exists()) {
   database.create();
}

where exists() checks for the existance of the database, without inspecting if the tables inside the database.

The end result of it is that if the database is not completely empty, EnsureCreated() will have no effect.

There are two ways of approaching the issue:

1. Split up the database into two. This is the solution that EntityFramework likes best.

However, if it's not an option, look into:

2. Look into the IRelationalDatabaseCreator.CreateTables() method:

https://learn.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.storage.irelationaldatabasecreator?view=efcore-5.0

Here is a possible solution:

using Microsoft.EntityFrameworkCore.Storage;
using Microsoft.EntityFrameworkCore.Infrastructure;
public static class Program
{
    public static void Main(string[] args)
    {
        CreateWebHostBuilder(args)
            .Build()
            .CreateDbIfNotExists()
            .Run();
    }

    private static IHost CreateDbIfNotExists(this IHost host)
    {
        using (var scope = host.Services.CreateScope())
        {
            var services = scope.ServiceProvider;
            try
            {
                var accountDbContext = services.GetRequiredService<AccountDbContext>();
                accountDbContext.Database.EnsureCreated();
                var accountDbCreator = accountDbContext.GetService<IRelationalDatabaseCreator>();
                accountDbCreator.CreateTables();
            }
            catch (Exception e) { }

            try
            {
                var appDbContext = services.GetRequiredService<AppDbContext>();
                appDbContext.Database.EnsureCreated();
                var appDbCreator = appDbContext.GetService<IRelationalDatabaseCreator>();
                appDbCreator.CreateTables();
            }
            catch (Exception e) { }
        }

        return host;
    }
}