Entity Framework Core Migration

Entity Framework (EF) Core migrations are a feature that allows developers to manage database schema changes in a version-controlled way. Instead of manually writing and executing SQL scripts, migrations automate the process of incrementally updating the database schema to keep it in sync with the application's C# data model.

🔍 What is EF Core Migration?

EF Core Migration is a feature that allows you to evolve your database schema over time as your model changes. It keeps your database in sync with your C# domain models.

How migrations work

Generate Migration: When a developer changes the EF Core model (adds an entity, property, or relationship), they run a command to create a new migration.

Compare and Scaffold: EF Core compares the current model with the snapshot of the last migration and generates migration files with Up() and Down() methods.

Up Method: Contains C# code that applies schema changes to the database.

Down Method: Contains C# code that reverts the applied changes.

Apply Migration: EF Core runs the SQL defined in the Up() method and tracks applied migrations using the __EFMigrationsHistory table.

Example of EF Core migrations

Prerequisites

Required Packages: Install Microsoft.EntityFrameworkCore.SqlServer (or another provider) and Microsoft.EntityFrameworkCore.Design.

Step 1: Define initial model and context

Create Context: Define a DbContext class with a DbSet for your entity.

// Models/AppDbContext.cs
public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options)
        : base(options)
    {
    }

    public DbSet<User> Users { get; set; }
}
  

Create Entity: Define a basic entity class.

// Models/User.cs
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
}
  

Step 2: Add initial migration

Run Command:

dotnet ef migrations add InitialCreate

Step 3: Update database

Apply Migration:

dotnet ef database update

This creates the database with Users table and the history table.

Step 4: Make a model change

Add Property: Modify the entity to include a new field.

// Models/User.cs (updated)
public class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; } // New property
}
  

Step 5: Add new migration

Run Command:

dotnet ef migrations add AddEmailToUser

Step 6: Update database again

Run Command:

dotnet ef database update

The new column is added without data loss.

✅ Advantages

• Schema evolves directly from code.
• Ideal for new projects (greenfield).
• Strongly typed models with LINQ support.
• Cross-platform and supports multiple databases.
• Version Control: Migrations are code files that can be version-controlled, allowing team collaboration and tracking of database changes alongside code changes.
• Automated Updates: Developers can focus on the application's domain model, with EF Core automatically handling the necessary database schema updates.
• Consistency: Migrations ensure that development, testing, and production databases all have a consistent schema, minimizing discrepancies
• Data Safety: EF Core migrations are designed to apply incremental updates, so existing data is not lost when applying schema changes like adding a new column.
• Auto SQL Generation: Migrations generate SQL scripts, saving developers from writing manual, potentially error-prone, SQL for every change

⚠️ Disadvantages

• Migration management can be tricky in production.
• Overhead for simple projects: might be quicker to write a few SQL scripts manually.
• Limited for complex scenarios: advanced DB operations may need manual intervention or raw SQL.
• Performance impact: generally minimal but relevant in extreme low-latency systems.
• Schema rigidity: code-first assumes same schema across databases, complicating complex multi-tenant setups.
• Learning curve: requires understanding EF Core and database concepts for advanced use.
• Setup Overhead: Small projects might not benefit from migrations.
• Complex Cases: Advanced database changes may require manual SQL.
• Performance Consideration: Slight overhead when applying migrations.
• Schema Uniformity: Assumes a single schema across all environments.
• Learning Curve: Requires understanding EF Core and databases.

🧭 Best Practices

• Use migrations for safe schema changes.
• Scope DbContext per request in web apps.
• Use AsNoTracking() for read-only queries.
• Combine EF Core with raw SQL for performance-critical queries.
• Log SQL queries during development.
• Use Descriptive Names: Name migrations clearly for readability. e.g., AddProductTable, RenameDescriptionColumn.
• Keep Changes Small: Smaller migrations are easier to manage.Focused migrations are easier to review and roll back
• Test First: Run migrations in development and staging that mirror production. Validate migrations in staging environments.
• Review Code: Inspect the generated migration before applying.
• Generate SQL for Production: Use scripts for safer deployment.
• Resolve Conflicts Early: Sync migrations regularly in teams.
• Separate Projects: Use a dedicated data project for large apps.
• Resolve merge conflicts promptly: pull latest migrations before adding new ones.
• Use a separate project for migrations in large applications: keep data layer in a class library.

🔒 Precautions

• Avoid Destructive Actions: Dropping objects can cause data loss.
• Handle Data Migration: Modify migrations to preserve existing data when renaming or altering columns.
• Do Not Delete Applied Migrations: Use rollback commands instead.
• Do Not Auto-Migrate in Production: Apply migrations manually.
• Backup First: Always back up databases before applying changes.
• Watch out for lazy loading and N+1 query issues.
• Secure connection strings (e.g., Azure Key Vault).
• Ensure proper indexing for large datasets.
• Test migrations in staging before production.
• Destructive changes: dropping tables/columns is permanent—have backups and a rollback plan.
• Data migration: renames or type changes may drop & recreate columns—modify migration files if needed to preserve data.
• Rollbacks: never remove a migration already applied to production; create a new migration to revert changes if necessary.
• Production deployment: avoid auto-applying migrations on app startup in production; prefer controlled application of migrations.

🎯 Summary

EF Core Code First with migrations is a powerful way to manage database schema from your code. It’s best suited for new projects and offers flexibility and control over schema evolution.