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:

ArgumentsDescriptions
<FROM>The starting migration. Defaults to '0' (the initial database).
<TO>The ending migration. Defaults to the last migration.

Options

Optionsshort cutDescription
--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-hShow help information
--verbose-vShow verbose output.
--no-color Don't colorize output.
--prefix-outputPrefix 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

Create EFContext.cs under the models folder.

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

Script-migration

Now, we can generate the SQL Script using the command

The following script is generated. Now you can go to SQL Server. Create a database and execute the following script to create the database.

Updating the model

Now let us add a new field Rate to the Product model as shown below

Create a new migration and name is as V2. Then generate the script again as shown below

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.

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.

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.

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.

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

Leave a Comment

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.

Scroll to Top