Beginner

using Microsoft.EntityFrameworkCore;          // => EF Core namespace; provides DbContext, DbSet<T>
 
namespace MyApp.Infrastructure;
 
// Primary constructor syntax (C# 12); injects DbContextOptions
public class AppDbContext(DbContextOptions<AppDbContext> options) : DbContext(options)
{
    // DbSet<T> exposes LINQ-queryable table; each property maps to one table
    public DbSet<UserModel> Users => Set<UserModel>();
    // => Users property maps to "Users" table (or configured table name)
    // => Set<T>() is preferred over field-backed DbSet for testability
 
    public DbSet<ProductModel> Products => Set<ProductModel>();
    // => Products property maps to "Products" table
 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        // => Called once at startup to build the model
        // => Fluent API here overrides data annotation defaults
 
        modelBuilder.Entity<UserModel>(entity =>
        {
            entity.HasKey(e => e.Id);
            // => Declares Id as primary key; EF generates PK constraint
            entity.HasIndex(e => e.Email).IsUnique();
            // => Creates UNIQUE INDEX on Email column
            // => Prevents duplicate emails without application-layer checks
            entity.Property(e => e.Email).HasMaxLength(255);
            // => Maps to VARCHAR(255); without this EF uses TEXT (no length limit)
        });
    }
}

# Run from project directory containing the .csproj file
dotnet ef migrations add InitialCreate
# => Compares current DbContext model against last snapshot (none exists yet)
# => Generates three files in Migrations/ folder:
# =>   20260327000000_InitialCreate.cs          (migration logic)
# =>   20260327000000_InitialCreate.Designer.cs (EF metadata snapshot for this migration)
# =>   AppDbContextModelSnapshot.cs             (current model snapshot, updated on every add)
 
dotnet ef migrations add AddProductTable
# => Compares updated DbContext (with Products DbSet) against current snapshot
# => Generates only the DIFF - changes since InitialCreate
# => If no model changes detected: "No changes detected" error; nothing generated

// Generated file: Migrations/20260327000000_InitialCreate.cs
using Microsoft.EntityFrameworkCore.Migrations;  // => MigrationBuilder lives here
 
#nullable disable                                 // => Suppresses nullable warnings in generated code
 
namespace MyApp.Migrations;
 
public partial class InitialCreate : Migration   // => partial class; Designer.cs adds metadata
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // => EF Core fills this with CreateTable, CreateIndex, etc. calls
        // => Runs when: dotnet ef database update
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // => Reverse of Up(); EF Core generates DropTable, DropIndex, etc.
        // => Runs when: dotnet ef database update PreviousMigration
    }
}

using Microsoft.EntityFrameworkCore.Migrations;  // => Required namespace for MigrationBuilder
 
namespace MyApp.Migrations;
 
public partial class AddEmailToUsers : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // => Runs on: dotnet ef database update (forward migration)
        // => Purpose: apply the intended schema change
 
        migrationBuilder.AddColumn<string>(
            name: "email",                        // => Column name in database
            table: "users",                       // => Target table
            type: "character varying(255)",       // => SQL type for PostgreSQL
            maxLength: 255,                       // => Reflected in EF model validation
            nullable: false,                      // => NOT NULL constraint
            defaultValue: "");                    // => Required default for existing rows
        // => SQL: ALTER TABLE users ADD COLUMN email VARCHAR(255) NOT NULL DEFAULT ''
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // => Runs on: dotnet ef database update AddEmailToUsers~ (revert)
        // => Purpose: undo exactly what Up() did, in reverse order
 
        migrationBuilder.DropColumn(
            name: "email",                        // => Column to remove
            table: "users");                      // => Target table
        // => SQL: ALTER TABLE users DROP COLUMN email
        // => All data in the email column is PERMANENTLY DELETED on Down()
    }
}

using System;                                    // => Guid lives here
using Microsoft.EntityFrameworkCore.Migrations;  // => MigrationBuilder
 
namespace MyApp.Migrations;
 
