Welcome to today’s post.
I will give a handy hint on upgrading SQL server database schemas that are in different instances or databases within the same instance using EF Core within the Visual Studio 2017 IDE.
This hint comes in handy whenever you have just upgraded the SQL Express version of a SQL database and you want to apply the same EF migration changes that you just applied to the Express database to another database (it might be another SQL Express database or a non-Express SQL database either local or on a server).
Below you have your connection strings to your databases:
Let AppDbContext be the connection string to your SQL Express database.
Let AppDbContext2 be the connection string to one of your other SQL databases that is not hosted as an SQL express instance.
Suppose that you have just applied the following migration(s) to the database for connection string AppDbContext:
You then wish to apply the same migrations to the database with the connection string AppDbContext2.
To do this is quite simple: Just swap the connection string names and re-save appsettings.json as shown:
Now open the Powershell prompt (or PMC) and apply the update database command:
Update-Database
Now open SSMS and connections to both SQL data stores and check the table and object definitions. They should be applied successfully.
When the above will fail is if you have implemented code that depends on the new migration and have run the update database command on the new connection string.
You will end up with an error like this:
Since EF Core runs Startup.cs to apply the migration to the database, it also verifies any code from the start up that does seeding, and references existing tables and / or fields in the target database.
The other alternative, which is a more standard approach is to generate the idempotent script using:
dotnet ef migrations script --idempotent
To generate a script that can be applied directly to the database offline or through a deployment pipeline, you can use the following command:
Script-Migration -Idempotent -Output [output file name]
The Idempotent switch will generate a script that can be run to bring any database schema up to date with the last migration. Entries in the script will be similar to the one below:
IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20191112075920_add_bookloan_newfields_12_11_2019')
BEGIN
ALTER TABLE [Loans] ADD [DateDue] datetime2 NOT NULL DEFAULT '0001-01-01T00:00:00.000';
END;
GO
Essentially, the idempotent script when run will create any schema tables not existing in the target schema.
That’s all for todays post.
I hope you found it useful and informative.
Andrew Halil is a blogger, author and software developer with expertise of many areas in the information technology industry including full-stack web and native cloud based development, test driven development and Devops.
