Have migrations and a service that needs the database?

Question

Have migrations and a service that needs the database?

asp.net-core c# entity-framework entity-framework-core

Question

How do I build a service that needs access to my database while also using migrations, given that the migrations tool "dotnet ef database update" runs my application BuildWebHost before creating the database? The attempt to configure the service needing the database throws an exception because the database doesn't exist and causes the migration command to fail. The database therefore never gets created.

I'm using asp.net core 2 and EF Core 2.

More specifically, running "dotnet ef database update" with a blank database fails with the following error:

An error occurred while calling method 'BuildWebHost' on class 'Program'.Continuing without the application service provider. Error: Cannot open database "MyDb" requested by the login. The login failed. Login failed for user 'MYCOMPUTER\MYNAME'.

This happens because I have built a custom configuration provider backed by my "MyDb" database (with the end goal of binding to it with a custom options class) as per this Microsoft Configuration Tutorial My program.cs looks like:

 public static IWebHost BuildWebHost(string[] args) =>
        WebHost.CreateDefaultBuilder(args)
            .ConfigureAppConfiguration((builderContext, config) =>
                {
                    var tmpconfig = config.Build();
                    config.AddMyOptionsConfig(options => options.UseSqlServer(tmpconfig.GetConnectionString("My_Database")));
                })
            .UseStartup<Startup>()
            .Build();

and the AddMyOptionsConfig eventually runs:

  public class EFConfigProvider : ConfigurationProvider
    {
     [...]

        // Load config data from EF DB.
        public override void Load()
        {
            var builder = new DbContextOptionsBuilder<MyDbContext>();
            OptionsAction(builder);

            using (var dbContext = new MyDbContext(builder.Options))
            {    
                 // dbContext.Database.EnsureCreated(); // will cause first migration to fail
                 Data = !dbContext.ConfigurationValue.Any() // throws exception
                      ? CreateAndSaveDefaultValues(dbContext)
                      : dbContext.ConfigurationValue.ToDictionary(c => c.Id, c => c.Value);

which throws an SQLException when it attempts to access the database because the database hasn't been created yet.

However, if I try to use dbContext.Database.EnsureCreated() then the initial migration fails because the tables already exist. I thought of trying dbContext.Database.Migrate() instead but as a beginner I'm concerned there might be unintended consequences for a production environment. As such I'd prefer to have control over migrations via the command line tools.

Fundamentally, the problem seems to be that "dotnet ef database update" runs the application startup - BuildWebHost - before it creates the database but the custom configuration provider added in BuildWebHost needs the database to already exist.

How do I solve this dilemma?

pere57

Popular Answer

My solution so far has been to move the entity backing the custom configuration provider into a separate dbContext.

Now the table can be created when needed via MyDbOptionsContext.Database.EnsureCreated() without affecting the migrations, as they are based on the main context (MyDbContext).

The options table no longer participates in migrations but as it's unlikely to change there shouldn't be a problem. The other downside is the need (due to having 2 contexts) to explicitly specify the context for the dotnet ef command line tool.

pere57

View more on Stack Overflow

Chris Pratt · Accepted Answer · 11/9/2017 2:29:50 PM

In a sense, you've created a circular reference. When running migrations on a ASP.NET Core project, the application is initialized to instantiate the DbContext needed to run migrations against. This is due to the fact that DbContext in EF Core now requires a DbContextOptions instance to be injected, as opposed to the old way of doing things in EF, where the connection string name (or the actual full connection string) would be defined directly on the constructor.

Normally, this would work just fine, but, as you've noticed, because the application initialization, itself, requires an already existing database, there's no way to run this before the migrations. As a result, you have two options:

You can attempt to abstract the piece that requires an existing database. That could be as simple as wrapping it in a try-catch and swallowing the exception, or more complex. Since the migration piece doesn't need this particular functionality, it could safely be excluded from the application initialization in that scenario.
You can move your context and entities into a class library and implement an IDesignTimeDbContextFactory to satisfy the ability to construct a DbContext to migrate against. You would then run the migrations against this class library instead of your ASP.NET Core project. That then sidesteps the issue of having to initialize the application in order to do a migration.

Have migrations and a service that needs the database?

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related