public partial class CreateProductsTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "products",                    // => Table name in database
            columns: table => new                // => Anonymous object; each property = one column
            {
                id = table.Column<Guid>(
                    type: "uuid",                // => PostgreSQL UUID type
                    nullable: false),            // => NOT NULL; primary keys cannot be null
                // => Column: id UUID NOT NULL
 
                name = table.Column<string>(
                    type: "character varying(200)",
                    maxLength: 200,              // => EF model validation limit
                    nullable: false),            // => Required field
                // => Column: name VARCHAR(200) NOT NULL
 
                price = table.Column<decimal>(
                    type: "numeric(19,4)",
                    precision: 19,               // => Total significant digits
                    scale: 4,                    // => Digits after decimal point
                    nullable: false),            // => NOT NULL; every product has a price
                // => Column: price NUMERIC(19,4) NOT NULL
 
                created_at = table.Column<DateTimeOffset>(
                    type: "timestamp with time zone",
                    nullable: false),            // => Audit timestamp; always populated
                // => Column: created_at TIMESTAMPTZ NOT NULL
            },
            constraints: table =>               // => Table-level constraints (PK, FK, CHECK)
            {
                table.PrimaryKey("pk_products", x => x.id);
                // => Constraint: PRIMARY KEY (id) NAMED pk_products
                // => Naming convention: pk_{table_name}
            });
        // => Final SQL (PostgreSQL):
        // => CREATE TABLE products (
        // =>   id UUID NOT NULL,
        // =>   name VARCHAR(200) NOT NULL,
        // =>   price NUMERIC(19,4) NOT NULL,
        // =>   created_at TIMESTAMPTZ NOT NULL,
        // =>   CONSTRAINT pk_products PRIMARY KEY (id)
        // => );
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "products");
        // => SQL: DROP TABLE products
        // => All rows and the table structure are permanently removed
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddDescriptionToProducts : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "description",                 // => New column name
            table: "products",                   // => Existing table to alter
            type: "character varying(500)",      // => SQL type
            maxLength: 500,                      // => EF model length validation
            nullable: false,                     // => NOT NULL constraint
            defaultValue: "");                   // => Default for existing rows (required for NOT NULL)
        // => SQL: ALTER TABLE products
        // =>        ADD COLUMN description VARCHAR(500) NOT NULL DEFAULT ''
        // => Existing rows get empty string; change default after population if needed
 
        migrationBuilder.AddColumn<DateTimeOffset>(
            name: "updated_at",
            table: "products",
            type: "timestamp with time zone",
            nullable: true);                     // => Nullable: no default needed for existing rows
        // => SQL: ALTER TABLE products ADD COLUMN updated_at TIMESTAMPTZ NULL
        // => Existing rows get NULL; application sets value on next update
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "description", table: "products");
        // => SQL: ALTER TABLE products DROP COLUMN description
        // => Irreversible data loss for description values if rows exist
 
        migrationBuilder.DropColumn(name: "updated_at", table: "products");
        // => SQL: ALTER TABLE products DROP COLUMN updated_at
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class RemoveLegacyColumns : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "legacy_code",                 // => Column to remove
            table: "products");                  // => Target table
        // => SQL: ALTER TABLE products DROP COLUMN legacy_code
        // => All values in legacy_code are permanently deleted from every row
        // => Cannot be rolled back without restoring from backup
 
        migrationBuilder.DropColumn(
            name: "deprecated_flag",
            table: "products");
        // => SQL: ALTER TABLE products DROP COLUMN deprecated_flag
        // => Drop columns individually; there is no multi-column drop shorthand
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // => Restoring a dropped column recreates it with NULL for all existing rows
        // => Original data is gone; Down() can only add the column back as nullable
 
        migrationBuilder.AddColumn<string>(
            name: "legacy_code",
            table: "products",
            type: "text",
            nullable: true);                     // => Must be nullable; no original data to restore
        // => SQL: ALTER TABLE products ADD COLUMN legacy_code TEXT NULL
 
        migrationBuilder.AddColumn<bool>(
            name: "deprecated_flag",
            table: "products",
            type: "boolean",
            nullable: true);
        // => SQL: ALTER TABLE products ADD COLUMN deprecated_flag BOOLEAN NULL
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddProductIndexes : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateIndex(
            name: "ix_products_category",        // => Index name; convention: ix_{table}_{column}
            table: "products",                   // => Table to index
            column: "category");                 // => Single column to index
        // => SQL: CREATE INDEX ix_products_category ON products (category)
        // => Speeds up: WHERE category = 'Electronics'
        // => Does NOT enforce uniqueness
 
        migrationBuilder.CreateIndex(
            name: "ix_products_category_price",
            table: "products",
            columns: new[] { "category", "price" });
                                                 // => Composite index on two columns
        // => SQL: CREATE INDEX ix_products_category_price ON products (category, price)
        // => Useful for: WHERE category = 'X' ORDER BY price
        // => Column ORDER matters: most selective filter first
 
        migrationBuilder.CreateIndex(
            name: "ix_products_sku",
            table: "products",
            column: "sku",
            unique: true);                       // => UNIQUE constraint via index
        // => SQL: CREATE UNIQUE INDEX ix_products_sku ON products (sku)
        // => Prevents duplicate SKU values at the database level
        // => EF Core maps this to HasIndex(...).IsUnique() in OnModelCreating
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(name: "ix_products_sku", table: "products");
        // => SQL: DROP INDEX ix_products_sku
        // => Removes UNIQUE constraint; duplicates can now exist
 
        migrationBuilder.DropIndex(name: "ix_products_category_price", table: "products");
        migrationBuilder.DropIndex(name: "ix_products_category", table: "products");
        // => Remove indexes in reverse order of creation (defensive practice)
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddOrderItemsForeignKey : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Step 1: Create supporting index on foreign key column first
        migrationBuilder.CreateIndex(
            name: "ix_order_items_order_id",    // => Index name for the FK column
            table: "order_items",               // => Child table
            column: "order_id");                // => FK column to index
        // => SQL: CREATE INDEX ix_order_items_order_id ON order_items (order_id)
        // => Index is critical: without it, deleting an order causes full scan of order_items
 
        // Step 2: Add the foreign key constraint
        migrationBuilder.AddForeignKey(
            name: "fk_order_items_orders_order_id",
                                                // => Constraint name; convention: fk_{child}_{parent}_{column}
            table: "order_items",               // => Child table (holds FK column)
            column: "order_id",                 // => FK column in child table
            principalTable: "orders",           // => Parent table (referenced)
            principalColumn: "id",              // => Referenced column in parent table (usually PK)
            onDelete: ReferentialAction.Cascade);
                                                // => ON DELETE CASCADE: deleting order deletes its items
        // => SQL: ALTER TABLE order_items
        // =>        ADD CONSTRAINT fk_order_items_orders_order_id
        // =>        FOREIGN KEY (order_id) REFERENCES orders (id)
        // =>        ON DELETE CASCADE
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Drop FK constraint BEFORE dropping the index it depends on
        migrationBuilder.DropForeignKey(
            name: "fk_order_items_orders_order_id",
            table: "order_items");
        // => SQL: ALTER TABLE order_items DROP CONSTRAINT fk_order_items_orders_order_id
 
        migrationBuilder.DropIndex(
            name: "ix_order_items_order_id",
            table: "order_items");
        // => SQL: DROP INDEX ix_order_items_order_id
    }
}

# Apply all pending migrations (forward to latest)
dotnet ef database update
# => Queries __EFMigrationsHistory to find last applied migration
# => Runs Up() of each unapplied migration in timestamp order
# => Inserts a row into __EFMigrationsHistory for each applied migration
# => Output: Applying migration '20260327000001_InitialCreate'
# => Output: Done.
 
