🗂️ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Details
  4. Database Changes
  5. Entity Relationship UML Diagram
  6. Usage & Applying the Migration
  7. Sample Code Blocks
  8. Best Practices & Considerations
  9. 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 WorkOrder and 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:

  1. Create Indexes on WorkOrder table
  2. 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

  1. Add Migration (if not already scaffolded)
    dotnet ef migrations add AddFKToWorkOrderTable \
        --project IrbyImaging.DbContext \
        --context IrbyDbContext \
        --schema irby
    
  2. Update Database
    dotnet ef database update \
        --project IrbyImaging.DbContext \
        --context IrbyDbContext
    
  3. 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/JOIN clauses.
  • 🛡️ Cascade Deletes: Use sparingly; ensure business rules allow automatic child deletion.
  • 🔍 Verification: Always check the generated SQL script with dotnet ef migrations script before 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.