๐ ๏ธ Migration Documentation: 20250310163049_AddCostCodeIdToCrewEquipment.cs
๐ Table of Contents
- Overview & Purpose
- Schema Changes Summary
- Detailed Breakdown
- 3.1 Up Method
- 3.1.1 AddColumn
- 3.1.2 CreateIndex
- 3.1.3 AddForeignKey
- 3.2 Down Method
- 3.1 Up Method
- Dependencies & Technologies
- UML Class Diagram
- Usage Examples
- 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
CrewEquipmentandCostCode.
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
- Namespace:
- SQL Server (implied by
uniqueidentifiertype and default GUID syntax) - Schema:
irby(custom schema for domain-specific tables)
๐ UML Class Diagram
+----------------------------------------------+
| AddCostCodeIdToCrewEquipment |
| : Migration |
+----------------------------------------------+
| + Up(migrationBuilder: MigrationBuilder) |
| + Down(migrationBuilder: MigrationBuilder) |
+----------------------------------------------+
Legend
Migrationis the base EF Core class.UpandDownare the override methods for applying/reverting changes.
๐ Usage Examples
-
Add the Migration
dotnet ef migrations add AddCostCodeIdToCrewEquipment \ --context IrbyDbContext \ --output-dir Migrations -
Apply to Database
dotnet ef database update \ --context IrbyDbContext -
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! ๐