# Apply up to a specific migration (partial update)
dotnet ef database update AddEmailToUsers
# => Applies only migrations up to and including AddEmailToUsers
# => Stops before any later migrations
# => Useful for staged rollouts or debugging a specific migration
 
# Apply to empty database (fresh install)
dotnet ef database update --connection "Host=localhost;Database=myapp_dev;Username=admin;Password=secret"
# => Uses explicit connection string instead of appsettings.json
# => Creates __EFMigrationsHistory table if it does not exist
# => Applies all migrations from the beginning

// What EF Core does internally when database update runs:
// 1. Opens database connection
// 2. Creates __EFMigrationsHistory if missing:
//    CREATE TABLE IF NOT EXISTS "__EFMigrationsHistory" (
//      "MigrationId" VARCHAR(150) NOT NULL,   -- => e.g. "20260327000001_InitialCreate"
//      "ProductVersion" VARCHAR(32) NOT NULL, -- => e.g. "10.0.1"
//      CONSTRAINT "PK___EFMigrationsHistory" PRIMARY KEY ("MigrationId")
//    );
// 3. Queries applied migrations:
//    SELECT "MigrationId" FROM "__EFMigrationsHistory";
// 4. For each pending migration, runs Up() then inserts:
//    INSERT INTO "__EFMigrationsHistory" VALUES ('20260327_InitialCreate', '10.0.1');

# Revert to a specific previous migration
dotnet ef database update InitialCreate
# => Runs Down() for each migration AFTER InitialCreate, in reverse order
# => Example: if applied order was InitialCreate -> AddEmail -> AddIndex
# =>   Runs Down() of AddIndex first
# =>   Runs Down() of AddEmail second
# =>   Stops; InitialCreate remains applied
# => Removes the reverted rows from __EFMigrationsHistory
 
# Revert ALL migrations (empty database schema)
dotnet ef database update 0
# => Runs Down() for ALL applied migrations in reverse order
# => __EFMigrationsHistory becomes empty (table remains but no rows)
# => All tables created by migrations are dropped
# => WARNING: All data is lost; use only in development or testing
 
# Check current state before reverting
dotnet ef migrations list
# => Shows each migration with [applied] or [pending] tag
# => Output:
# =>   20260327000001_InitialCreate [applied]
# =>   20260327000002_AddEmailToUsers [applied]
# =>   20260327000003_AddIndex [applied]  <-- current tip

# Remove the last unapplied migration
dotnet ef migrations remove
# => Deletes Migrations/20260327000003_AddIndex.cs
# => Deletes Migrations/20260327000003_AddIndex.Designer.cs
# => Reverts AppDbContextModelSnapshot.cs to the state before AddIndex was generated
# => Output: Removing migration '20260327000003_AddIndex'
# => Output: Done.
 
# ERROR case: trying to remove an already-applied migration
dotnet ef migrations remove
# => Output: The migration '20260327000003_AddIndex' has already been applied to the database.
# => Output: Revert it and try again.
# => Fix: run `dotnet ef database update PreviousMigration` first, then `migrations remove`
 
# Verify the removal
dotnet ef migrations list
# => Output:
# =>   20260327000001_InitialCreate [applied]
# =>   20260327000002_AddEmailToUsers [applied]
# => AddIndex is gone from the list

// After remove, the snapshot reverts.
// AppDbContextModelSnapshot.cs no longer contains the AddIndex configuration.
// Your DbContext model must also be reverted (remove the property/index that caused AddIndex).
// If you leave the DbContext change but remove the migration, the NEXT add will regenerate it.

# List all migrations with their applied/pending status
dotnet ef migrations list
# => Output:
# =>   20260101000001_InitialCreate (Applied)
# =>   20260201000001_AddEmailToUsers (Applied)
# =>   20260301000001_AddProductTable (Applied)
# =>   20260327000001_AddIndexes (Pending)
# => Applied = row exists in __EFMigrationsHistory
# => Pending = migration file exists but not yet run
 
# List against a specific environment database
dotnet ef migrations list --connection "Host=prod-db.example.com;Database=myapp;Username=ro;Password=secret"
# => Connects to production (or staging) DB to check actual applied migrations
# => Useful for verifying deployment status without SSH access
 
# List with verbose output (shows project and startup project)
dotnet ef migrations list --verbose
# => Output also includes:
# =>   Using project 'MyApp.Infrastructure'
# =>   Using startup project 'MyApp.Api'
# =>   Build started...
# =>   Build succeeded.

-- Structure of __EFMigrationsHistory (PostgreSQL)
-- => EF Core creates this automatically on first database update
SELECT column_name, data_type, character_maximum_length
FROM information_schema.columns
WHERE table_name = '__EFMigrationsHistory';
-- => column_name: MigrationId  | data_type: character varying | max_length: 150
-- => column_name: ProductVersion | data_type: character varying | max_length: 32
 
-- Querying applied migrations directly
SELECT "MigrationId", "ProductVersion"
FROM "__EFMigrationsHistory"
ORDER BY "MigrationId";
-- => Output:
-- =>   MigrationId                              | ProductVersion
-- =>   20260101000001_InitialCreate             | 10.0.1
-- =>   20260201000001_AddEmailToUsers           | 10.0.1
-- =>   20260301000001_AddProductTable           | 10.0.1
 
-- Checking if a specific migration was applied
SELECT COUNT(*) FROM "__EFMigrationsHistory"
WHERE "MigrationId" = '20260327000001_AddIndexes';
-- => 0 = not applied (pending)
-- => 1 = applied

// EF Core inserts into __EFMigrationsHistory after each successful Up() call:
// INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
// VALUES ('20260327000001_AddIndexes', '10.0.1');
// => MigrationId format: {timestamp}_{ClassName}
// => ProductVersion: the EF Core NuGet package version (e.g. "10.0.1")
 
