Scripting Migrations in EF Core

Intro

Sometimes it's useful to generate T-SQL scripts for our Entity Framework Code First Migrations. Creating and applying migrations to our local development database(s), whether using LocalDb or something else, are fairly trivial tasks. What isn't as intuitive is how we can generate scripts from these migrations. Let's explore how this can be accomplished.

Setting Up Our Migrations

For this write-up, I'm going to assume that you already know the basics about setting up Entity Framework Core and adding your first migrations. I'm using the latest version of EF Core 2.0 at the time of publishing. I'm going to forego the details that are littered across dozens and dozens of other tutorials and do the bare minimum to demonstrate how to generate our change scripts. I'll also assume you already have your DbContext created and your connection string configured.

Let's start by creating our first model. We're going to create a simple model called Product.

using System.ComponentModel.DataAnnotations;

public class Product
{ 
    public int Id { get; set; }
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    public decimal Price { get; set; }
}

Now, add a DbSet<Product> to your DbContext class and create your first migration from the Package Manager console.

Add-Migration InitialCreate

This should create an initial migration for your database. Now let's apply the migration:

Update-Database

If all goes well, our database should be created and updated with our Product table. The Product table will have a primary key column called "Id", a "not null" nvarchar(100) column called "Name", and a decimal column called "Price".

Scripting Our Migrations

Now let's generate an initial SQL script that we can use to run on another server. We're going to accomplish this with the Script-Migration command from the package manager console.

Script-Migration

When the command completes, a new, randomly named .sql file will be opened in Visual Studio. This script includes everything you need to generate your database from scratch. In fact, at any time, with any number of migrations, you can run Script-Migration to generate a ground up creation script for your database. This will include the _EFMigrations table. If you don't want this table and its data in your other database (and you probably don't), simply remove it from the script.

Generating a script for the entire database is fine and dandy, but sometimes you want to script migrations from a specific point. This is where the Script-Migration command loses a bit of its intuitiveness. Let's go ahead and update our model, create a new migration, and update our database. We've decided we want to soft delete our entities instead of removing them from the database, so we're going to add an IsDeleted property. Our new Product entity now looks like this:

public class Product
{ 
    public int Id { get; set; }
    [Required]
    [MaxLength(100)]
    public string Name { get; set; }
    public decimal Price { get; set; }
    public bool IsDeleted { get; set; }
}

With the model updated, let's create a new migration and update our database with the following two commands:

Add-Migration SoftDeleteSupport

Update-Database

Once complete, the IsDeleted column is added to our development database. What we want to do now is generate a SQL script for our new migration. We will accomplish this with the Script-Migration command that we used earlier but we're going to add a migration name. Here's where it gets tricky. Intuition will tell you that you want to execute it with the name of the migration you just created, but attempting it will result in a blank sql file. That's because the migration specified to Script-Migration is the migration to start from.

In this case, we want to start from the InitialCreate migration, and script everything that comes after it, so the command we execute is:

Script-Migration InitialCreate

This will script every migration that has been added after the InitialCreate migration. If you want to script a specific migration or range of migrations, this can be done by passing two migration names to the Script-Migration command. However, in this case, the second parameter is inclusive. The format is:

Script-Migration <First: Exclusive> <Second: Inclusive>

This means that all migrations after "First", up to and including "Second" will be scripted.

And there you have it, sql scripts generated from your Entity Framework migrations. If you've found this post helpful or have questions, feel free to comment below.

Dustin Horne