EF Core Migrations

The data models change as features get implemented, new entities or properties are added and removed, and database schemas need to be changed accordingly to be kept in sync with the application.

In this article, we will go through the implementation of migration scripts in the following sections:
1. Creating Database and Tables
2. Modifying a Column in the Table
3. Executing SQL Commands

Let us start by Creating a Net Core Project

A. Creating .NetCore Project

Create a new Project named ‘EmployeeManagementSystem’

Fig A.1: Create .Net Core Web App Project

Fig A.2: Name the Project — EmployeeManagementSystem

Fig A.3: Select Framework — .Net 6.0

B. Configure the connection string in the AppSettings.json

Fig B.1: Configure Connection String in App Settings

C. Install Packages

Open Package Manager Console
Path: Tools > Nuget Package Manager > Package Manager Console

Run the following packages in the Package Manager Console

Install-Package Microsoft.EntityFrameworkCore -Version 6.0.3
Install-Package Microsoft.EntityFrameworkCore.Design -Version 6.0.3
Install-Package Microsoft.EntityFrameworkCore.SqlServer -Version 6.0.3
Install-Package Microsoft.EntityFrameworkCore.Tools -Version 6.0.3

D. Configure DBContext

Create a Context Class

Fig D.1: Create Context Class

Fig D.2: Context.cs

Configure DbContext Service in Program.cs

Fig D.3: Add DbContext Service

E. Migrations: Database/Table Creation

1. Adding a Model

We have completed Creating the Project and configuring the services.
Next, we will create a model named ‘Employee’ with the following fields:
- Id [Integer, Primary Key]
- Name [Varchar, Not Null]

Fig E.1: Add Employee Class under Models

Fig E.2: Configure Employee Model

Fig E.3: Add Employee Model to DbContext

The Employee Model has been configured. Now we need to generate a migration script based on the model created. To generate a migration script, the following command needs to be executed in the Package Manager Console.

Add-Migration <MigrationScriptName>

Fig E.4: Generate Migration Script

Once the Migration file has been generated, a new folder named ‘Migrations’ will be created under the Project. This folder will hold the generated migrations. The migration codes are generated based on the model created.

The screenshot below will display the generated migration code.

Fig E.4: Migration Code

After generating the migration file, we need to execute the code, so that the model is synced with the database and its content. To execute the migration file, the following command needs to be executed in the Package Manager Console.

Update-Database

Once this command is executed, the EF will fetch the Connection string configured in the appSettings.json. If the configured database does not exist, a new database is created. Once the database is created the table ‘Employee’ will be created next.

Fig E.5: Update-Database

Once the script has been executed successfully, an additional table will be generated named — ‘EFMigrationsHistory’. This table will log all the scripts executed. So, the next time when you execute a script, it checks if that script has already been executed or not. If executed, that script is excluded and the next script is taken for execution.

Fig E.6: Migration History

2. Modifying a Model

Now let's modify the Employee Model, by making the field ‘Name’ a Nullable string and adding a new integer field ‘Age’.

Fig E.7: Update Employee Model

Generate script for the modified model.

Fig E.8: Generate Migration Script for Updated Model

Every time when a new migration script is generated, the application checks if there are any changes in the models, or if any new models are added. The migration codes are generated automatically based on these changes.

Fig E.9: Generated Script for the modified Employee model

Now let's update these changes to the database:

Fig E.10: Update Database with the new scripts

Fig E.11: Migration History

3. Executing SQL Scripts

Next, let's create a migration to perform SQL Commands
Our models and the database are now synced. Let's try to create a migration script now.

Fig E.12: Generate Script for SQL Commands

Since there are no model updates, a blank migration file will be generated. If you have noticed. Each Migration file will be having 2 functions : up() & down().

The up() function is executed when the update database is triggered. The down() function is executed when we are removing a migration.

Fig E.13: Empty Migration File

Let's write the code to perform to insert values to the employee table and also, let's create a stored procedure that accepts employee name and performs an insert.

Fig E.14: Migration-SQL Commands

Let us now perform the update-database. The result is displayed in the screenshot below.

Fig E.15: Update database

Fig E.15: Update Database Result

F. Flow

1. Add-Migration

Fig F.1: Add Migration

2. Update-Database

Fig F.1: Update-Database

Conclusion

We have gone through step by step implementation of EF Core Migration. FOr more information please go through the Microsoft documentation in this link.

I hope this article helped you and provided the confidence to take one step closer to implementing migrations in your project.

Happy Coding :)