// EF Core deletes from __EFMigrationsHistory during revert (Down()):
// DELETE FROM "__EFMigrationsHistory"
// WHERE "MigrationId" = '20260327000001_AddIndexes';
// => Row removed means migration is no longer tracked as applied

// File: Migrations/AppDbContextModelSnapshot.cs
// => Auto-generated; DO NOT edit manually
// => Updated every time you run: dotnet ef migrations add
 
// <auto-generated />
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using MyApp.Infrastructure;
 
namespace MyApp.Migrations;
 
[DbContext(typeof(AppDbContext))]              // => Links snapshot to specific DbContext
partial class AppDbContextModelSnapshot : ModelSnapshot
{
    protected override void BuildModel(ModelBuilder modelBuilder)
    {
#pragma warning disable 612, 618             // => Suppress obsolete API warnings in generated code
        modelBuilder
            .HasAnnotation("ProductVersion", "10.0.1")
            // => Tracks EF Core version that generated this snapshot
            .HasAnnotation("Relational:MaxIdentifierLength", 63);
            // => PostgreSQL identifier limit (63 chars for table/column names)
 
        modelBuilder.Entity("MyApp.Infrastructure.Models.UserModel", b =>
        {
            b.Property<Guid>("Id")            // => Matches entity property type
                .ValueGeneratedOnAdd()        // => Database generates value on INSERT
                .HasColumnType("uuid")        // => PostgreSQL-specific type annotation
                .HasColumnName("id");         // => Actual column name in database
 
            b.HasKey("Id");                   // => Primary key declaration
            b.ToTable("users");               // => Table mapping
        });
    }
}

// File: Migrations/20260327000001_InitialCreate.Designer.cs
// => Auto-generated alongside the migration class; DO NOT edit manually
 
// <auto-generated />
using System;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Infrastructure;
using Microsoft.EntityFrameworkCore.Migrations;
using MyApp.Infrastructure;
 
namespace MyApp.Migrations;
 
[DbContext(typeof(AppDbContext))]            // => Associates metadata with AppDbContext
[Migration("20260327000001_InitialCreate")] // => Links metadata to specific migration ID
partial class InitialCreate                  // => Same partial class as InitialCreate.cs
{
    protected override void BuildTargetModel(ModelBuilder modelBuilder)
    {
        // => Snapshot of model state AFTER this migration was applied
        // => Used by dotnet ef migrations script to generate SQL between two points
 
        modelBuilder
            .HasAnnotation("ProductVersion", "10.0.1")
            .HasAnnotation("Relational:MaxIdentifierLength", 63);
 
        modelBuilder.Entity("MyApp.Infrastructure.Models.UserModel", b =>
        {
            b.Property<Guid>("Id")
                .ValueGeneratedOnAdd()
                .HasColumnType("uuid")
                .HasColumnName("id");         // => Column name at time of InitialCreate
 
            b.HasKey("Id");
            b.ToTable("users");
        });
        // => If AddEmailToUsers later added the email column,
        // => THIS snapshot (Designer.cs for InitialCreate) does NOT include email
        // => The Designer.cs for AddEmailToUsers DOES include email
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddUniqueConstraints : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Approach 1: Unique index (most common; supports queries on the column)
        migrationBuilder.CreateIndex(
            name: "ix_users_email",
            table: "users",
            column: "email",
            unique: true);                       // => UNIQUE constraint via index
        // => SQL: CREATE UNIQUE INDEX ix_users_email ON users (email)
        // => Rejects INSERT/UPDATE that would create a duplicate email
        // => Also makes queries on email fast (index is used for WHERE email = ?)
 
        // Approach 2: Composite unique constraint (unique combination of columns)
        migrationBuilder.CreateIndex(
            name: "ix_order_items_order_product",
            table: "order_items",
            columns: new[] { "order_id", "product_id" },
                                                 // => Composite uniqueness
            unique: true);
        // => SQL: CREATE UNIQUE INDEX ix_order_items_order_product
        // =>        ON order_items (order_id, product_id)
        // => Each (order_id, product_id) pair must be unique
        // => Same product can appear in different orders (order_id differs)
        // => Same order cannot have the same product twice
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(name: "ix_order_items_order_product", table: "order_items");
        // => SQL: DROP INDEX ix_order_items_order_product
        // => Uniqueness no longer enforced; duplicate (order, product) pairs now possible
 
        migrationBuilder.DropIndex(name: "ix_users_email", table: "users");
        // => SQL: DROP INDEX ix_users_email
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddAuditColumns : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<string>(
            name: "created_by",
            table: "users",
            type: "character varying(255)",
            maxLength: 255,
            nullable: false,
            defaultValue: "system");             // => Default for existing rows
        // => SQL: ALTER TABLE users
        // =>        ADD COLUMN created_by VARCHAR(255) NOT NULL DEFAULT 'system'
        // => All existing rows get "system" as the created_by value
        // => New rows must supply a value (or rely on application-side default)
 
        migrationBuilder.AddColumn<string>(
            name: "updated_by",
            table: "users",
            type: "character varying(255)",
            maxLength: 255,
            nullable: false,
            defaultValue: "system");
        // => SQL: ALTER TABLE users
        // =>        ADD COLUMN updated_by VARCHAR(255) NOT NULL DEFAULT 'system'
 
        migrationBuilder.AddColumn<DateTimeOffset>(
            name: "deleted_at",
            table: "users",
            type: "timestamp with time zone",
            nullable: true);                     // => Nullable; no default needed
        // => SQL: ALTER TABLE users ADD COLUMN deleted_at TIMESTAMPTZ NULL
        // => Null means "not deleted"; non-null means soft-deleted with timestamp
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "deleted_at", table: "users");
        migrationBuilder.DropColumn(name: "updated_by", table: "users");
        migrationBuilder.DropColumn(name: "created_by", table: "users");
        // => Drop in reverse order of creation (defensive practice)
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class CreateOrdersWithUuid : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.CreateTable(
            name: "orders",
            columns: table => new
            {
                id = table.Column<Guid>(
                    type: "uuid",                // => PostgreSQL UUID type; 16 bytes
                    nullable: false),            // => Primary key; never null
                // => C# Guid maps to PostgreSQL uuid
                // => Default value NOT set here; application generates the Guid
 
                customer_id = table.Column<Guid>(
                    type: "uuid",
                    nullable: false),            // => Foreign key reference; uuid type
                // => Matches id type of customers table for FK relationship
 
                total = table.Column<decimal>(
                    type: "numeric(19,4)",
                    precision: 19,
                    scale: 4,
                    nullable: false),
 
                created_at = table.Column<DateTimeOffset>(
                    type: "timestamp with time zone",
                    nullable: false),
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_orders", x => x.id);
                // => PRIMARY KEY (id); uuid column as PK
                // => No sequence/identity needed; application supplies Guid.NewGuid()
            });
        // => Final SQL:
        // => CREATE TABLE orders (
        // =>   id UUID NOT NULL,
        // =>   customer_id UUID NOT NULL,
        // =>   total NUMERIC(19,4) NOT NULL,
        // =>   created_at TIMESTAMPTZ NOT NULL,
        // =>   CONSTRAINT pk_orders PRIMARY KEY (id)
        // => );
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "orders");
        // => SQL: DROP TABLE orders
    }
}

