📂 Migration: AddCrewsFieldLog

File: IrbyImaging.DbContext/Migrations/20250225145815_AddCrewsFieldLog.cs


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Migration Methods
  5. Database Changes
  6. Class Diagram (UML)
  7. Usage Examples
  8. Additional Sections (Not Applicable)
  9. Conclusion

1. Overview & Purpose 🚀

The AddCrewsFieldLog migration is designed to evolve the existing database schema by:

  • Adding a new column PerDiemType to the WorkEntry table.
  • Creating a new Crews table to support crew management and link crews to jobs.

Why?

  1. Track different per‐diem categories per work entry (e.g., “Standard”, “Overnight”).
  2. Organize workers into crews and tie them to specific jobs for reporting and scheduling.

2. Dependencies & Technologies 🔧

Area Details
Framework .NET 6.0+ / .NET 7.0+
ORM Entity Framework Core (EF Core Migrations)
Language C# 10+
Package Microsoft.EntityFrameworkCore.Migrations

3. Key Functional Areas 🛠️

  • Schema Modification
    • Append new nullable column to an existing table.
  • Table Creation
    • Define primary key, foreign key, and index for a new entity.
  • Rollback Support
    • Provide a clean Down method to revert both changes.

4. Migration Methods 🔄

4.1 Up Method

The Up method applies changes to the target database.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1. Add PerDiemType column to WorkEntry
    migrationBuilder.AddColumn<string>(
        name: "PerDiemType",
        schema: "irby",
        table: "WorkEntry",
        type: "nvarchar(max)",
        nullable: true);

    // 2. Create Crews table
    migrationBuilder.CreateTable(
        name: "Crews",
        columns: table => new
        {
            CrewId    = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            CrewName  = table.Column<string>(type: "nvarchar(max)",       nullable: true),
            JobId     = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Crews", x => x.CrewId);
            table.ForeignKey(
                name: "FK_Crews_Job_JobId",
                column: x => x.JobId,
                principalSchema: "irby",
                principalTable: "Job",
                principalColumn: "JobId",
                onDelete: ReferentialAction.Cascade);
        });

    // 3. Index on JobId for fast lookups
    migrationBuilder.CreateIndex(
        name: "IX_Crews_JobId",
        table: "Crews",
        column: "JobId");
}

4.2 Down Method

The Down method rolls back the schema changes:

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1. Drop Crews table
    migrationBuilder.DropTable(name: "Crews");

    // 2. Remove PerDiemType column
    migrationBuilder.DropColumn(
        name: "PerDiemType",
        schema: "irby",
        table: "WorkEntry");
}

5. Database Changes 🗂️

5.1 New Crews Table

Column Type Nullable Key Description
CrewId uniqueidentifier No Primary Key Unique ID for each crew
CrewName nvarchar(max) Yes — Optional human‐readable crew name
JobId uniqueidentifier No Foreign Key → Job Links crew to a job (onDelete: Cascade)
  • Index: IX_Crews_JobId for optimized JOINs.

5.2 New PerDiemType Column

Schema Table Column Type Nullable Description
irby WorkEntry PerDiemType nvarchar(max) Yes Classifies per‐diem (e.g., “Standard”)

6. Class Diagram (UML) 🔍

classDiagram
    class Migration {
        <<abstract>>
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }

    class AddCrewsFieldLog {
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }
    AddCrewsFieldLog --|> Migration

    class Crews {
        +Guid CrewId
        +string? CrewName
        +Guid JobId
    }
    Crews --> Job : FK on Delete Cascade

7. Usage Examples 📝

  1. Apply Migration
    dotnet ef migrations add AddCrewsFieldLog
    dotnet ef database update
    
  2. Revert Migration
    dotnet ef database update <Previous_Migration_Name>
    

8. Additional Sections (Not Applicable) ⚠️

  • API Endpoints Overview: N/A – pure database migration.
  • View Models & Data Models: N/A – no DTOs or VM classes.
  • Helper Methods / Utilities: N/A – uses EF Core built‐in APIs.
  • Error Handling & Return Types: N/A – migration errors bubble up via EF Core.
  • Third‐party Integrations: N/A – no external services invoked.

9. Conclusion 🎉

The AddCrewsFieldLog migration enhances the irby schema by:

  • Capturing per‐diem classifications directly on work entries.
  • Introducing a flexible Crews entity to group labor resources and associate them with jobs.

This lays the foundation for richer crew management, reporting, and payroll calculations in downstream services.

“Schema evolution is the heartbeat of an agile data model.” – Entity Framework Core