EF Core: The Main Things You Need To Know About Migrations

Rename fields, merge fields, change output directories and more

Are you a Software Engineer who enjoys writing raw SQL and taking a database-first approach? If so, Migrations aren’t going to be your thing.

In Entity Framework Core, engineers can evolve their database schema programmatically. When requirements change and the code changes, the database schema will respond and update accordingly. Modifications are recorded in Migration files which are stored within your app. Each file comes with a descriptive name and a timestamp. These files can be treated like a commit message.

Migration files provide a record of all the changes to the database over time. A clear migration history makes performing a rollback far more straightforward.

Let’s explore how to maximise Migrations and overcome some common obstacles.

Photo by Alina Grubnyak on Unsplash

Migration commands

To allow Migrations in your app, you need to install Microsoft.EntityFrameworkCore.Tools:

dotnet tool install -g dotnet-ef // allows you to use the dotnet core CLI
dotnet ef add package Microsoft.EntityFrameworkCore.Tools

After we have set up our DbContext and registered our entities using DbSet, we can run the following command to create a migration. Replace MigrationName with a description of your migration:

dotnet ef migrations add MigrationName

A new folder will be created in your app called Migrations. Inside the Migrations folder, we will have two new files. One file will describe our migration and the other is a snapshot file which serves as a historical record of the database schema.

The snapshot file is important because it reflects the state of the database schema at a given time. EF Core uses this as a reference when deciding if any migrations need to be applied. If there is a discrepancy between the snapshot and the current state of the DbContext, then migrations will run to ensure everything is aligned.

If you’re not happy after reviewing the migration file, you can remove it

// removes the last migration
dotnet ef migrations remove

Otherwise, you can apply the migration and update your database. The command will look for a database. If no database can be found then a new one is created. Then it will review which migrations have been applied and generate a new SQL script if a migration is needed. Entity Framework Core runs the new script against the database:

dotnet ef database update

Output Migrations in a different directory

Sometimes, you will want your Migrations folder to appear inside a specific project or folder. For instance, if you follow a Clean Architecture approach, you will want your migration history to sit at the same layer as your DbContext.

To help with this we can use some parameters when running our .NET core commands: --project, --start-up-project and--output-dir.

• --project helps us specify where our migrations should be added and this value is relative to where the command is being run.
• --start-up-project represents the entry point to our app and where the command is being run. We can use a . value to indicate the current directory.
• Lastly, --output-dir goes into more detail about where the migration files will be saved.

The below command adds a new migration and saves it inside the MyProject.Infrastructure project. As we run the command from the app’s entry point, we use a . to indicate the start-up project. We then target a specific folder inside MyProject.Infrastructure to save our migration files: Data/Migrations.

dotnet ef migrations add InitialCreate --project ../MyProject.Infrastructure --startup-project . --output-dir Data/Migrations

Rename a column in the database

Imagine you have registered the following table in your DbContext:

public class DataContext : DbContext
{
  public DataContext(DbContextOptions<DataContext> options) : base(options)
  {
  
  }  

  public DbSet<Employee> Employees { get; set; }
}

public class Employee
{
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string Team { get; set; }
}

A requirement comes in for you to rename the Team field to Squad. The change will be reflected in the database and the column will also be called Squad.

When a migration is generated, Entity Framework Core will instead drop the existing Team column and create a new Squad column. As a result, there is a risk of losing data:

// This is inside your migration file

migrationBuilder.DropColumn(
    name: "Team",
    table: "Employees");

migrationBuilder.AddColumn<string>(
    name: "Squad",
    table: "Employees",
    nullable: true);

Instead, to rename a column, you need to modify the migrationBuilder code and use the RenameColumn method:

migrationBuilder.RenameColumn(
    name: "Team",
    table: "Employees",
    newName: "Squad");

Merge two columns into one

Using the above scenario, what if another requirement were to arrive to merge the FirstName and LastName fields into a single FullName field?

Our Employee object will look like this:

public class Employee
{
  public int Id { get; set; }
  public string FullName { get; set; }
  public string Squad { get; set; }
}

When we run a migration, Entity Framework Core will drop both the FirstName and LastName fields and create a new FullName field. We will lose all our data for FirstName and LastName.

migrationBuilder.DropColumn(
    name: "FirstName",
    table: "Employees");

migrationBuilder.DropColumn(
    name: "LastName",
    table: "Employees");

migrationBuilder.AddColumn<string>(
    name: "FullName",
    table: "Employees",
    nullable: true);

To fix this issue we can write raw SQL inside our migration file. Remember that Migration files, as well as the code inside, work in a sequential, cascading way. Each Migration file builds on the preceding one. With that in mind, we need to tell our migration to do the following:

1. Create a new column
2. Use the existing data in the database to update the new column
3. Delete the old columns

Our code will look like this:

migrationBuilder.AddColumn<string>(
    name: "FullName",
    table: "Employees",
    nullable: true);

migrationBuilder.Sql(
@"
    UPDATE Employees
    SET FullName = FirstName + ' ' + LastName;
");

migrationBuilder.DropColumn(
    name: "FirstName",
    table: "Employees");

migrationBuilder.DropColumn(
    name: "LastName",
    table: "Employees");

We can create a method that extends the Migration Builder API to encapsulate the custom operation in an isolated file:

// in the Migrations file
migrationBuilder.MergeFields("Employees", "FullName", "FirstName", "LastName");

// Extension method
public static OperationBuilder<SqlOperation> MergeFields(
    this MigrationBuilder migrationBuilder,
    string table,
    string newField,
    string fieldOne,
    string fieldTwo)
    => migrationBuilder.Sql(
      @"
        UPDATE {table} 
        SET {newField} = {fieldOne} + ' ' + {fieldTwo}';
      ");

Check for pending model changes

Entity Framework Core 8 introduced a new way to check whether there have been any model changes since the last migration.

By entering the below command, you can check to see whether your code is out of sync with the current migration:

dotnet ef migrations has-pending-model-changes

The check can also be run in your code with context.Database.GetPendingMigrations(). You can use the GetPendingMigrations() method to create a unit test in your app.

GetPendingMigrations() will return an IEnumerable<string>. Therefore, your test should assert that this result should be empty.

[Test]
public void Database_Should_Have_No_Pending_Migrations()
{
    using (var context = new MyDbContext(_options))
    {
        var pendingMigrations = context.Database.GetPendingMigrations();

        pendingMigrations.Should().BeEmpty();
    }
}

If the above test fails, then it indicates that you need to run a migration. The test can be a valuable part of your CI/CD pipeline.

Generate SQL scripts from Migrations

Sometimes, you may need to generate an SQL script that reflects the code in your migrations. The below code will create a SQL script from a blank database to the latest migration:

dotnet ef migrations script

The script can be modified to target a specific period of the migration history. We can append a ‘from’ and ‘to’ value to our command to prioritise certain migrations:

// AddEmployeesTable represents the 'from' parameter
// because the 'to' value is empty, it will default to the latest migration
dotnet ef migrations script AddEmployeesTable

// Conversely, below we have both 'from' and 'to' values
dotnet ef migrations script AddEmployeesTable CreateFullNameField

When working with Entity Framework Core it is important to understand how Migrations work. As we can see in some of our examples, Entity Framework Core is a powerful, intuitive ORM however it can misinterpret your intentions. Understanding how to read and modify Migration files will reduce the risk of losing data and ensure you and your team are in sync.

🙌 🙌