// In your entity class, use Guid for the Id property:
public class OrderModel
{
    public Guid Id { get; set; } = Guid.NewGuid();
    // => Guid.NewGuid() generates a new UUID on object creation
    // => Client generates the ID before the INSERT; no round-trip needed to get ID
    // => Format example: 550e8400-e29b-41d4-a716-446655440000
 
    public Guid CustomerId { get; set; }         // => FK; no default (must be set by caller)
    public decimal Total { get; set; }
    public DateTimeOffset CreatedAt { get; set; }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddTimestampDefaults : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<DateTimeOffset>(
            name: "created_at",
            table: "products",
            type: "timestamp with time zone",
            nullable: false,
            defaultValueSql: "now()");           // => Database function as default
        // => SQL: ALTER TABLE products
        // =>        ADD COLUMN created_at TIMESTAMPTZ NOT NULL DEFAULT now()
        // => Any INSERT that omits created_at gets the current timestamp automatically
        // => Bypassing EF Core (raw SQL INSERT) still gets correct timestamp
 
        migrationBuilder.AddColumn<DateTimeOffset>(
            name: "updated_at",
            table: "products",
            type: "timestamp with time zone",
            nullable: false,
            defaultValueSql: "now()");           // => Same default for initial value
        // => SQL: ALTER TABLE products
        // =>        ADD COLUMN updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
        // => Application code must set updated_at on every UPDATE
        // => (Or use a trigger; EF Core does not auto-update timestamps by default)
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "updated_at", table: "products");
        migrationBuilder.DropColumn(name: "created_at", table: "products");
    }
}

// In OnModelCreating, mirror the SQL default so EF Core knows about it:
modelBuilder.Entity<ProductModel>(entity =>
{
    entity.Property(e => e.CreatedAt)
        .HasDefaultValueSql("now()");            // => Tells EF Core to use now() as SQL default
        // => EF Core omits created_at from INSERT if value equals CLR default (DateTimeOffset.MinValue)
        // => Database default fills it automatically
});

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddStatusColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // String storage pattern (preferred for readability)
        migrationBuilder.AddColumn<string>(
            name: "status",
            table: "users",
            type: "character varying(50)",       // => Bounded string; enum values are short
            maxLength: 50,
            nullable: false,
            defaultValue: "ACTIVE");             // => Default to active status
        // => SQL: ALTER TABLE users
        // =>        ADD COLUMN status VARCHAR(50) NOT NULL DEFAULT 'ACTIVE'
        // => Stored as: 'ACTIVE', 'INACTIVE', 'SUSPENDED'
        // => Readable in raw SQL queries; safe to add new enum values later
 
        // Integer storage pattern (compact, less readable)
        migrationBuilder.AddColumn<int>(
            name: "role",
            table: "users",
            type: "integer",
            nullable: false,
            defaultValue: 0);                    // => 0 maps to first enum value (e.g. Guest)
        // => SQL: ALTER TABLE users
        // =>        ADD COLUMN role INTEGER NOT NULL DEFAULT 0
        // => Stored as: 0, 1, 2 (opaque without enum definition)
        // => RISK: reordering enum values in C# changes meaning of existing data
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(name: "role", table: "users");
        migrationBuilder.DropColumn(name: "status", table: "users");
    }
}

