Keeping domain models and database schema in sync in asp.net core

Suggested Videos
Part 49 - Repository pattern in asp.net core | Text | Slides
Part 50 - Entity framework core migrations | Text | Slides
Part 51 - Entity framework core seed data | Text | Slides

In this video we will discuss 

• How to keep domain models and database schema in sync using migrations in asp.net core
• How to add a migration in asp.net core
• How to remove a migration in asp.net core
• How to apply the migration and update the database
• How to undo a migration that is already applied and bring the database schema to it's previous state
• What is the use of ModelSnapshot class and __EFMigrationsHistory table

As we develop our application and add new features, our application domain classes change. When the domain classes change, the corresponding changes have to be made to the underlying database schema. Otherwise the database schema goes out of sync and the application does not work as expected. 

However, it is important not to manually make these changes to the database schema. We use migrations to keep the database schema in sync as the application domain classes change.


Example 

At the moment our domain Employee class does not have a property to store the Photo Path of the employee.

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Email { get; set; }

    public Dept? Department { get; set; }

}

To be able to store the Photo Path of the employee, we want to add PhotoPath property to the Employee class.

public class Employee

{

    public int Id { get; set; }

    public string Name { get; set; }

    public string Email { get; set; }

    public Dept? Department { get; set; }

    public string PhotoPath { get; set; }

}

To keep the Employees database table in sync with the Employee Model class, add a new migration and execute it to update the database. 

Adding a new migration

Use Add-Migration command to add a new migration. AddPhotoPathToEmployees is the name of the migration.

Add-Migration AddPhotoPathToEmployees

Files in the Migrations folder

Executing the above command generates the following files.

[TimeStap]_AddPhotoPathToEmployees.cs - The file name is made up of TimeStamp, UnderScore and the migration name. The class name in this file has the same name as the migration name. This class contains 2 methods - Up() and Down(). Up() method contains code to apply the changes made to the model class to the underlying database schema. Down() method contains code to undo the changes.

[DbContextClassName]ModelSnapshot.cs - As the name implies this file contains the snapshot of your current model. This file is created when the first migration is added and updated with each subsequent migration. EF core migrations API uses this file to determine what has changed when adding the next migration.

Apply Migration and Update Database

To apply the migration and update the database use Update-Database command. This command executes the code in Up() method and applies the changes to the underlying database objects.

Update-Database

Use of __EFMigrationsHistory table 

__EFMigrationsHistory table is created in the database, when the first migration is executed. This table is used to keep track of the migrations that are applied to the database. There will be an entry for every migration that is applied.

Removing a migration

To remove a migration execute Remove-Migration command

It only removes one migration at a time and that too only the latest migration that is not yet applied to the database. If all the migrations are already applied, executing Remove-Migration command throws the following exception.

The migration 'Latest_Migration_Name' has already been applied to the database. Revert it and try again.

Removing a migration that is already applied to the database

Let us understand how to remove a migration that has already been applied to the database with an example. Let us say we have the following 3 migrations already applied to the database.

• Migration_One
• Migration_Two
• Migration_Three

We want to remove both migration Migration_Two and Migration_Three. Since all these 3 migrations are already applied to the database, executing Remove-Migration command will throw an error.

To be able to remove a migration that is already applied to the database, we first have to undo the changes the migration made to the database. The way we do this is by executing Update-Database command with a migration name. 

If we relate this to our example, we want to undo the changes done by Migration_Two and Migration_Three and bring the database state to the state it was in, when Migration_One was executed. To achieve this we execute Update-Database command with Migration_One name as shown below.

Update-Database Migration_One

Executing the above command will undo the changes made by Migration_Two and Migration_Three. EF core will also remove the entries for these 2 migrations from __EFMigrationsHistory table.

However, the migration code files will still be there in the Migrations folder. To remove the code files use Remove-Migration command. Since we want to remove both Migration_Three and Migration_Two code files, we want to execute Remove-Migration command twice.