Razor Pages with EF Core in ASP.NET Core - Migrations - 4 of 8

By Tom Dykstra, Jon P Smith, and Rick Anderson

The Contoso University web app demonstrates how to create Razor Pages web apps using EF Core and Visual Studio. For information about the tutorial series, see the first tutorial.

In this tutorial, the EF Core migrations feature for managing data model changes is used.

If you run into problems you can't solve, download the completed app.

When a new app is developed, the data model changes frequently. Each time the model changes, the model gets out of sync with the database. This tutorial started by configuring the Entity Framework to create the database if it doesn't exist. Each time the data model changes:

  • The DB is dropped.
  • EF creates a new one that matches the model.
  • The app seeds the DB with test data.

This approach to keeping the DB in sync with the data model works well until you deploy the app to production. When the app is running in production, it's usually storing data that needs to be maintained. The app can't start with a test DB each time a change is made (such as adding a new column). The EF Core Migrations feature solves this problem by enabling EF Core to update the DB schema instead of creating a new DB.

Rather than dropping and recreating the DB when the data model changes, migrations updates the schema and retains existing data.

Drop the database

Use SQL Server Object Explorer (SSOX) or the database drop command:

In the Package Manager Console (PMC), run the following command:

Drop-Database

Run Get-Help about_EntityFrameworkCore from the PMC to get help information.

Create an initial migration and update the DB

Build the project and create the first migration.

Add-Migration InitialCreate
Update-Database

Examine the Up and Down methods

The EF Core migrations add command generated code to create the DB. This migrations code is in the Migrations<timestamp>_InitialCreate.cs file. The Up method of the InitialCreate class creates the DB tables that correspond to the data model entity sets. The Down method deletes them, as shown in the following example:

public partial class InitialCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "Course",
            columns: table => new
            {
                CourseID = table.Column<int>(nullable: false),
                Title = table.Column<string>(nullable: true),
                Credits = table.Column<int>(nullable: false)
            },
            constraints: table =>
            {
                table.PrimaryKey("PK_Course", x => x.CourseID);
            });

        migrationBuilder.CreateTable(
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(
            name: "Enrollment");

        migrationBuilder.DropTable(
            name: "Course");

        migrationBuilder.DropTable(
            name: "Student");
    }
}

Migrations calls the Up method to implement the data model changes for a migration. When you enter a command to roll back the update, migrations calls the Down method.

The preceding code is for the initial migration. That code was created when the migrations add InitialCreate command was run. The migration name parameter ("InitialCreate" in the example) is used for the file name. The migration name can be any valid file name. It's best to choose a word or phrase that summarizes what is being done in the migration. For example, a migration that added a department table might be called "AddDepartmentTable."

If the initial migration is created and the DB exists:

  • The DB creation code is generated.
  • The DB creation code doesn't need to run because the DB already matches the data model. If the DB creation code is run, it doesn't make any changes because the DB already matches the data model.

When the app is deployed to a new environment, the DB creation code must be run to create the DB.

Previously the DB was dropped and doesn't exist, so migrations creates the new DB.

The data model snapshot

Migrations create a snapshot of the current database schema in Migrations/SchoolContextModelSnapshot.cs. When you add a migration, EF determines what changed by comparing the data model to the snapshot file.

To delete a migration, use the following command:

Remove-Migration

The remove migrations command deletes the migration and ensures the snapshot is correctly reset.

Remove EnsureCreated and test the app

For early development, EnsureCreated was used. In this tutorial, migrations are used. EnsureCreated has the following limitations:

  • Bypasses migrations and creates the DB and schema.
  • Doesn't create a migrations table.
  • Can not be used with migrations.
  • Is designed for testing or rapid prototyping where the DB is dropped and re-created frequently.

Remove EnsureCreated:

context.Database.EnsureCreated();

Run the app and verify the DB is seeded.

Inspect the database

Use SQL Server Object Explorer to inspect the DB. Notice the addition of an __EFMigrationsHistory table. The __EFMigrationsHistory table keeps track of which migrations have been applied to the DB. View the data in the __EFMigrationsHistory table, it shows one row for the first migration. The last log in the preceding CLI output example shows the INSERT statement that creates this row.

Run the app and verify that everything works.

Applying migrations in production

We recommend production apps should not call Database.Migrate at application startup. Migrate shouldn't be called from an app in server farm. For example, if the app has been cloud deployed with scale-out (multiple instances of the app are running).

Database migration should be done as part of deployment, and in a controlled way. Production database migration approaches include:

  • Using migrations to create SQL scripts and using the SQL scripts in deployment.
  • Running dotnet ef database update from a controlled environment.

EF Core uses the __MigrationsHistory table to see if any migrations need to run. If the DB is up-to-date, no migration is run.

Troubleshooting

Download the completed app.

The app generates the following exception:

SqlException: Cannot open database "ContosoUniversity" requested by the login.
The login failed.
Login failed for user 'user name'.

Solution: Run dotnet ef database update

Additional resources