Beginners: Code First Database Migration in Entity Framework

Posted on April 5, 2023
beginnersdatabaseEntity FrameworkCode FirstDatabase schema changesSQL database migration.NET Core 6.0Fluent APIDatabase initializer

What is Code First Database Migration in Entity Framework?

Code First Database Migration is a feature of Entity Framework, tool developers use to help map their code to a database. With Code First, developers can define the structure of their database using code, and Entity Framework can automatically generate and apply scripts to migrate the database based on changes made to the code. This makes it easier for developers to manage and maintain their databases as their code changes over time.

The source code for this article can be found on Github.

This article is based on .Net Core 6 + Entity Framework Core 7.0

All commands need to run in Visual Studio and open the Package Manager Console from Tools

Add NuGet Package

install-package Microsoft.EntityFrameworkCore
install-package Microsoft.EntityFrameworkCore.Tools
install-package Microsoft.EntityFrameworkCore.SqlServer

A step-by-step guide to implementing Code First Database Migration using Entity Framework

  1. Define: Connection string/Register Context in the Program.cs
{
 "ConnectionStrings": {
        "SchoolDb": "Server=localhost;Database=DPSSchoolDb;Trusted_Connection=SSPI;Encrypt=false;TrustServerCertificate=true"
    }
}

Program.cs

var connectionString = builder.Configuration.GetConnectionString("SchoolDb");

builder.Services.AddDbContextPool<SchoolDbContext>(option =>
{
    option.UseSqlServer(connectionString);
});
  1. Define your Entities/Models: Two classes are defined: StudentEntity and TeacherEntity

StudentEntity.cs

public class StudentEntity
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public DateTime DateOfBirth { get; set; }

    [NotMapped]
    public List<string> ClassesEnrolled { get; set; }
}

TeacherEntity.cs

public class TeacherEntity
{
    public int ID { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public string Email { get; set; }
    public string PhoneNumber { get; set; }

    [NotMapped]
    public List<string> ClassesTaught { get; set; }
}
  1. Add Context and Configure Model with Rules: Use fluent API to configure a model, you can override the OnModelCreating method, Fluent API configuration takes the highest priority when defining the mapping between a code entity and the corresponding database table.
    public class SchoolDbContext : DbContext
    {
        public SchoolDbContext(DbContextOptions options) : base(options)
        {
        }

        public DbSet<TeacherEntity> Teachers { get; set; }

        public DbSet<StudentEntity> Students { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Configure Teacher
            modelBuilder.Entity<TeacherEntity>().
                 ToTable("Teachers");

            modelBuilder.Entity<TeacherEntity>()
                .HasKey(x => x.ID);

           modelBuilder.Entity<TeacherEntity>().Property(t => t.Email)
                .IsRequired()
                .HasMaxLength(50);

            modelBuilder.Entity<TeacherEntity>().Property(t => t.PhoneNumber)
               .IsRequired()
               .HasMaxLength(10);

            //Configure Student
            modelBuilder.Entity<StudentEntity>().
                 ToTable("Students");
            
            modelBuilder.Entity<StudentEntity>()
                .HasKey(x => x.ID);

            modelBuilder.Entity<StudentEntity>()
               .Property(x => x.DateOfBirth)
                .IsRequired();

            modelBuilder.Entity<StudentEntity>()
               .Property(x => x.Email)
               .IsRequired()
               .HasMaxLength(50);
        }
    }

The configuration includes defining the table name, setting the primary key, and defining constraints on properties such as IsRequired and HasMaxLength.

  1. Seed Initial Data: into the database. This is done using the HasData method in the OnModelCreating method
        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<TeacherEntity>().HasData(
            new TeacherEntity
            {
                ID = 1,
                FirstName = "Ramesh",
                LastName = "Kumar",
                Email = "testramesh@gmail.com",
                PhoneNumber = "1234567890",
            },
            new TeacherEntity
            {
                ID = 2,
                FirstName = "Amit ",
                LastName = "Sharma",
                Email = "testamitsharma@gmail.com",
                PhoneNumber = "1234517890",
            });

            modelBuilder.Entity<StudentEntity>().HasData(
            new StudentEntity
            {
                ID = 1,
                FirstName = "Mohit",
                LastName = "Yadav",
                Email = "testmohit@gmail.com",
                DateOfBirth = DateTime.Now,
            },
            new StudentEntity
            {
                ID = 2,
                FirstName = "Ankit",
                LastName = "Sharma",
                Email = "testankitsharma@gmail.com",
                DateOfBirth = DateTime.Now,
            });
        }
  1. Add New Migration: Now, you can add a migration using Add-Migration. For example:
Add-Migration InitialCreate

This command will generate a migration file to apply in the database. Here is a screenshot

Migration

  1. Update Database: Finally, you can update the database with the changes by running the Update-Database
Update-Database

Here is a screenshot of the PowerShell output.

PowerShell output

Here is a screenshot from the database

from the database

Handle Schema Changes

Schema Changes Scenario 01: Let's say we want to add a new property to our Teacher entity called Gender. We can add this property to our Teacher class and update the configuration to include it. Here is a screenshot of the changes.

New Property Added

Configuration New Property

Add new migration and update the database to apply changes with the following commands.

Add-Migration AddGenderColumn
update-database AddGenderColumn

Here is a screenshot of the database after you add a new column

database after you add a new column

Schema Changes Scenario 02: Let's say we want to rename the Teacher table to Educators and also need indexing to be applied to the 'Email' column

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Rename the Teachers table to Educators
    modelBuilder.Entity<TeacherEntity>()
        .ToTable("Educators");

    // Add an index to the Educators table on the Name column
    modelBuilder.Entity<TeacherEntity>()
        .HasIndex(x => x.Email)
        .HasDatabaseName("IX_Educators_Email")
        .HasFilter(null)
        .IsUnique();
}
Add-Migration RenameTeacherTableAndAddIndexToEmail
update-database

Here is a screenshot from the database

Rename Teacher Table Add Index To Email

Push These Changes out to a test server and eventually production.

We need a SQL Script to hand over to DBA so they can apply changes in the same sequence manner. This time we will run the Script-Migration to write changes as a script rather than applied. You can also provide the range to apply script.

Script-Migration

SQL Script

Apply Migration at runtime

public static void Main(string[] args)
{
    var host = CreateHostBuilder(args).Build();

    using (var scope = host.Services.CreateScope())
    {
        var db = scope.ServiceProvider.GetRequiredService<ApplicationDbContext>();

        //To apply migrations programmatically
        db.Database.Migrate();
    }

    host.Run();
}

The source code for this article can be found on Github.

Summary

Code First Database Migration using Entity Framework. Code First is a feature of Entity Framework that allows developers to define the structure of their database using code. The code uses .NET Core 6 and Entity Framework Core 7.0. The article provides a step-by-step guide to implementing Code First Database Migration. The steps include defining a connection string and registering a context, defining model entities, configuring the model using fluent API, seeding initial data, adding a new migration, and updating the database. The code demonstrates how to add a migration using Add-Migration and update the database using Update-Database. The output includes screenshots of the migration file, PowerShell output, and the updated database.

Thanks for reading!


Posted on April 5, 2023
Profile Picture

Arun Yadav

Software Architect | Full Stack Web Developer | Cloud/Containers

Subscribe
to our Newsletter

Signup for our weekly newsletter to get the latest news, articles and update in your inbox.