πŸ“š Documentation: AddCrewEmployeeTable Migration

File Path: IrbyImaging.DbContext/Migrations/20250306035117_AddCrewEmployeeTable.cs
Purpose: Introduce a many-to-many join table (CrewEmployee) linking Crew and Employee in the irby schema.


πŸ—‚ Table of Contents

  1. Overview & Purpose
  2. Migration Snapshot
  3. Key Functional Areas
  4. Dependencies & Technologies
  5. Data Models & Schema Changes
  6. Migration Methods: Up & Down
  7. Entity Relationship Diagram (UML)
  8. Sample Code Blocks & Usage
  9. Applying the Migration
  10. Conclusion

πŸ” Overview & Purpose

This Entity Framework Core migration creates a join table named CrewEmployee in the irby schema to represent a many‑to‑many relationship between:

  • Crew (table: irby.Crew)
  • Employee (table: irby.Employee)

Prior to this migration, Crew and Employee had no direct link. After applying it, one crew can have multiple employees and one employee can be assigned to multiple crews.

Why a join table?

  • EF Core does not support implicit many-to-many prior to version 5.
  • A join table provides referential integrity and efficient querying.

πŸ“ Migration Snapshot

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    public partial class AddCrewEmployeeTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateTable(
                name: "CrewEmployee",
                schema: "irby",
                columns: table => new
                {
                    CrewId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
                    EmployeeId = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_CrewEmployee", x => new { x.CrewId, x.EmployeeId });
                    table.ForeignKey(
                        name: "FK_CrewEmployee_Crew_CrewId",
                        column: x => x.CrewId,
                        principalSchema: "irby",
                        principalTable: "Crew",
                        principalColumn: "CrewId",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_CrewEmployee_Employee_EmployeeId",
                        column: x => x.EmployeeId,
                        principalSchema: "irby",
                        principalTable: "Employee",
                        principalColumn: "EmployeeId",
                        onDelete: ReferentialAction.Restrict);
                });

            migrationBuilder.CreateIndex(
                name: "IX_CrewEmployee_EmployeeId",
                schema: "irby",
                table: "CrewEmployee",
                column: "EmployeeId");
        }

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

πŸ”‘ Key Functional Areas

Area Description
Schema irby – isolates domain tables under a dedicated schema.
Table Creation Creates CrewEmployee with composite PK of (CrewId, EmployeeId).
Foreign Keys Enforces referential integrity to Crew and Employee tables, using Restrict on delete to prevent accidental cascade deletes.
Index Single-column index on EmployeeId for efficient querying of all crews an employee belongs to.
Migration Reversal Down method drops the CrewEmployee table, rolling back schema changes.

πŸ›  Dependencies & Technologies

Component Version / Notes
.NET / C# .NET 6.0+ compatible
Entity Framework Core EF Core 5.0+ – migrations API
Microsoft.EntityFrameworkCore.Migrations Provides MigrationBuilder, Migration base class
SQL Server (or other RDBMS supporting GUID) Underlying database type uniqueidentifier

πŸ“Š Data Models & Schema Changes

1. Original Models (Before Migration)

public class Crew
{
    public Guid CrewId { get; set; }
    // Other props...
    // No direct navigation to Employee
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    // Other props...
    // No direct navigation to Crew
}

2. Join Table Model

While EF Core can scaffold a join table automatically in newer versions, this migration explicitly creates it:

CREATE TABLE irby.CrewEmployee (
    CrewId uniqueidentifier NOT NULL,
    EmployeeId uniqueidentifier NOT NULL,
    CONSTRAINT PK_CrewEmployee PRIMARY KEY (CrewId, EmployeeId),
    CONSTRAINT FK_CrewEmployee_Crew FOREIGN KEY (CrewId) REFERENCES irby.Crew(CrewId) ON DELETE NO ACTION,
    CONSTRAINT FK_CrewEmployee_Employee FOREIGN KEY (EmployeeId) REFERENCES irby.Employee(EmployeeId) ON DELETE NO ACTION
);
CREATE INDEX IX_CrewEmployee_EmployeeId ON irby.CrewEmployee(EmployeeId);

