Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
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.
If you run into problems you can't solve, download the completed app and compare that code to what you created by following the tutorial.
This tutorial introduces the EF Core migrations feature for managing data model changes.
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 series started by configuring the Entity Framework to create the database if it doesn't exist. Each time the data model changes, the database needs to be dropped. The next time the app runs, the call to EnsureCreated re-creates the database to match the new data model. The DbInitializer class then runs to seed the new database.
This approach to keeping the DB in sync with the data model works well until the app needs to be deployed 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 database.
Rather than dropping and recreating the database when the data model changes, migrations updates the schema and retains existing data.
Note
SQLite limitations
This tutorial uses the Entity Framework Core migrations feature where possible. Migrations updates the database schema to match changes in the data model. However, migrations only does the kinds of changes that the database engine supports, and SQLite's schema change capabilities are limited. For example, adding a column is supported, but removing a column is not supported. If a migration is created to remove a column, the ef migrations add command succeeds but the ef database update command fails.
The workaround for the SQLite limitations is to manually write migrations code to perform a table rebuild when something in the table changes. The code goes in the Up and Down methods for a migration and involves:
- Creating a new table.
- Copying data from the old table to the new table.
- Dropping the old table.
- Renaming the new table.
Writing database-specific code of this type is outside the scope of this tutorial. Instead, this tutorial drops and re-creates the database whenever an attempt to apply a migration would fail. For more information, see the following resources:
Drop the database
Use SQL Server Object Explorer (SSOX) to delete the database, or run the following command in the Package Manager Console (PMC):
Drop-Database
Create an initial migration
Run the following commands in the PMC:
Add-Migration InitialCreate
Update-Database
Remove EnsureCreated
This tutorial series started by using EnsureCreated. EnsureCreated doesn't create a migrations history table and so can't be used with migrations. It's designed for testing or rapid prototyping where the database is dropped and re-created frequently.
From this point forward, the tutorials will use migrations.
In Program.cs, delete the following line:
context.Database.EnsureCreated();
Run the app and verify that the database is seeded.
Up and Down methods
The EF Core migrations add command generated code to create the database. This migrations code is in the Migrations\<timestamp>_InitialCreate.cs file. The Up method of the InitialCreate class creates the database tables that correspond to the data model entity sets. The Down method deletes them, as shown in the following example:
using System;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.EntityFrameworkCore.Migrations;
namespace ContosoUniversity.Migrations
{
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(
name: "Student",
columns: table => new
{
ID = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
LastName = table.Column<string>(nullable: true),
FirstMidName = table.Column<string>(nullable: true),
EnrollmentDate = table.Column<DateTime>(nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Student", x => x.ID);
});
migrationBuilder.CreateTable(
name: "Enrollment",
columns: table => new
{
EnrollmentID = table.Column<int>(nullable: false)
.Annotation("SqlServer:ValueGenerationStrategy", SqlServerValueGenerationStrategy.IdentityColumn),
CourseID = table.Column<int>(nullable: false),
StudentID = table.Column<int>(nullable: false),
Grade = table.Column<int>(nullable: true)
},
constraints: table =>
{
table.PrimaryKey("PK_Enrollment", x => x.EnrollmentID);
table.ForeignKey(
name: "FK_Enrollment_Course_CourseID",
column: x => x.CourseID,
principalTable: "Course",
principalColumn: "CourseID",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_Enrollment_Student_StudentID",
column: x => x.StudentID,
principalTable: "Student",
principalColumn: "ID",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Enrollment_CourseID",
table: "Enrollment",
column: "CourseID");
migrationBuilder.CreateIndex(
name: "IX_Enrollment_StudentID",
table: "Enrollment",
column: "StudentID");
}
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Enrollment");
migrationBuilder.DropTable(
name: "Course");
migrationBuilder.DropTable(
name: "Student");
}
}
}
The preceding code is for the initial migration. The code:
- Was generated by the
migrations add InitialCreatecommand. - Is executed by the
database updatecommand. - Creates a database for the data model specified by the database context class.
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."
The migrations history table
- Use SSOX or SQLite tool to inspect the database.
- Notice the addition of an
__EFMigrationsHistorytable. The__EFMigrationsHistorytable keeps track of which migrations have been applied to the database. - View the data in the
__EFMigrationsHistorytable. It shows one row for the first migration.
The data model snapshot
Migrations creates a snapshot of the current data model in Migrations/SchoolContextModelSnapshot.cs. When add a migration is added, EF determines what changed by comparing the current data model to the snapshot file.
Because the snapshot file tracks the state of the data model, a migration cannot be deleted by deleting the <timestamp>_<migrationname>.cs file. To back out the most recent migration, use the migrations remove command. migrations remove deletes the migration and ensures the snapshot is correctly reset. For more information, see dotnet ef migrations remove.
See Resetting all migrations to remove all migrations.
Applying migrations in production
We recommend that production apps not call Database.Migrate at application startup. Migrate shouldn't be called from an app that is deployed to a server farm. If the app is scaled out to multiple server instances, it's hard to ensure database schema updates don't happen from multiple servers or conflict with read/write access.
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 updatefrom a controlled environment.
Troubleshooting
If the app uses SQL Server LocalDB and displays the following exception:
SqlException: Cannot open database "ContosoUniversity" requested by the login.
The login failed.
Login failed for user 'user name'.
The solution may be to run dotnet ef database update at a command prompt.
Related content
Next steps
The next tutorial builds out the data model, adding entity properties and new entities.
ASP.NET Core