DbContext & Code-First Migrations

The DbContext is the beating heart of Entity Framework Core. It acts as a session with the database, allowing you to query entities and save data. Combined with "Code-First Migrations", you can generate entire SQL database schemas directly from your C# class definitions without ever opening SQL Server Management Studio.

1. Modeling the Domain

First, we define Plain Old CLR Objects (POCOs) that represent our database tables.

public class User
{
    public int Id { get; set; } // Auto-incrementing Primary Key
    public string Email { get; set; }
    public string PasswordHash { get; set; }
    public DateTime CreatedAt { get; set; }
}

2. The ApplicationDbContext

We must subclass DbContext and define DbSet<T> properties. Each DbSet represents a physical table in SQL Server.

using Microsoft.EntityFrameworkCore;

public class ApplicationDbContext : DbContext
{
    // The constructor accepts DbContextOptions (which contains the SQL Connection String
    // we set up in Program.cs) and passes it to the base class.
    public ApplicationDbContext(DbContextOptions<ApplicationDbContext> options) : base(options)
    {
    }

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

    // Fluent API Configurations
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);
        
        // Ensure Emails are strictly unique at the database level!
        modelBuilder.Entity<User>().HasIndex(u => u.Email).IsUnique();
    }
}

3. Executing Code-First Migrations

Once your DbContext is ready, you must generate Migrations. A Migration is an auto-generated C# file that contains the instructions to translate your C# classes into raw SQL CREATE TABLE scripts.

dotnet ef migrations add InitialCreate

dotnet ef database update

4. Interview Mastery

Q: "Should we run `context.Database.Migrate()` inside `Program.cs` so our API automatically updates the database schema when the server boots up?"

Architect Answer: "Absolutely not in a production environment. While it is incredibly convenient for a solo developer running a local API, it is a catastrophic anti-pattern for enterprise deployment. If we deploy our API to an Azure Server Farm composed of 5 load-balanced API servers, all 5 servers will boot simultaneously. All 5 will detect a pending migration and attempt to execute `ALTER TABLE` scripts concurrently against the identical SQL Database. This causes immediate locking failures, deadlocks, and can corrupt the database. Production migrations should ALWAYS be executed as a dedicated, isolated step in the CI/CD pipeline (e.g., GitHub Actions) BEFORE the APIs are deployed."