EF Core Script Migration

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.

Script-migration Syntax

Package Manager Console

Script-migration [arguments] [options]

Dot Net Core CLI

ef migrations script [arguments] [options]

Arguments:

Options

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

//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.

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

add-migration V1

Script-migration

Now, we can generate the SQL Script using the command

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.

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

    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

add-migration v2
script-migration

The command generates the following script. You will notice two points here

1. It generates the entire script. Not the changed scripts
2. If you run this script on a database where the V1 version is already applied, then it will result in an error.

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

1. What was the last migration you ran on a database
2. You have multiple databases and each with a different set of migration.
3. 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.

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.

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.

//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

1. Entity Framework Core tools reference

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