In this tutorial let us explore the script migration in EF core to generate SQL Scripts. We can execute these SQL Scripts in the production server to bring the database in sync with the model. This is very useful when you do not have direct access to the production server. The script migration has a --idempotent option which ensures that you do not accidentally execute the script twice. This tutorial assumes that you know how to create EF Core console app and also know about EF Core migrations
Source Code:
The source code of this project available in GitHub.
Table of Contents
Script-migration Syntax
Package Manager Console
Script-migration [arguments] [options]
ef migrations script [arguments] [options]
Arguments:
| Arguments | Descriptions |
|---|---|
| <FROM> | The starting migration. Defaults to '0' (the initial database). |
| <TO> | The ending migration. Defaults to the last migration. |
Options
| Options | short cut | Description |
|---|---|---|
| --output | -o | The file to write the result to. |
| --idempotent | -i | Generate a script that can be used on a database at any migration. |
| --context | -c | The DbContext to use. |
| --assembly | -a | The assembly to use. |
| --startup-assembly | -s | The startup assembly to use. Defaults to the target assembly. |
| --data-dir | The data directory. | |
| --project-dir | The project directory. Defaults to the current directory. | |
| --root-namespace | The root namespace. Defaults to the target assembly name. | |
| --language | The language. Defaults to 'C#'. | |
| --working-dir | The working directory of the tool invoking this command. | |
| |--help | -h | Show help information |
| --verbose | -v | Show verbose output. |
| --no-color | Don't colorize output. | |
| --prefix-output | Prefix output with level. |
Script-migration Example
Create a new EF Core application. Add the following Product & Vendor models under the folder Models. Refer to the tutorial EF Core Migrations for details
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 | //Product.cs namespace EFCoreMigration.Models { public class Product { public int Id { get; set; } public string Name { get; set; } } } //Vendor.cs namespace EFCoreMigration.Models { public class Vendor { public int Id { get; set; } public string Name { get; set; } } } |
Create EFContext.cs under the models folder.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | using Microsoft.EntityFrameworkCore; namespace EFCoreMigration.Models { public class EFContext : DbContext { private const string connectionString = "Server=(localdb)\\mssqllocaldb;Database=EFCoreMigration;Trusted_Connection=True;"; protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) { optionsBuilder.UseSqlServer(connectionString); } public DbSet<Product> Products { get; set; } public DbSet<Vendor> Vendors { get; set; } } } |
Add Initial Migration
Now, we can run the command add-migration V1 to create the first migration. Here V1 is the name of the migration
1 2 3 | add-migration V1 |
Script-migration
Now, we can generate the SQL Script using the command
1 2 3 | script-migration |
The following script is generated. Now you can go to SQL Server. Create a database and execute the following script to create the database.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 | IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL BEGIN CREATE TABLE [__EFMigrationsHistory] ( [MigrationId] nvarchar(150) NOT NULL, [ProductVersion] nvarchar(32) NOT NULL, CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId]) ); END; GO CREATE TABLE [Products] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Products] PRIMARY KEY ([Id]) ); GO CREATE TABLE [Vendors] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Vendors] PRIMARY KEY ([Id]) ); GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200413070550_v1', N'3.1.3'); GO |
Updating the model
Now let us add a new field Rate to the Product model as shown below
1 2 3 4 5 6 7 8 | public class Product { public int Id { get; set; } public string Name { get; set; } public int Rate { get; set; } } |
Create a new migration and name is as V2. Then generate the script again as shown below
1 2 3 4 | add-migration v2 script-migration |
The command generates the following script. You will notice two points here
- It generates the entire script. Not the changed scripts
- If you run this script on a database where the
V1version is already applied, then it will result in an error.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 | IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL BEGIN CREATE TABLE [__EFMigrationsHistory] ( [MigrationId] nvarchar(150) NOT NULL, [ProductVersion] nvarchar(32) NOT NULL, CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId]) ); END; GO CREATE TABLE [Products] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Products] PRIMARY KEY ([Id]) ); GO CREATE TABLE [Vendors] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Vendors] PRIMARY KEY ([Id]) ); GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200413070550_v1', N'3.1.3'); GO ALTER TABLE [Products] ADD [Rate] int NOT NULL DEFAULT 0; GO INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200413070847_v2', N'3.1.3'); GO |
idempotent
The script does not check if the migrations already applied or not. This may lead to an error while applying the script to the database. This is where the idempotent flag comes handy.
An idempotent script does not apply the migrations if they are already applied. It helps you under the following circumstances
- What was the last migration you ran on a database
- You have multiple databases and each with a different set of migration.
- it also helps you accidentally skipping a migration. For Example, you apply the v1 and then v3 without applying the v2.
Run the script-migration with idempotent -i flag as shown below.
1 2 3 | script-migration -i |
If you inspect the script, you can see that it checks for the value of MigrationId from the __EFMigrationsHistory table to ensure that the migrations are not applied again.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 | IF OBJECT_ID(N'[__EFMigrationsHistory]') IS NULL BEGIN CREATE TABLE [__EFMigrationsHistory] ( [MigrationId] nvarchar(150) NOT NULL, [ProductVersion] nvarchar(32) NOT NULL, CONSTRAINT [PK___EFMigrationsHistory] PRIMARY KEY ([MigrationId]) ); END; GO IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200413070550_v1') BEGIN CREATE TABLE [Products] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Products] PRIMARY KEY ([Id]) ); END; GO IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200413070550_v1') BEGIN CREATE TABLE [Vendors] ( [Id] int NOT NULL IDENTITY, [Name] nvarchar(max) NULL, CONSTRAINT [PK_Vendors] PRIMARY KEY ([Id]) ); END; GO IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200413070550_v1') BEGIN INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200413070550_v1', N'3.1.3'); END; GO IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200413070847_v2') BEGIN ALTER TABLE [Products] ADD [Rate] int NOT NULL DEFAULT 0; END; GO IF NOT EXISTS(SELECT * FROM [__EFMigrationsHistory] WHERE [MigrationId] = N'20200413070847_v2') BEGIN INSERT INTO [__EFMigrationsHistory] ([MigrationId], [ProductVersion]) VALUES (N'20200413070847_v2', N'3.1.3'); END; GO |
FROM & TO Arguments
The above command generates the script starting from the first version to the last version. We can limit the generated script using the From & To Arguments.
Note that that the version specified in <FROM> argument is excluded from the script. i.e. when you specify the script-migration V1 V3 as the argument, only V2 & V3 are included in the final script. V1 is taken as a starting point and is not added in the script.
1 2 3 4 5 6 7 8 | //From the version 0 to version V1 script-migration 0 V1 -i script-migration V1 V2 -i //Starting version V1 to V2 script-migration V1 -i //starting from version V1 till the last version script-migration -i //All Updates starting from 0 to the last version |
Remove migration
When you remove a migration using the command remove-migration the migration is deleted from the package. If the deleted migration is already applied to the database, then you have to remove it manually from the database and bring it in sync with the model.
References
Read More
- EF Core Migrations
- Reverse Engineer Database (Database First)
- EF Core Data Seeding to the Database
- EF Core Script MIgration
Summary
In this article, we learned how to use script migration to update the database Make sure you use the idempotent flag to ensure that these scripts are accidentally executed or executed out of order. Also, note that the remove-migration will not be captured by the script migration as it removes the migration from the project