📚 Documentation: AddCrewEquipmentTable Migration
📖 Table of Contents
- Overview & Purpose
- Key Functional Areas
- Dependencies & Technologies
- Data Model: CrewEquipment Table
- Migration Methods
- Entity Relationships & UML Diagram
- Usage Examples
- Error Handling & Edge Cases
- Third‑Party Integrations
- 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
CreatesCrewEquipmentwith the following columns:- Primary Key:
CrewEquipmentId(GUID) - Foreign Keys:
CrewId,EquipmentId,JobId - Audit Fields:
CreatedBy,CreatedDate,IsDeleted
- Primary Key:
-
Indexing
Improves query performance on foreign-key lookups forCrewId,EquipmentId, andJobId. -
Referential Integrity
EstablishesON DELETE RESTRICTto prevent accidental deletion of related records. -
Rollback Capability
TheDownmethod drops theCrewEquipmenttable, 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
Restrictto 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
-
Adding the Migration
dotnet ef migrations add AddCrewEquipmentTable \ --project IrbyImaging.DbContext \ --context IrbyDbContext -
Updating the Database
dotnet ef database update --project IrbyImaging.DbContext -
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, orJobIdthat doesn’t exist will throw an exception.
- Attempting to insert a
- Soft‑Deletion
- Queries should filter on
IsDeleted = falseto hide “deleted” assignments.
- Queries should filter on
🔌 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