🗂️ Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Details
- Database Changes
- Entity Relationship UML Diagram
- Usage & Applying the Migration
- Sample Code Blocks
- Best Practices & Considerations
- Conclusion
Overview & Purpose
This migration file 20240229203206_AddFKToWorkOrderTable belongs to the IrbyImaging.DbContext.Migrations namespace. Its primary goal is to enhance data integrity in the WorkOrder table by:
- 🔍 Creating indexes on foreign key columns for performance.
- 🛡️ Adding foreign key constraints between
WorkOrderand related tables (Employee,Equipment,Job).
These changes ensure that every WorkOrder record references valid Employee, Equipment, and Job entries, and improve query performance on those relationships.
Dependencies & Technologies
| Technology / Package | Version / Notes |
|---|---|
| Microsoft.EntityFrameworkCore | ≥ 6.0 (for MigrationBuilder APIs) |
| .NET SDK | ≥ .NET 6.0 |
| Relational Database (e.g., SQL Server) | Schema named irby |
Migration Details
namespace IrbyImaging.DbContext.Migrations
{
public partial class AddFKToWorkOrderTable : Migration
{
protected override void Up(MigrationBuilder migrationBuilder) { /* ... */ }
protected override void Down(MigrationBuilder migrationBuilder) { /* ... */ }
}
}
🔼 Up Method
The Up method is executed when applying this migration. It performs the following steps:
- Create Indexes on
WorkOrdertable - Add Foreign Key Constraints to ensure referential integrity
protected override void Up(MigrationBuilder migrationBuilder)
{
// Indexes
migrationBuilder.CreateIndex(
name: "IX_WorkOrder_EquipmentId",
schema: "irby",
table: "WorkOrder",
column: "EquipmentId");
migrationBuilder.CreateIndex(
name: "IX_WorkOrder_JobId",
schema: "irby",
table: "WorkOrder",
column: "JobId");
migrationBuilder.CreateIndex(
name: "IX_WorkOrder_ProjectManagerId",
schema: "irby",
table: "WorkOrder",
column: "ProjectManagerId");
// Foreign Keys
migrationBuilder.AddForeignKey(
name: "FK_WorkOrder_Employee_ProjectManagerId",
schema: "irby",
table: "WorkOrder",
column: "ProjectManagerId",
principalSchema: "irby",
principalTable: "Employee",
principalColumn: "EmployeeId");
migrationBuilder.AddForeignKey(
name: "FK_WorkOrder_Equipment_EquipmentId",
schema: "irby",
table: "WorkOrder",
column: "EquipmentId",
principalSchema: "irby",
principalTable: "Equipment",
principalColumn: "EquipmentId");
migrationBuilder.AddForeignKey(
name: "FK_WorkOrder_Job_JobId",
schema: "irby",
table: "WorkOrder",
column: "JobId",
principalSchema: "irby",
principalTable: "Job",
principalColumn: "JobId",
onDelete: ReferentialAction.Cascade);
}
🔽 Down Method
The Down method reverses the changes made in Up. It drops the constraints and indexes, effectively rolling back the migration.
protected override void Down(MigrationBuilder migrationBuilder)
{
// Remove Foreign Keys
migrationBuilder.DropForeignKey(
name: "FK_WorkOrder_Employee_ProjectManagerId",
schema: "irby",
table: "WorkOrder");
migrationBuilder.DropForeignKey(
name: "FK_WorkOrder_Equipment_EquipmentId",
schema: "irby",
table: "WorkOrder");
migrationBuilder.DropForeignKey(
name: "FK_WorkOrder_Job_JobId",
schema: "irby",
table: "WorkOrder");
// Remove Indexes
migrationBuilder.DropIndex(
name: "IX_WorkOrder_EquipmentId",
schema: "irby",
table: "WorkOrder");
migrationBuilder.DropIndex(
name: "IX_WorkOrder_JobId",
schema: "irby",
table: "WorkOrder");
migrationBuilder.DropIndex(
name: "IX_WorkOrder_ProjectManagerId",
schema: "irby",
table: "WorkOrder");
}
Database Changes
New Indexes
| Index Name | Table | Column | Purpose |
|---|---|---|---|
| IX_WorkOrder_EquipmentId | WorkOrder | EquipmentId | Speed up lookups by equipment |
| IX_WorkOrder_JobId | WorkOrder | JobId | Speed up lookups by job (with cascade) |
| IX_WorkOrder_ProjectManagerId | WorkOrder | ProjectManagerId | Speed up lookups by project manager |
Foreign Key Constraints
| FK Name | From Table | From Column | To Table | To Column | On Delete Behavior |
|---|---|---|---|---|---|
| FK_WorkOrder_Employee_ProjectManagerId | WorkOrder | ProjectManagerId | Employee | EmployeeId | No action (default) |
| FK_WorkOrder_Equipment_EquipmentId | WorkOrder | EquipmentId | Equipment | EquipmentId | No action (default) |
| FK_WorkOrder_Job_JobId | WorkOrder | JobId | Job | JobId | Cascade on delete (child deletion) |
Entity Relationship UML Diagram
@startuml
schema "irby" {
class WorkOrder {
+int WorkOrderId
+int EquipmentId
+int JobId
+int ProjectManagerId
// ...
}
class Employee {
+int EmployeeId
// ...
}
class Equipment {
+int EquipmentId
// ...
}
class Job {
+int JobId
// ...
}
}
WorkOrder "1" --> "1" Employee : ProjectManagerId
WorkOrder "1" --> "1" Equipment : EquipmentId
WorkOrder "1" --> "1..*" Job : JobId
@enduml
Usage & Applying the Migration
- Add Migration (if not already scaffolded)
dotnet ef migrations add AddFKToWorkOrderTable \ --project IrbyImaging.DbContext \ --context IrbyDbContext \ --schema irby - Update Database
dotnet ef database update \ --project IrbyImaging.DbContext \ --context IrbyDbContext - Verify
- Check SQL Server / PostgreSQL management tool for new indexes
- Inspect table design for foreign key constraints
Sample Code Blocks
Checking Foreign Keys via SQL
-- List foreign keys on WorkOrder
SELECT
fk.name AS FK_Name,
tp.name AS ParentTable,
cp.name AS ParentColumn,
tr.name AS ReferencedTable,
cr.name AS ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc
ON fk.object_id = fkc.constraint_object_id
INNER JOIN sys.tables tp
ON fkc.parent_object_id = tp.object_id
INNER JOIN sys.columns cp
ON fkc.parent_object_id = cp.object_id AND fkc.parent_column_id = cp.column_id
INNER JOIN sys.tables tr
ON fkc.referenced_object_id = tr.object_id
INNER JOIN sys.columns cr
ON fkc.referenced_object_id = cr.object_id AND fkc.referenced_column_id = cr.column_id
WHERE tp.name = 'WorkOrder';
Rolling Back
dotnet ef database update 20240229200000 \
--project IrbyImaging.DbContext \
--context IrbyDbContext
Best Practices & Considerations
- ⚙️ Index Selection: Only add indexes on columns frequently used in
WHERE/JOINclauses. - 🛡️ Cascade Deletes: Use sparingly; ensure business rules allow automatic child deletion.
- 🔍 Verification: Always check the generated SQL script with
dotnet ef migrations scriptbefore applying to production. - 📝 Documentation: Keep migration names and comments descriptive for future auditing.
Conclusion
This migration strengthens the relational integrity of the WorkOrder entity by introducing indexes and foreign key constraints against Employee, Equipment, and Job tables. By following the usage steps and adhering to best practices, you ensure:
- 📈 Improved query performance
- 🔒 Stronger data consistency
- 🛠️ Easier maintainability
Deploy this migration as part of your CI/CD pipeline to keep your database schema in sync with your EF Core model.