📦 IrbyImaging.DbContext/Migrations/20250317160033_UpdateCrewModels.cs


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Changes
  5. Migration: Up Method
  6. Migration: Down Method
  7. UML Diagram of Model Changes
  8. Usage Examples
  9. Considerations & Best Practices
  10. Conclusion

🧐 Overview & Purpose

This Entity Framework Core Migration (UpdateCrewModels) modifies the CrewEquipment and CrewEmployee tables in the irby schema.

  • Why? To support new business requirements:
    • Track hours worked on equipment
    • Add per-diem rates, types & start times for crew employees
    • Make certain foreign keys optional

🛠 Dependencies & Technologies

Layer Technology / Package
ORM / Migration Tool Microsoft.EntityFrameworkCore
Database SQL Server (uniqueidentifier, decimal(18,2), time)
Schema irby
Language C# (.NET Core / .NET 7+)

🌟 Key Functional Areas

  • Schema Alterations
    • Change CostCodeId and JobClassificationId from non-nullable to nullable
  • Column Additions
    • WorkedHours → CrewEquipment
    • PerDiemRate, PerDiemType, StartTime → CrewEmployee

🔄 Data Model Changes

Table Column Old Definition New Definition
CrewEquipment CostCodeId uniqueidentifier NOT NULL uniqueidentifier NULL
WorkedHours n/a decimal(18,2) NULL
CrewEmployee JobClassificationId uniqueidentifier NOT NULL uniqueidentifier NULL
CostCodeId uniqueidentifier NOT NULL uniqueidentifier NULL
PerDiemRate n/a decimal(18,2) NULL
PerDiemType n/a nvarchar(max) NULL
StartTime n/a time NULL

🚀 Migration: Up Method

The Up method applies changes to the live database:

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Make CostCodeId optional (CrewEquipment)
    migrationBuilder.AlterColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEquipment",
        type: "uniqueidentifier",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier");

    // 2️⃣ Add WorkedHours column (CrewEquipment)
    migrationBuilder.AddColumn<decimal>(
        name: "WorkedHours",
        schema: "irby",
        table: "CrewEquipment",
        type: "decimal(18,2)",
        nullable: true);

    // 3️⃣ Make JobClassificationId & CostCodeId optional (CrewEmployee)
    migrationBuilder.AlterColumn<Guid>(
        name: "JobClassificationId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier");

    migrationBuilder.AlterColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier");

    // 4️⃣ Add PerDiemRate, PerDiemType, StartTime (CrewEmployee)
    migrationBuilder.AddColumn<decimal>(
        name: "PerDiemRate",
        schema: "irby",
        table: "CrewEmployee",
        type: "decimal(18,2)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "PerDiemType",
        schema: "irby",
        table: "CrewEmployee",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<TimeSpan>(
        name: "StartTime",
        schema: "irby",
        table: "CrewEmployee",
        type: "time",
        nullable: true);
}
  • AlterColumn calls adjust the nullability of existing GUID FKs.
  • AddColumn calls introduce new business fields with nullable: true.

🔙 Migration: Down Method

The Down method reverts the schema back to its previous state:

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Remove new columns
    migrationBuilder.DropColumn("WorkedHours", schema: "irby", table: "CrewEquipment");
    migrationBuilder.DropColumn("PerDiemRate", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn("PerDiemType", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn("StartTime", schema: "irby", table: "CrewEmployee");

    // 2️⃣ Revert CostCodeId on CrewEquipment to NOT NULL with default
    migrationBuilder.AlterColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEquipment",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier",
        oldNullable: true);

    // 3️⃣ Revert CrewEmployee GUIDs to NOT NULL with defaults
    migrationBuilder.AlterColumn<Guid>(
        name: "JobClassificationId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier",
        oldNullable: true);

    migrationBuilder.AlterColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier",
        oldNullable: true);
}
  • DropColumn removes the fields we added.
  • AlterColumn resets FKs to NOT NULL with Guid.Empty as default.

📊 UML Diagram of Model Changes

@startuml
package irby {
  class CrewEquipment {
    + Guid Id
    + Guid? CostCodeId        // was Guid NOT NULL
    + decimal? WorkedHours    // NEW
    // ... other props
  }

  class CrewEmployee {
    + Guid Id
    + Guid? JobClassificationId // was Guid NOT NULL
    + Guid? CostCodeId          // was Guid NOT NULL
    + decimal? PerDiemRate      // NEW
    + string  PerDiemType       // NEW
    + TimeSpan? StartTime       // NEW
    // ... other props
  }
}
CrewEquipment --> CostCode : FK
CrewEmployee --> CostCode : FK
CrewEmployee --> JobClassification : FK
@enduml

💡 Usage Examples

  1. Add & Apply Migration

    dotnet ef migrations add UpdateCrewModels \
      --project IrbyImaging.DbContext \
      --startup-project IrbyImaging.Api
    dotnet ef database update
    
  2. Rollback

    dotnet ef database update PreviousMigrationName
    
  3. Inspect Generated SQL

    dotnet ef migrations script UpdateCrewModels
    

✅ Considerations & Best Practices

  • Data Integrity
    • When switching a FK from non-nullable → nullable, verify existing data consistency.
  • Backfill
    • If WorkedHours or PerDiemRate must have initial values, consider a one-off data script.
  • Downtime
    • Adding columns is usually safe; altering nullability may require table locks.
  • Testing
    • Always test Up and Down in a staging environment.
  • Version Control
    • Commit migration files alongside code changes to ensure reproducible deployments.

🏁 Conclusion

The UpdateCrewModels migration enhances the Crew domain by:

  • Allowing optional assignment of cost codes and classifications
  • Enabling tracking of equipment hours
  • Introducing per-diem management fields

This aligns the database schema with evolving business requirements for crew time and expense tracking. Always follow EF Core migration best practices to ensure seamless database evolution! 🚀


Generated by your friendly AI documentation assistant 🤖