πŸš€ Migration Methods: Up & Down

Method Purpose
Up Applies schema changes. Creates the CrewEmployee table, sets up PK, FKs, and an index on EmployeeId.
Down Reverts schema changes by dropping the CrewEmployee table (automatically removes related constraints/indexes).

Code Walkthrough

  1. CreateTable

    • name: "CrewEmployee"
    • schema: "irby"
    • columns:
      • CrewId: GUID, PK part #1
      • EmployeeId: GUID, PK part #2
  2. PrimaryKey

    • Composite key on (CrewId, EmployeeId) ensures unique pairings.
  3. ForeignKey

    • Link CrewId β†’ irby.Crew.CrewId
    • Link EmployeeId β†’ irby.Employee.EmployeeId
    • onDelete: Restrict to avoid cascading deletes.
  4. CreateIndex

    • Speeds up lookups by EmployeeId.

πŸ“ Entity Relationship Diagram (UML)

@startuml
entity "Crew (irby.Crew)" as Crew {
  *CrewId : GUID
  --
  ... other props ...
}

entity "Employee (irby.Employee)" as Employee {
  *EmployeeId : GUID
  --
  ... other props ...
}

entity "CrewEmployee (irby.CrewEmployee)" as CE {
  *CrewId : GUID
  *EmployeeId : GUID
}

Crew ||--o{ CE : has
Employee ||--o{ CE : assigned_to
@enduml

Note: Use a PlantUML renderer to visualize the above diagram.


πŸ’» Sample Code Blocks & Usage

1. Defining Navigation Properties (Optional)

If you wish to leverage EF Core’s many-to-many APIs, update your models:

public class Crew
{
    public Guid CrewId { get; set; }
    public ICollection<CrewEmployee> CrewEmployees { get; set; }
}

public class Employee
{
    public Guid EmployeeId { get; set; }
    public ICollection<CrewEmployee> CrewEmployees { get; set; }
}

public class CrewEmployee
{
    public Guid CrewId { get; set; }
    public Crew Crew { get; set; }

    public Guid EmployeeId { get; set; }
    public Employee Employee { get; set; }
}

2. Configuring in OnModelCreating

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.HasDefaultSchema("irby");

    modelBuilder.Entity<CrewEmployee>()
        .HasKey(ce => new { ce.CrewId, ce.EmployeeId });

    modelBuilder.Entity<CrewEmployee>()
        .HasOne(ce => ce.Crew)
        .WithMany(c => c.CrewEmployees)
        .HasForeignKey(ce => ce.CrewId)
        .OnDelete(DeleteBehavior.Restrict);

    modelBuilder.Entity<CrewEmployee>()
        .HasOne(ce => ce.Employee)
        .WithMany(e => e.CrewEmployees)
        .HasForeignKey(ce => ce.EmployeeId)
        .OnDelete(DeleteBehavior.Restrict);
}

πŸ›  Applying the Migration

  1. Add Migration (already created):
    dotnet ef migrations add AddCrewEmployeeTable --context YourDbContext
    
  2. Update Database:
    dotnet ef database update --context YourDbContext
    
  3. Verify Schema:
    SELECT * 
    FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = 'irby' AND TABLE_NAME = 'CrewEmployee';
    

🎯 Conclusion

  • This migration establishes a robust many-to-many link between Crew and Employee.
  • It enforces data integrity with composite primary keys and foreign key constraints.
  • The Restrict delete behavior prevents accidental removal of related data.
  • After applying, your domain can naturally express:
    • Which employees belong to a crew?
    • Which crews an employee is assigned to?

Ready to build richer scheduling, assignment, and reporting features around your crews and employees! πŸš€


Generated by documentation template for IRBY Imaging DbContext migrations.