// In OnModelCreating, configure string conversion:
modelBuilder.Entity<UserModel>(entity =>
{
    entity.Property(e => e.Status).HasConversion(
        v => v.ToString().ToUpperInvariant(),    // => C# enum -> DB string: UserStatus.Active -> "ACTIVE"
        v => Enum.Parse<UserStatus>(v, ignoreCase: true));
                                                 // => DB string -> C# enum: "ACTIVE" -> UserStatus.Active
    // => HasConversion teaches EF Core to translate between C# enum and database string
});

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddCheckConstraints : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Add CHECK constraint using raw SQL (EF Core does not have a typed API)
        migrationBuilder.Sql(
            "ALTER TABLE products ADD CONSTRAINT chk_products_price_positive " +
            "CHECK (price > 0)");
        // => SQL: ALTER TABLE products
        // =>        ADD CONSTRAINT chk_products_price_positive CHECK (price > 0)
        // => INSERT INTO products (price) VALUES (-5) => ERROR: check constraint violated
        // => Constraint name convention: chk_{table}_{description}
 
        migrationBuilder.Sql(
            "ALTER TABLE products ADD CONSTRAINT chk_products_quantity_non_negative " +
            "CHECK (quantity IS NULL OR quantity >= 0)");
        // => SQL: ALTER TABLE products
        // =>        ADD CONSTRAINT chk_products_quantity_non_negative
        // =>        CHECK (quantity IS NULL OR quantity >= 0)
        // => NULL is allowed (quantity is optional); non-null quantity must be >= 0
 
        migrationBuilder.Sql(
            "ALTER TABLE users ADD CONSTRAINT chk_users_email_format " +
            "CHECK (email LIKE '%@%')");
        // => SQL: ALTER TABLE users
        // =>        ADD CONSTRAINT chk_users_email_format CHECK (email LIKE '%@%')
        // => Basic format check; not a full email validation but catches obvious errors
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(
            "ALTER TABLE users DROP CONSTRAINT IF EXISTS chk_users_email_format");
        // => IF EXISTS prevents error if constraint was never added (safe rollback)
 
        migrationBuilder.Sql(
            "ALTER TABLE products DROP CONSTRAINT IF EXISTS chk_products_quantity_non_negative");
 
        migrationBuilder.Sql(
            "ALTER TABLE products DROP CONSTRAINT IF EXISTS chk_products_price_positive");
        // => Drop in reverse order; constraints are independent but consistent ordering is good practice
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class AddCompositeIndexes : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Index for: WHERE user_id = ? ORDER BY created_at DESC
        migrationBuilder.CreateIndex(
            name: "ix_expenses_user_id_created_at",
            table: "expenses",
            columns: new[] { "user_id", "created_at" });
                                                  // => Column order: user_id first, created_at second
        // => SQL: CREATE INDEX ix_expenses_user_id_created_at
        // =>        ON expenses (user_id, created_at)
        // => Benefits: WHERE user_id = X (uses index)
        // =>            WHERE user_id = X AND created_at > Y (uses index fully)
        // => Does NOT benefit: WHERE created_at > Y (user_id missing; partial index scan only)
 
        // Covering index: includes extra column to avoid table lookup
        migrationBuilder.CreateIndex(
            name: "ix_expenses_user_id_date_amount",
            table: "expenses",
            columns: new[] { "user_id", "date", "amount" });
        // => SQL: CREATE INDEX ix_expenses_user_id_date_amount
        // =>        ON expenses (user_id, date, amount)
        // => For: SELECT amount FROM expenses WHERE user_id = ? AND date = ?
        // => Index contains all needed columns; no heap lookup required (covering index)
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropIndex(name: "ix_expenses_user_id_date_amount", table: "expenses");
        migrationBuilder.DropIndex(name: "ix_expenses_user_id_created_at", table: "expenses");
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class CreateTagsJunctionTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create the lookup/reference table first
        migrationBuilder.CreateTable(
            name: "tags",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                name = table.Column<string>(type: "character varying(100)", maxLength: 100, nullable: false),
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_tags", x => x.id);
                // => Tags is the reference table; products can have many tags
            });
 
        // Create the junction table with composite primary key
        migrationBuilder.CreateTable(
            name: "product_tags",                // => Junction table name: {entity1}_{entity2}
            columns: table => new
            {
                product_id = table.Column<Guid>(type: "uuid", nullable: false),
                // => FK to products table
                tag_id = table.Column<Guid>(type: "uuid", nullable: false),
                // => FK to tags table
                // => No surrogate PK here; (product_id, tag_id) is the composite PK
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_product_tags", x => new { x.product_id, x.tag_id });
                // => Composite PK prevents duplicate (product, tag) associations
                // => Equivalent to: PRIMARY KEY (product_id, tag_id)
 
                table.ForeignKey("fk_product_tags_products_product_id",
                    x => x.product_id, "products", "id",
                    onDelete: ReferentialAction.Cascade);
                // => Deleting a product removes all its tag associations
 
                table.ForeignKey("fk_product_tags_tags_tag_id",
                    x => x.tag_id, "tags", "id",
                    onDelete: ReferentialAction.Cascade);
                // => Deleting a tag removes all product associations for that tag
            });
 
        migrationBuilder.CreateIndex(name: "ix_product_tags_tag_id", table: "product_tags", column: "tag_id");
        // => Index on tag_id for reverse lookups: products with a given tag
        // => product_id is already covered by the composite PK index
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "product_tags");  // => Drop junction table first (has FKs)
        migrationBuilder.DropTable(name: "tags");          // => Then drop referenced table
    }
}

// In OnModelCreating, configure seed data:
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.Entity<CategoryModel>().HasData(
        new CategoryModel { Id = 1, Name = "Electronics", Slug = "electronics" },
        // => Row with Id=1 will be seeded
        // => Id must be explicitly set; EF Core uses it for upsert tracking
        new CategoryModel { Id = 2, Name = "Clothing", Slug = "clothing" },
        new CategoryModel { Id = 3, Name = "Books", Slug = "books" }
        // => All rows listed here are managed by HasData
        // => EF Core detects changes to these rows and generates UPDATE migrations
    );
}
// => After running `dotnet ef migrations add SeedCategories`, EF Core generates:

// Generated migration for seed data:
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class SeedCategories : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.InsertData(
            table: "categories",                  // => Target table
            columns: new[] { "id", "name", "slug" },
                                                  // => Columns to populate
            values: new object[,]
            {
                { 1, "Electronics", "electronics" },
                // => Row 1: id=1, name="Electronics", slug="electronics"
                { 2, "Clothing", "clothing" },
                { 3, "Books", "books" }
            });
        // => SQL: INSERT INTO categories (id, name, slug) VALUES
        // =>   (1, 'Electronics', 'electronics'),
        // =>   (2, 'Clothing', 'clothing'),
        // =>   (3, 'Books', 'books');
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DeleteData(
            table: "categories",
            keyColumn: "id",                      // => Identify rows by PK for deletion
            keyValues: new object[] { 1, 2, 3 }); // => Delete all three seed rows
        // => SQL: DELETE FROM categories WHERE id IN (1, 2, 3)
    }
}

