I came across a problem with EnsureCreated() method that broke all the SQL migrations on start up.
I have some queries that I wanted to run before the migrations. I thought I should make sure the database exists before I run them. I saw the method DatabaseFacade. EnsureCreated() and decided to use it.
This method will do nothing if the database for the context exists. It will create the database and all the schema if it doesn’t exist. Sounds promising. When testing this on an existing database, it works perfectly. However, when tested on a context without the database, it fails.
This fails because EnsureCreated() creates all the schema without migrations and migration history table. This means once the database is created by EnsureCreated() method, running Database.Migrate() will start with the first migration and will attempt to create all the schema that already existed, causing a conflict. After further investigation, I found this from Microsoft docs.
Note that this API does not use migrations to create the database. In addition, the database that is created cannot be later updated using migrations.
This means you have to choose between EnsureCreated() and Migrate() but not both. I also found that EnsureCreated() is designed for testing or rapid prototyping where dropping and re-creating the database every time is ok.
For my solution, I ended up removing EnsureCreated() and run my queries after the Migrations. It was not really a good idea to run before the migrations.