Efcore: create migration for several provider

[ajcvickers]

Moved from discussion #22813.

From @pas059

Hello,

My app supports three providers (PostgreSQL, SQLite and SQL Server). For each provider, there is a corresponding DbContext class which inherits from a common class erited from DbContext. Each specialized DbContext class override OnConfiguring() and OnModelCreating().

AppDbContextNpgSQL---|-------> MyDbContext --> DbContext
AppDbContextSQLite-----|
AppDbContextSqlServer--|

In order to create migration, i installed the package 'Microsoft.EntityFrameworkCore.Tools'.
Now for creating the "initial migration", i tried this for the first provider:

PM> Add-Migration InitialCreate -Context AppDbContextSQLite -OutputDir Migrations\SqliteMigrations
Build started...
Build failed.

As you see, this does not work. Someone can explain what is the right way to create migrations in this situation?

I Use VS2019CE 16.7.4 + EFCore 3.1.8 + .net 4.7.2.

Regards
Pascal

[smitpatel]

Please see https://docs.microsoft.com/ef/core/managing-schemas/migrations/providers

[pas059]

Hi,
thanks, but of course i had read this before begin.
I had thought of using one mgration by provider. I do what is discribed in the documentation.
But the 1st Add-Migration fails without giving any reasons, difficult to go further in these conditions...
any ideas?
regards

[smitpatel]

Does your project build in Visual Studio?
Run Add-Migration with verbose flag and share output with us.

[pas059]

Hello,
Yes i use VS2019CE and i found the solution. In fact, reading the content of the above link is not sufficient. The other important documentation can be found following this link:
https://docs.microsoft.com/fr-fr/ef/core/miscellaneous/cli/dbcontext-creation
Perhaps Microsoft can include a link to this doc in the chapters dedicated to the migrations :smile:.
So following this doc, i created one class by provider that implements the IDesignTimeDbContextFactory interface. For instance, for SQL Server this gives:

    public class AppDbContextSqlServerFactory : IDesignTimeDbContextFactory<AppDbContextSqlServer>
    {
        public AppDbContextSqlServer CreateDbContext( string[] args )
        {
            var optionsBuilder = new DbContextOptionsBuilder<AppDbContextSqlServer>();
            optionsBuilder.UseSqlServer( "Data Source=(local);Initial Catalog=biodata;Integrated Security=True;User Instance=False" );

            return new AppDbContextSqlServer( optionsBuilder.Options );
        }

    }

And the SQL Server DbContext class has to contain an adequate constructor, for instance for SQL Server this gives:
public AppDbContextSqlServer( DbContextOptions<AppDbContextSqlServer> options ) : base( options ) { }
and the MyDbContext class must also contain a constructor like:
public MyDbContext( DbContextOptions options ) : base( options ) { }

then one 'Add-Migration InitialCreate........' command by provider (like the one above) can be executed. In my case, 3 migration set have been generated. I hope this works because this generates thousands lines of code and i dont want to debug them :smile:.

So now, if i well understand, i just have to call 'dbContext.Database.Migrate()' in place of 'dbContext.Database.EnsureCreated()' to create a database with the current migration. Exact?

regards
Pascal

[Zero3]

@pas059 Regarding this:

PM> Add-Migration InitialCreate -Context AppDbContextSQLite -OutputDir Migrations\SqliteMigrations Build started... Build failed.

Try adding -Verbose and see if that shows you the actual error.

Anyway, your last post about multiple IDesignTimeDbContextFactory<> sounds really interesting. Did you manage to make it work? I will try something similar, as I also have a need to support two different database providers.

So now, if i well understand, i just have to call 'dbContext.Database.Migrate()'

Hopefully that will work, but surely you must use a context of the correct type for the database you are trying to migrate.

[pas059]

Hi,
So i tried with the 3 providers, and this works fine. Then, for testing purpose, i modified simply my model by just adding an Entity and a relation 1-* to other entity. Then execute 'AddMigration migrationName -Context ....' like for the InitialCreate (one by provider) and this works; a database is modified as expected when calling 'dbContext.Database.Migrate()' 😄.
Sometimes i tend to be grumpy :grimacing:, but here i have only one word to say, congratulations EFc team :smile:, I imagine that behind this migration tools there is a lot of work done.

regards, Pascal