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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
var host = BuildWebHost(args); using (var scope = host.Services.CreateScope()) { var dbContext = scope.ServiceProvider.GetService<ApplicationDbContext>(); var roleManager = scope.ServiceProvider.GetService<RoleManager<IdentityRole>>(); var userManager = scope.ServiceProvider.GetService<UserManager<ApplicationUser>>(); // Create the Database if it doesn't exist and applies any pending migration. dbContext.Database.Migrate(); // Seed the Database DbSeeder.Seed(dbContext, roleManager, userManager); } host.Run(); |
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
if (!dbContext.Database.GetService<IRelationalDatabaseCreator>().Exists()) { var host = BuildWebHost(args); using (var scope = host.Services.CreateScope()) { var dbContext = scope.ServiceProvider.GetService<ApplicationDbContext>(); var roleManager = scope.ServiceProvider.GetService<RoleManager<IdentityRole>>(); var userManager = scope.ServiceProvider.GetService<UserManager<ApplicationUser>>(); // Create the Db if it doesn't exist and applies any pending migration. dbContext.Database.Migrate(); // Seed the Db DbSeeder.Seed(dbContext, roleManager, userManager); } host.Run(); } |
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).
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
__EFMigrationsHistory(MigrationIdVARCHAR(150) NOT NULL,ProductVersionVARCHAR(32) NOT NULL,PRIMARY KEY(
MigrationId));
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!
i applied part of your fix and THANKS GOD THIS WAS THE FIX
it points me to the error which was in my entities classes relationships which showed to me as error message
Hello there,
We’re glad you have managed to fix your issue! :) If you liked the post, feel free to like us on Twitter and Facebook!