📚 Documentation: AddCrewEquipmentTable Migration


📖 Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
  3. Dependencies & Technologies
  4. Data Model: CrewEquipment Table
  5. Migration Methods
  6. Entity Relationships & UML Diagram
  7. Usage Examples
  8. Error Handling & Edge Cases
  9. Third‑Party Integrations
  10. Conclusion

🧐 Overview & Purpose

The 20250306183744_AddCrewEquipmentTable migration introduces the CrewEquipment table into the irby schema of the database. This bridging (junction) table enables a many‑to‑many association between Crews, Equipment, and Jobs:

  • A Crew can have multiple Equipment items.
  • A single Equipment can be assigned to different Crews on different Jobs.

Why This Migration Matters

  • 🛠️ Builds the foundation for tracking which equipment is assigned to which crew on a job.
  • 🗄️ Maintains historical assignment data through metadata (CreatedBy, CreatedDate, IsDeleted).

🔑 Key Functional Areas

  • Table Creation
    Creates CrewEquipment with the following columns:

    • Primary Key: CrewEquipmentId (GUID)
    • Foreign Keys: CrewId, EquipmentId, JobId
    • Audit Fields: CreatedBy, CreatedDate, IsDeleted
  • Indexing
    Improves query performance on foreign-key lookups for CrewId, EquipmentId, and JobId.

  • Referential Integrity
    Establishes ON DELETE RESTRICT to prevent accidental deletion of related records.

  • Rollback Capability
    The Down method drops the CrewEquipment table, enabling safe rollbacks.


🛠 Dependencies & Technologies

Technology Version / Details
.NET 6.0+
Entity Framework Core Microsoft.EntityFrameworkCore.Migrations
Database SQL Server (Schema: irby)
C# Nullable Reference Types disabled (#nullable disable)

🗃️ Data Model: CrewEquipment Table

Column Name Data Type Nullable Description
CrewEquipmentId uniqueidentifier NO Primary Key—Uniquely identifies the assignment.
CrewId uniqueidentifier NO FK to irby.Crew.
EquipmentId uniqueidentifier NO FK to irby.Equipment.
JobId uniqueidentifier NO FK to irby.Job.
CreatedBy nvarchar(max) YES Username or system that created the record.
CreatedDate datetime2 NO Timestamp when record was created.
IsDeleted bit NO Soft‑delete flag (0 = active, 1 = deleted).

🔨 Migration Methods

Up Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "CrewEquipment",
        schema: "irby",
        columns: table => new
        {
            CrewEquipmentId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            CrewId          = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            EquipmentId     = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            JobId           = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            CreatedBy       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            CreatedDate     = table.Column<DateTime>(type: "datetime2", nullable: false),
            IsDeleted       = table.Column<bool>(type: "bit", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_CrewEquipment", x => x.CrewEquipmentId);
            table.ForeignKey(
                name: "FK_CrewEquipment_Crew_CrewId",
                column: x => x.CrewId,
                principalSchema: "irby",
                principalTable: "Crew",
                principalColumn: "CrewId",
                onDelete: ReferentialAction.Restrict);
            table.ForeignKey(
                name: "FK_CrewEquipment_Equipment_EquipmentId",
                column: x => x.EquipmentId,
                principalSchema: "irby",
                principalTable: "Equipment",
                principalColumn: "EquipmentId",
                onDelete: ReferentialAction.Restrict);
            table.ForeignKey(
                name: "FK_CrewEquipment_Job_JobId",
                column: x => x.JobId,
                principalSchema: "irby",
                principalTable: "Job",
                principalColumn: "JobId",
                onDelete: ReferentialAction.Restrict);
        });

    // Indexes for fast lookups
    migrationBuilder.CreateIndex(
        name: "IX_CrewEquipment_CrewId",
        schema: "irby",
        table: "CrewEquipment",
        column: "CrewId");
    migrationBuilder.CreateIndex(
        name: "IX_CrewEquipment_EquipmentId",
        schema: "irby",
        table: "CrewEquipment",
        column: "EquipmentId");
    migrationBuilder.CreateIndex(
        name: "IX_CrewEquipment_JobId",
        schema: "irby",
        table: "CrewEquipment",
        column: "JobId");
}

Highlights:

  • CreateTable(...) defines the schema and constraints.
  • Foreign keys use Restrict to ensure no cascade deletes.
  • Indexes on FKs speed up join operations.

Down Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "CrewEquipment",
        schema: "irby");
}

This method reverses the changes made in Up, allowing the migration to be rolled back cleanly.


📊 Entity Relationships & UML Diagram

Below is a Mermaid UML diagram illustrating how CrewEquipment ties the other entities together:

erDiagram
    CREW ||--o{ CREW_EQUIPMENT : assigns
    EQUIPMENT ||--o{ CREW_EQUIPMENT : is_assigned
    JOB ||--o{ CREW_EQUIPMENT : under

    CREW {
        GUID CrewId PK
        string Name
    }
    EQUIPMENT {
        GUID EquipmentId PK
        string SerialNumber
    }
    JOB {
        GUID JobId PK
        DateTime StartDate
    }
    CREW_EQUIPMENT {
        GUID CrewEquipmentId PK
        GUID CrewId FK
        GUID EquipmentId FK
        GUID JobId FK
        string CreatedBy
        DateTime CreatedDate
        bool IsDeleted
    }

🚀 Usage Examples

  1. Adding the Migration

    dotnet ef migrations add AddCrewEquipmentTable \
        --project IrbyImaging.DbContext \
        --context IrbyDbContext
    
  2. Updating the Database

    dotnet ef database update --project IrbyImaging.DbContext
    
  3. Querying (LINQ example in C#)

    var assignments = dbContext.CrewEquipments
        .Where(ce => !ce.IsDeleted && ce.CrewId == crewId)
        .Include(ce => ce.Equipment)
        .Include(ce => ce.Job)
        .ToList();
    

⚠️ Error Handling & Edge Cases

  • Foreign Key Violations
    • Attempting to insert a CrewId, EquipmentId, or JobId that doesn’t exist will throw an exception.
  • Soft‑Deletion
    • Queries should filter on IsDeleted = false to hide “deleted” assignments.

🔌 Third‑Party Integrations

This migration relies on Entity Framework Core Migrations. No additional external libraries are required.


🏁 Conclusion

The AddCrewEquipmentTable migration is a foundational step for managing complex associations between crews, equipment, and jobs. By creating a dedicated junction table with audit fields and referential integrity, the application gains:

  • Clear mapping of who uses what on which job.
  • Built‑in support for soft deletes and auditing.
  • Maintainable schema evolution via EF Core Migrations.

With this documentation, you should be able to understand, apply, and extend the migration to suit your business rules and data‑access patterns. 🎉


Generated on 2025‑03‑06 by the IrbyImaging.DbContext Migration Tool