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?
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.
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.