Entity Framework Core Migrations error – Database already exists – How to fix How to fix the Database already Exists (or Table, or Relation) error that might occur when using Database.Migrate() method by conditionally calling it

BuildWebHost - Unable to create an object of type ApplicationDbContext error in EF Core 2.x - How to Fix

Today I was working on my TestMakerFree Angular app, which I used as a sample app for my ASP.NET Core 2 and Angular 5 book: specifically, I was looking for a way to programmatically check if the SQL database existed or not. The reason of that is simple: the DbContext.Database.Migrate() method, which can be used in an ASP.NET Core application (when using EF Core with migrations) to ensure the database is created and all migrations are applied, will throw an exception when used against a Database which is not identical to the one it created and migrated from the very beginning.

Here’s a typical implementation of the DbContext.Database.Migrate() method in an ASP.NET Core application:

This piece of code is usually put inside the Main() method of the Program.cs file and will ensure that the DB is not only present, but also up-to-date and perfectly matching the migrations present in the actual code.

The issue

Although this is indeed a good thing, that method might either pose performance issues and/or raise unexpected exceptions in our code. The former will occur whenever there aren’t new migrations to apply, while the latter will be thrown whenever the Database – despite being up-to-date – has been created using different migrations, such as from a different development machine: as a matter of fact, the Migrate() method will fail (and throw a SqlException) whenever it doesn’t find all the expected migrations in the __EFMigrationsHistory SQL table.

Here’s a typical SqlException that you would see while attempting to do that:

Application startup exception: System.Data.SqlClient.SqlException (0x80131904): Database ‘MyDatabaseName’ already exists. Choose a different database name.

The fix

Before start talking about the fix, there’s an important thing that we need to understand: the migration pattern is an excellent way to ensure that all the Databases we’re working on (and we’ll use to connect our app with) will have a consistent and up-to-date structure in any given environment – testing, stage, production, DR and so on; if we choose to use it, the best thing we can do is to stick to the pattern best practices and ensure that the Migrate() method is called whenever we need to.

That said, if you want to use the Migrate() method solely to create your database on the first run and you don’t need to programmatically (and automatically) keep track of any further migration, the best thing you can do is to wrap the Migrate() method inside a conditional block:

That way you will ensure that the Migrate() method will be programmatically executed only if the Database doesn’t exists yet: that would be perfect for testing environments, where you can just drop and recreate the Database everytime without having to worry about losing actual data, and/or for any other context where you prefer to manually update your Database – for example using the dotnet ef powershell command. That’s good for performance and also to avoid the SqlException from the Migrate() method, as it will only run whenever there is no Database to begin with, thus preventing the chance of finding wrong or outdated migrations data.

If you’re curious about what the Exists() method actually does behind the curtains, you can easily check it out by looking at the in the SqlServer.Storage/Internal/SqlServerDatabaseCreator.cs class within the EF Core’s official GitHub repository: you will see that there’s no actual magic – just an attempt to open the connection and either catch the SqlException and return false or return true. Probably not the best thing you can hope to find there, yet still better than nothing (at least it does the job).

If you want to know more about .NET Core and Angular check out the ASP.NET Core 2 and Angular 5 book, available in paperback and/or digital format. Promo Code: ASPCA50 to get it with a 50% discount!

 

About Ryan

IT Project Manager, Web Interface Architect and Lead Developer for many high-traffic web sites & services hosted in Italy and Europe. Since 2010 it's also a lead designer for many App and games for Android, iOS and Windows Phone mobile devices for a number of italian companies. Microsoft MVP for Development Technologies since 2018.

View all posts by Ryan

1 Comment on “Entity Framework Core Migrations error – Database already exists – How to fix How to fix the Database already Exists (or Table, or Relation) error that might occur when using Database.Migrate() method by conditionally calling it

  1. Hello! I’m starting to work with EF Core Migrations.

    I already had the database modelled in MySQL so I did the reverse engineering scaffolding to create all Model classes from database tables. Until there everything was fine.

    Once all the classes were created I tried to create my first migration so I ran:

    “dotnet ef migrations add FirstMigration”

    and then updated the database:

    “dotnet ef database update”

    But an error occurred:
    Table ‘database.__EFMigrationsHistory’ doesn’t exist.

    To handle this error I created the table manually:
    CREATE TABLE (
    VARCHAR(150) NOT NULL,
    VARCHAR(32) NOT NULL,
    PRIMARY KEY( )
    );

    Finally, I tried again to update the database but an error is thrown alerting that one table already exists. Obviously already exists, the model class was created from the table with reverse engineering.

    So I’d like to know what I could to avoid this kind of error.

    Thanks in advance!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.