๐Ÿ› ๏ธ Migration Documentation: 20250310163049_AddCostCodeIdToCrewEquipment.cs


๐Ÿ“‹ Table of Contents

  1. Overview & Purpose
  2. Schema Changes Summary
  3. Detailed Breakdown
  4. Dependencies & Technologies
  5. UML Class Diagram
  6. Usage Examples
  7. Conclusion

๐Ÿง Overview & Purpose

File
IrbyImaging.DbContext/Migrations/20250310163049_AddCostCodeIdToCrewEquipment.cs

Intent
This Entity Framework Core migration introduces a new non-nullable CostCodeId column to the irby.CrewEquipment table. It also ensures referential integrity by indexing this column and creating a foreign key relationship to the irby.CostCode table.

Why?

  • Equip each crewโ€™s equipment record with an associated cost code.
  • Facilitate cost tracking and reporting by linking equipment to predefined cost codes.
  • Enforce data integrity by preventing orphaned equipment records without a valid cost code.

๐Ÿ“Š Schema Changes Summary

Operation Schema Table Column Details
Add Column irby CrewEquipment CostCodeId GUID, non-nullable, default = 00000000-0000-0000-0000-000000000000
Create Index irby CrewEquipment (CostCodeId) Named IX_CrewEquipment_CostCodeId
Add Foreign Key irby CrewEquipment (CostCodeId) โ†’ irby.CostCode(CostCodeId), Restrict on delete (no cascade delete)

๐Ÿ” Detailed Breakdown

3.1 Up Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1๏ธโƒฃ Add new non-nullable GUID column 'CostCodeId'
    migrationBuilder.AddColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEquipment",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));

    // 2๏ธโƒฃ Create an index to speed up lookups and joins
    migrationBuilder.CreateIndex(
        name: "IX_CrewEquipment_CostCodeId",
        schema: "irby",
        table: "CrewEquipment",
        column: "CostCodeId");

    // 3๏ธโƒฃ Enforce referential integrity back to the CostCode table
    migrationBuilder.AddForeignKey(
        name: "FK_CrewEquipment_CostCode_CostCodeId",
        schema: "irby",
        table: "CrewEquipment",
        column: "CostCodeId",
        principalSchema: "irby",
        principalTable: "CostCode",
        principalColumn: "CostCodeId",
        onDelete: ReferentialAction.Restrict);
}

3.1.1 AddColumn

  • Purpose: Introduce a new field to store the associated cost code.
  • Type: uniqueidentifier (GUID in SQL Server).
  • Nullable: false โ†’ requires a value for every existing and new record.
  • Default: All existing rows get the โ€œzero-GUIDโ€ placeholder.

3.1.2 CreateIndex

  • Index Name: IX_CrewEquipment_CostCodeId.
  • Rationale: Improves performance for join queries between CrewEquipment and CostCode.

3.1.3 AddForeignKey

  • FK Name: FK_CrewEquipment_CostCode_CostCodeId.
  • OnDelete: Restrict โ†’ prevents deleting a cost code if any equipment is still linked.

3.2 Down Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_CrewEquipment_CostCode_CostCodeId",
        schema: "irby",
        table: "CrewEquipment");

    migrationBuilder.DropIndex(
        name: "IX_CrewEquipment_CostCodeId",
        schema: "irby",
        table: "CrewEquipment");

    migrationBuilder.DropColumn(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEquipment");
}
  • Reverse Operations: Cleanly removes the FK, index, and column to revert the schema back to its prior state.

โš™๏ธ Dependencies & Technologies

  • .NET 6+ / .NET Core
  • Entity Framework Core
    • Namespace: Microsoft.EntityFrameworkCore.Migrations
  • SQL Server (implied by uniqueidentifier type and default GUID syntax)
  • Schema: irby (custom schema for domain-specific tables)

๐Ÿ“ UML Class Diagram

+----------------------------------------------+
|           AddCostCodeIdToCrewEquipment       |
|                 : Migration                  |
+----------------------------------------------+
| + Up(migrationBuilder: MigrationBuilder)     |
| + Down(migrationBuilder: MigrationBuilder)   |
+----------------------------------------------+

Legend

  • Migration is the base EF Core class.
  • Up and Down are the override methods for applying/reverting changes.

๐Ÿš€ Usage Examples

  1. Add the Migration

    dotnet ef migrations add AddCostCodeIdToCrewEquipment \
      --context IrbyDbContext \
      --output-dir Migrations
    
  2. Apply to Database

    dotnet ef database update \
      --context IrbyDbContext
    
  3. Verify Schema

    SELECT TOP 10 CostCodeId, * 
    FROM irby.CrewEquipment;
    

๐Ÿ Conclusion

This migration strengthens your data model by linking crew equipment directly to cost codes, enabling richer financial reporting and enforcing referential integrity. When deployed, it ensures all future equipment records are tied to a legitimate cost code, while existing rows receive a default placeholderโ€”ready for data cleanup or immediate use in cost analyses.


Happy coding! ๐ŸŽ‰