Entity Framework Core Migrations

The following topics are covered in this document:

When developing applications, the model is likely to change often as new requirements come to light. The database needs to be kept in sync with the model. The migrations feature enables you to make changes to your model and then propagate those changes to your database schema.

Migrations are enabled by default in EF Core. They are managed by executing commands. If you have Visual Studio, you can use the Package Manager Console (PMC) to manage migrations. Alternatively, you can use a command line tool to execute Entity Framework CLI commands to create a migration.

Creating a Migration

The following command creates a migration:

[Command Line]
dotnet ef migrations add <name of migration>

[Package Manager console]
add-migration <name of migration>

When you create a migration, the framework compares the current state of the model with the previous migration if one exists and generates a file containing a class inheriting from Microsoft.EntityFrameworkCore.Migrations.Migration featuring an Up and a Down method. The class is given the same name as you specified for the migration. The file name itself is the name of the migration prefixed with a timestamp.

The Up method contains C# code that applies any changes made to the model to the schema of the database since the last migration was generated. The Down method reverses those changes, restoring the database to the state of the previous migration. A ModelSnapshot file is also created or updated, depending on whether one previously existed.

Removing A Migration

The following command removes a migration:

[Command Line]
dotnet ef migrations remove

[Package Manager Console]
remove-migration

You will use this command to remove the latest migration. This will remove the class file that was generated for the latest migration and it will also revert the ModelSnapshot file back to the state of the previous migration. If there are no pending migrations, an exception will be raised. If you want to remove a migration that has been committed, you must reverse the migration first (see below).

You should always use commands to remove a migration instead of simply deleting the migration code file, otherwise the snapshot and migrations will fall out of sync with eachother. Then future migrations will be based on a model that is incorrect. Nevertheless, the remove command will recognise if migration files have been deleted and will revert the snapshot accordingly.

If you need to remove a migration that was generated before the most recent one, you must remove all sunsequent migrations first, then adjust the model and then create a new migration to cater for the changes.

Applying A Migration

The following command results in the Up method of the migration class being executed with any changes applied to the database:

[Command line]
dotnet ef database update

[Package Manager Console]
update-database

Unless otherwise specified, all pending migrations will be applied. If this is the first migration, a table will be added to the database called __EFMigrationsHistory. It is used to store the name of this and each subsequent migration as they are applied to the database.

Reversing A Migration

To reverse a migration, you pass the name of a target migration to the update command. The target migration is the point to which you want to restore the database. For example, if your first migration is named "Create", and you would like to restore the database to remove all changes made by subsequent migrations, you pass "Create" to the update command:

[Command line]
dotnet ef database update Create

[Package Manager Console]
update-database Create

This action wil result in the Down method in all subsequent migrations being executed. Any migrations that have been applied to the database after the target migration will have their entries removed from the __EFMigrationsHistory table in the database. It will not remove subsequent migrations from the Migrations folder, or alter the ModelSnapshot file. You should use the remove command to achieve this rather than manually deleting the migrations files.

Applying A Migration To A Remote Database

At some stage, you will need to deploy one or more migrations against another database, whether that is a test database or a live production database. Currently, the easiest way to accomplish that is to execute SQL scripts against the target database. You can generate the required script via the script command:

[Command Line]
dotnet ef migrations script

[Package Manager Console]
script-migration

By default, all migrations will be included. You can specify a range of migrations to include by using the -to and -from options.

Executing Custom SQL

Sometimes, you may want to execute custom SQL at the same time as the migration is applied to the database. You may need to do this because your database provider doesn't support something, or because the operation you want to perform is not related to the migration. Or you might want to add a new coumn to a table and then populate it with data.

In these instances, you can use the MigrationBuilder.Sql method. This should be placed in the generated Up method at the point where you want the SQL to be executed. In the example below, the method is called before the first table is created:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql("Some custom SQL statement");

        migrationBuilder.CreateTable(
            name: "Authors",
            columns: table => new
            {
                AuthorId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                FirstName = table.Column<string>(nullable: true),
                LastName = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Authors", x => x.AuthorId);
            });

If you need to reverse the custom SQL operation in the event that the migration is applied, you would use the Sql method to execute the appropriate SQL in the Down method.

Targeting Multiple Providers

Migrations are generated for a specific provider. Although a lot of work has been done to make migrations as provider-agnostic as possible, there will inevitably be cases where a migration generated for one provider cannot be used against another. Annotations are used for provider-specific migrations operations, and if they don't apply to the current provider, they are ignored. You can therefore "overload" the migration with as many annotations as you need safe in the knowledge that the current provider will only apply the ones that relate to it:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Authors",
            columns: table => new
            {
                AuthorId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn)
                    .Annotation("Sqlite:Autoincrement", true),
                FirstName = table.Column<string>(nullable: true),
                LastName = table.Column<string>(nullable: true)
            },

The Migration class exposes an ActiveProvider property that gets the name of the current database provider being used. This can be helpful for generating conditional code enabling a single migration to target multiple providers:

public partial class CreateDatabase : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {

        if(ActiveProvider == "Microsoft.EntityFrameworkCore.SqlServer")
        {
            // do something SQL Server - specific
        }
        if(ActiveProvider == "Microsoft.EntityFrameworkCore.Sqlite")
        {
            // do something SqLite - specific
        }

        migrationBuilder.CreateTable(
            name: "Authors",
            columns: table => new
            {
                AuthorId = table.Column<int>(nullable: false)
                    .Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
                FirstName = table.Column<string>(nullable: true),
                LastName = table.Column<string>(nullable: true)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Authors", x => x.AuthorId);
            });

Created:
Last updated: 28/03/2017 08:52:39
Proficiency Level: Beginner