using System;
using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class CreateInvoicingSchema : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Table 1: invoices (parent)
        migrationBuilder.CreateTable(
            name: "invoices",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                customer_id = table.Column<Guid>(type: "uuid", nullable: false),
                total = table.Column<decimal>(type: "numeric(19,4)", precision: 19, scale: 4, nullable: false),
                issued_at = table.Column<DateTimeOffset>(type: "timestamp with time zone", nullable: false),
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_invoices", x => x.id);
                // => invoices is the parent table; must be created before invoice_lines
            });
 
        // Table 2: invoice_lines (child, depends on invoices)
        migrationBuilder.CreateTable(
            name: "invoice_lines",
            columns: table => new
            {
                id = table.Column<Guid>(type: "uuid", nullable: false),
                invoice_id = table.Column<Guid>(type: "uuid", nullable: false),
                // => FK to invoices.id; invoices must exist before this FK can be added
                description = table.Column<string>(type: "character varying(500)", maxLength: 500, nullable: false),
                unit_price = table.Column<decimal>(type: "numeric(19,4)", precision: 19, scale: 4, nullable: false),
                quantity = table.Column<int>(type: "integer", nullable: false),
            },
            constraints: table =>
            {
                table.PrimaryKey("pk_invoice_lines", x => x.id);
                table.ForeignKey("fk_invoice_lines_invoices_invoice_id",
                    x => x.invoice_id, "invoices", "id",
                    onDelete: ReferentialAction.Cascade);
                // => FK in same migration; parent table invoices was just created above
            });
 
        // Indexes for both tables in the same migration
        migrationBuilder.CreateIndex("ix_invoices_customer_id", "invoices", "customer_id");
        // => Index for: SELECT * FROM invoices WHERE customer_id = ?
 
        migrationBuilder.CreateIndex("ix_invoice_lines_invoice_id", "invoice_lines", "invoice_id");
        // => Index for FK lookups and: SELECT * FROM invoice_lines WHERE invoice_id = ?
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropTable(name: "invoice_lines");
        // => Drop child table first (has FK dependency on invoices)
        migrationBuilder.DropTable(name: "invoices");
        // => Drop parent table second; FK constraint is gone after child table dropped
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class ConfigureCascadeDelete : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Cascade: delete parent => delete children automatically
        migrationBuilder.AddForeignKey(
            name: "fk_comments_posts_post_id",
            table: "comments",
            column: "post_id",
            principalTable: "posts",
            principalColumn: "id",
            onDelete: ReferentialAction.Cascade);
        // => SQL: FOREIGN KEY (post_id) REFERENCES posts (id) ON DELETE CASCADE
        // => Deleting a post: ALL its comments are automatically deleted
        // => Use when: child records have no meaning without the parent
 
        // Restrict: prevents deleting parent if children exist
        migrationBuilder.AddForeignKey(
            name: "fk_orders_customers_customer_id",
            table: "orders",
            column: "customer_id",
            principalTable: "customers",
            principalColumn: "id",
            onDelete: ReferentialAction.Restrict);
        // => SQL: FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE RESTRICT
        // => Deleting a customer with orders: ERROR; must delete orders first
        // => Use when: accidental parent deletion would cause irreversible data loss
 
        // SetNull: sets FK to NULL when parent is deleted
        migrationBuilder.AddForeignKey(
            name: "fk_tasks_assignees_assignee_id",
            table: "tasks",
            column: "assignee_id",
            principalTable: "users",
            principalColumn: "id",
            onDelete: ReferentialAction.SetNull);
        // => SQL: FOREIGN KEY (assignee_id) REFERENCES users (id) ON DELETE SET NULL
        // => Deleting a user: tasks become unassigned (assignee_id = NULL)
        // => Use when: the relationship is optional and orphaned child records are valid
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropForeignKey(name: "fk_tasks_assignees_assignee_id", table: "tasks");
        migrationBuilder.DropForeignKey(name: "fk_orders_customers_customer_id", table: "orders");
        migrationBuilder.DropForeignKey(name: "fk_comments_posts_post_id", table: "comments");
    }
}

using Microsoft.EntityFrameworkCore.Migrations;
 
namespace MyApp.Migrations;
 
public partial class RenameFilenameColumn : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "file_name",                   // => Old column name
            table: "attachments",                // => Target table
            newName: "filename");                // => New column name
        // => SQL: ALTER TABLE attachments RENAME COLUMN file_name TO filename
        // => Fast operation: no data movement; only metadata change
        // => DANGER: any application code using the old column name breaks immediately
        // => Deploy application code update BEFORE running this migration
    }
 
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.RenameColumn(
            name: "filename",                    // => Revert: new name back to old
            table: "attachments",
            newName: "file_name");
        // => SQL: ALTER TABLE attachments RENAME COLUMN filename TO file_name
    }
}

// Step 1 migration: Add new column alongside old column
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<string>(
        name: "filename",                        // => New column with desired name
        table: "attachments",
        type: "character varying(500)",
        maxLength: 500,
        nullable: true);                         // => Nullable initially; old column still active
    // => SQL: ALTER TABLE attachments ADD COLUMN filename VARCHAR(500) NULL
    // => Both file_name and filename now exist; application reads both, writes both
 
    migrationBuilder.Sql(
        "UPDATE attachments SET filename = file_name WHERE filename IS NULL");
    // => SQL: UPDATE attachments SET filename = file_name WHERE filename IS NULL
    // => Copy existing data from old to new column in the migration itself
}
// Step 2 (separate migration, after all app instances are updated to use new column name):
// => Drop old column: migrationBuilder.DropColumn(name: "file_name", table: "attachments")

