📦 IrbyImaging.DbContext/Migrations/20250317160033_UpdateCrewModels.cs
📑 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Model Changes
- Migration: Up Method
- Migration: Down Method
- UML Diagram of Model Changes
- Usage Examples
- Considerations & Best Practices
- Conclusion
🧐 Overview & Purpose
This Entity Framework Core Migration (UpdateCrewModels) modifies the CrewEquipment and CrewEmployee tables in the irby schema.
- Why? To support new business requirements:
- Track hours worked on equipment
- Add per-diem rates, types & start times for crew employees
- Make certain foreign keys optional
🛠 Dependencies & Technologies
| Layer | Technology / Package |
|---|---|
| ORM / Migration Tool | Microsoft.EntityFrameworkCore |
| Database | SQL Server (uniqueidentifier, decimal(18,2), time) |
| Schema | irby |
| Language | C# (.NET Core / .NET 7+) |
🌟 Key Functional Areas
- Schema Alterations
- Change
CostCodeIdandJobClassificationIdfrom non-nullable to nullable
- Change
- Column Additions
WorkedHours→CrewEquipmentPerDiemRate,PerDiemType,StartTime→CrewEmployee
🔄 Data Model Changes
| Table | Column | Old Definition | New Definition |
|---|---|---|---|
| CrewEquipment | CostCodeId | uniqueidentifier NOT NULL |
uniqueidentifier NULL |
| WorkedHours | n/a | decimal(18,2) NULL |
|
| CrewEmployee | JobClassificationId | uniqueidentifier NOT NULL |
uniqueidentifier NULL |
| CostCodeId | uniqueidentifier NOT NULL |
uniqueidentifier NULL |
|
| PerDiemRate | n/a | decimal(18,2) NULL |
|
| PerDiemType | n/a | nvarchar(max) NULL |
|
| StartTime | n/a | time NULL |
🚀 Migration: Up Method
The Up method applies changes to the live database:
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1️⃣ Make CostCodeId optional (CrewEquipment)
migrationBuilder.AlterColumn<Guid>(
name: "CostCodeId",
schema: "irby",
table: "CrewEquipment",
type: "uniqueidentifier",
nullable: true,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier");
// 2️⃣ Add WorkedHours column (CrewEquipment)
migrationBuilder.AddColumn<decimal>(
name: "WorkedHours",
schema: "irby",
table: "CrewEquipment",
type: "decimal(18,2)",
nullable: true);
// 3️⃣ Make JobClassificationId & CostCodeId optional (CrewEmployee)
migrationBuilder.AlterColumn<Guid>(
name: "JobClassificationId",
schema: "irby",
table: "CrewEmployee",
type: "uniqueidentifier",
nullable: true,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier");
migrationBuilder.AlterColumn<Guid>(
name: "CostCodeId",
schema: "irby",
table: "CrewEmployee",
type: "uniqueidentifier",
nullable: true,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier");
// 4️⃣ Add PerDiemRate, PerDiemType, StartTime (CrewEmployee)
migrationBuilder.AddColumn<decimal>(
name: "PerDiemRate",
schema: "irby",
table: "CrewEmployee",
type: "decimal(18,2)",
nullable: true);
migrationBuilder.AddColumn<string>(
name: "PerDiemType",
schema: "irby",
table: "CrewEmployee",
type: "nvarchar(max)",
nullable: true);
migrationBuilder.AddColumn<TimeSpan>(
name: "StartTime",
schema: "irby",
table: "CrewEmployee",
type: "time",
nullable: true);
}
- AlterColumn calls adjust the nullability of existing GUID FKs.
- AddColumn calls introduce new business fields with
nullable: true.
🔙 Migration: Down Method
The Down method reverts the schema back to its previous state:
protected override void Down(MigrationBuilder migrationBuilder)
{
// 1️⃣ Remove new columns
migrationBuilder.DropColumn("WorkedHours", schema: "irby", table: "CrewEquipment");
migrationBuilder.DropColumn("PerDiemRate", schema: "irby", table: "CrewEmployee");
migrationBuilder.DropColumn("PerDiemType", schema: "irby", table: "CrewEmployee");
migrationBuilder.DropColumn("StartTime", schema: "irby", table: "CrewEmployee");
// 2️⃣ Revert CostCodeId on CrewEquipment to NOT NULL with default
migrationBuilder.AlterColumn<Guid>(
name: "CostCodeId",
schema: "irby",
table: "CrewEquipment",
type: "uniqueidentifier",
nullable: false,
defaultValue: Guid.Empty,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier",
oldNullable: true);
// 3️⃣ Revert CrewEmployee GUIDs to NOT NULL with defaults
migrationBuilder.AlterColumn<Guid>(
name: "JobClassificationId",
schema: "irby",
table: "CrewEmployee",
type: "uniqueidentifier",
nullable: false,
defaultValue: Guid.Empty,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier",
oldNullable: true);
migrationBuilder.AlterColumn<Guid>(
name: "CostCodeId",
schema: "irby",
table: "CrewEmployee",
type: "uniqueidentifier",
nullable: false,
defaultValue: Guid.Empty,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier",
oldNullable: true);
}
- DropColumn removes the fields we added.
- AlterColumn resets FKs to
NOT NULLwithGuid.Emptyas default.
📊 UML Diagram of Model Changes
@startuml
package irby {
class CrewEquipment {
+ Guid Id
+ Guid? CostCodeId // was Guid NOT NULL
+ decimal? WorkedHours // NEW
// ... other props
}
class CrewEmployee {
+ Guid Id
+ Guid? JobClassificationId // was Guid NOT NULL
+ Guid? CostCodeId // was Guid NOT NULL
+ decimal? PerDiemRate // NEW
+ string PerDiemType // NEW
+ TimeSpan? StartTime // NEW
// ... other props
}
}
CrewEquipment --> CostCode : FK
CrewEmployee --> CostCode : FK
CrewEmployee --> JobClassification : FK
@enduml
💡 Usage Examples
-
Add & Apply Migration
dotnet ef migrations add UpdateCrewModels \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api dotnet ef database update -
Rollback
dotnet ef database update PreviousMigrationName -
Inspect Generated SQL
dotnet ef migrations script UpdateCrewModels
✅ Considerations & Best Practices
- Data Integrity
- When switching a FK from non-nullable → nullable, verify existing data consistency.
- Backfill
- If
WorkedHoursorPerDiemRatemust have initial values, consider a one-off data script.
- If
- Downtime
- Adding columns is usually safe; altering nullability may require table locks.
- Testing
- Always test
UpandDownin a staging environment.
- Always test
- Version Control
- Commit migration files alongside code changes to ensure reproducible deployments.
🏁 Conclusion
The UpdateCrewModels migration enhances the Crew domain by:
- Allowing optional assignment of cost codes and classifications
- Enabling tracking of equipment hours
- Introducing per-diem management fields
This aligns the database schema with evolving business requirements for crew time and expense tracking. Always follow EF Core migration best practices to ensure seamless database evolution! 🚀
Generated by your friendly AI documentation assistant 🤖