π Documentation: AddCrewEmployeeTable Migration
File Path:
IrbyImaging.DbContext/Migrations/20250306035117_AddCrewEmployeeTable.cs
Purpose: Introduce a many-to-many join table (CrewEmployee) linkingCrewandEmployeein theirbyschema.
π Table of Contents
- Overview & Purpose
- Migration Snapshot
- Key Functional Areas
- Dependencies & Technologies
- Data Models & Schema Changes
- Migration Methods:
Up&Down - Entity Relationship Diagram (UML)
- Sample Code Blocks & Usage
- Applying the Migration
- 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
-
CreateTable
name: "CrewEmployee"schema: "irby"columns:CrewId: GUID, PK part #1EmployeeId: GUID, PK part #2
-
PrimaryKey
- Composite key on
(CrewId, EmployeeId)ensures unique pairings.
- Composite key on
-
ForeignKey
- Link
CrewIdβirby.Crew.CrewId - Link
EmployeeIdβirby.Employee.EmployeeId onDelete: Restrictto avoid cascading deletes.
- Link
-
CreateIndex
- Speeds up lookups by
EmployeeId.
- Speeds up lookups by
π 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
- Add Migration (already created):
dotnet ef migrations add AddCrewEmployeeTable --context YourDbContext - Update Database:
dotnet ef database update --context YourDbContext - 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
CrewandEmployee. - It enforces data integrity with composite primary keys and foreign key constraints.
- The
Restrictdelete 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.