# Generate SQL for ALL migrations (from empty to latest)
dotnet ef migrations script
# => Outputs SQL for every migration in order
# => Includes CREATE TABLE, ALTER TABLE, CREATE INDEX, and INSERT INTO __EFMigrationsHistory
# => Save to file: dotnet ef migrations script --output full_migration.sql
 
# Generate SQL for a range of migrations
dotnet ef migrations script InitialCreate AddEmailToUsers
# => Generates SQL for migrations BETWEEN InitialCreate (exclusive) and AddEmailToUsers (inclusive)
# => InitialCreate = FROM migration (already applied; start from here)
# => AddEmailToUsers = TO migration (target; apply this and everything between)
# => Useful for upgrade scripts when updating from a known baseline
 
# Save to file with verbose output
dotnet ef migrations script --output ./sql/upgrade.sql --verbose
# => Writes SQL to ./sql/upgrade.sql
# => --verbose shows each migration being processed
# => Output file contains plain SQL; review before executing on production database

-- Example generated SQL output for a simple migration:
-- => Each migration's Up() is translated to raw SQL
 
-- Migration: 20260327000001_InitialCreate
CREATE TABLE "users" (
    "id" uuid NOT NULL,                    -- => Guid maps to uuid
    "email" character varying(255) NOT NULL,
    CONSTRAINT "pk_users" PRIMARY KEY ("id")
);
-- => EF Core uses quoted identifiers in PostgreSQL (double quotes)
 
CREATE UNIQUE INDEX "ix_users_email" ON "users" ("email");
-- => CreateIndex with unique: true generates CREATE UNIQUE INDEX
 
INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
    VALUES ('20260327000001_InitialCreate', '10.0.1');
-- => History tracking row appended after each migration's SQL

# Generate an idempotent script (safe to run multiple times)
dotnet ef migrations script --idempotent
# => Wraps each migration's SQL in an IF NOT EXISTS check against __EFMigrationsHistory
# => Safe to run on a database at ANY migration level
# => Re-running a migration that is already applied: skipped (no error)
# => Running on a fresh database: applies all migrations in order
 
# Generate idempotent script from a baseline
dotnet ef migrations script --idempotent --output ./sql/upgrade-idempotent.sql
# => Recommended for CI/CD pipelines that run on environments of unknown migration state
# => Blue-green deployments, multiple environments, DR (disaster recovery) setup

-- Example idempotent script structure:
 
-- => Generated DO block checks __EFMigrationsHistory before applying each migration
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM "__EFMigrationsHistory"
        WHERE "MigrationId" = '20260327000001_InitialCreate'
    )
    THEN
        -- => Migration SQL runs only if not already applied
        CREATE TABLE "users" (
            "id" uuid NOT NULL,
            "email" character varying(255) NOT NULL,
            CONSTRAINT "pk_users" PRIMARY KEY ("id")
        );
        -- => Additional SQL for this migration...
 
        INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
        VALUES ('20260327000001_InitialCreate', '10.0.1');
        -- => History row inserted AFTER the migration SQL succeeds
    END IF;
END $$;
-- => Pattern repeats for each migration in the script

// Program.cs (ASP.NET Core startup)
using Microsoft.EntityFrameworkCore;               // => DbContext, MigrateAsync
using MyApp.Infrastructure;                        // => AppDbContext
 
var builder = WebApplication.CreateBuilder(args);
// => Creates the ASP.NET Core application builder
 
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseNpgsql(builder.Configuration.GetConnectionString("DefaultConnection")));
// => Registers AppDbContext in DI container
// => UseNpgsql: configures PostgreSQL provider
// => Connection string from appsettings.json: "DefaultConnection"
 
var app = builder.Build();
// => Builds the application; DI container is now finalized
 
// Apply pending migrations at startup
using (var scope = app.Services.CreateScope())
{
    // => CreateScope: creates a DI scope for resolving scoped services
    // => using ensures the scope is disposed after migration
 
    var dbContext = scope.ServiceProvider.GetRequiredService<AppDbContext>();
    // => Resolves AppDbContext from DI; uses connection string configured above
    // => GetRequiredService throws if AppDbContext is not registered
 
    await dbContext.Database.MigrateAsync();
    // => Calls MigrateAsync: applies all pending migrations in order
    // => Equivalent to: dotnet ef database update (at startup time)
    // => Safe to call if no pending migrations: no-op (fast check against __EFMigrationsHistory)
    // => NOT recommended for production: concurrent instances may race; use deployment scripts instead
}
 
app.MapControllers();
// => After migration completes, register routes and start handling requests
// => If MigrateAsync throws, app fails to start (prevents running against stale schema)
 
app.Run();
// => Start the HTTP server; all migrations have been applied before first request

// For production: use a hosted service pattern to run migrations before the server starts
public class MigrationHostedService : IHostedService
{
    private readonly IServiceScopeFactory _scopeFactory;
    public MigrationHostedService(IServiceScopeFactory scopeFactory) =>
        _scopeFactory = scopeFactory;
 
    public async Task StartAsync(CancellationToken cancellationToken)
    {
        using var scope = _scopeFactory.CreateScope();         // => DI scope
        var db = scope.ServiceProvider.GetRequiredService<AppDbContext>();
        // => Resolve DbContext within scope
        await db.Database.MigrateAsync(cancellationToken);    // => Apply pending migrations
        // => Cancellation token enables graceful shutdown during migration
    }
 
    public Task StopAsync(CancellationToken cancellationToken) => Task.CompletedTask;
    // => No cleanup needed; migration is complete or failed on StartAsync
}
// Register in DI: builder.Services.AddHostedService<MigrationHostedService>();