πŸ“‘ 20220516185008_ChangeFieldOfficeTable Migration Documentation


πŸ“‹ Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
  3. Dependencies & Technologies
  4. Data Models Before & After
  5. Helper Methods / Utilities
  6. Error Handling & Return Types
  7. API Endpoints Overview
  8. Third-Party Integrations
  9. Sample Code Blocks
  10. Usage Examples
  11. UML Diagram
  12. Conclusion

🎯 Overview & Purpose

This Entity Framework Core migration modifies the FieldOffice table schema in the irby schema.

  • Primary Goal:

    1. Make the three GUID columns (ManagerId, ProjectManagerId, SuperintendentId) nullable.
    2. Change their delete behavior from Cascade β†’ Restrict.
  • Why?

    • Allow creation of a FieldOffice record without immediately assigning employees.
    • Prevent accidental deletion of an Employee who is still referenced by a FieldOffice.

πŸ”‘ Key Functional Areas

  1. Up(MigrationBuilder)

    • Drops existing foreign keys on ManagerId, ProjectManagerId, SuperintendentId.
    • Alters those columns to nullable (Guid?).
    • Re-adds foreign keys with Restrict on-delete behavior.
  2. Down(MigrationBuilder)

    • Reverts the above changes:
      • Drops Restrict FKs.
      • Alters columns back to non-nullable with default 0000-…-0000.
      • Re-adds original FKs with Cascade on-delete.

βš™οΈ Dependencies & Technologies

Component Version / Type
Language C#
Framework .NET (Core / 5 / 6)
ORM EntityFrameworkCore (Migrations)
Namespaces System, Microsoft.EntityFrameworkCore

πŸ—„οΈ Data Models Before & After

Before Migration

Column Type Nullable FK Delete Behavior
ManagerId uniqueidentifier No Cascade
ProjectManagerId uniqueidentifier No Cascade
SuperintendentId uniqueidentifier No Cascade

After Migration

Column Type Nullable FK Delete Behavior
ManagerId uniqueidentifier Yes Restrict
ProjectManagerId uniqueidentifier Yes Restrict
SuperintendentId uniqueidentifier Yes Restrict

πŸ› οΈ Helper Methods / Utilities

  • MigrationBuilder
    • Exposed in protected override void Up(MigrationBuilder migrationBuilder)
    • Provides fluent API calls:
      • DropForeignKey()
      • AlterColumn<T>()
      • AddForeignKey()

⚠️ Error Handling & Return Types

  • EF Core Migrations will throw exceptions if any SQL operation fails (e.g., missing table/column).
  • No custom error handling in this migrationβ€”EF’s internal transaction management ensures either all or none of the operations apply.

πŸš€ API Endpoints Overview

Not Applicable
This file is strictly a database schema migration, no web endpoints are defined here.


πŸ”— Third-Party Integrations

None
This migration only uses built-in Microsoft EF Core packages.


πŸ“¦ Sample Code Blocks

Up Method (Making Columns Nullable + Restrict FKs)

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop existing FKs
    migrationBuilder.DropForeignKey(
      name: "FK_FieldOffice_Employee_ManagerId",
      schema: "irby", table: "FieldOffice");
    // … repeat for ProjectManagerId & SuperintendentId …

    // 2️⃣ Alter columns to nullable
    migrationBuilder.AlterColumn<Guid>(
      name: "ManagerId",
      schema: "irby", table: "FieldOffice",
      type: "uniqueidentifier", nullable: true,
      oldClrType: typeof(Guid), oldType: "uniqueidentifier");

    // 3️⃣ Re-add FKs with Restrict
    migrationBuilder.AddForeignKey(
      name: "FK_FieldOffice_Employee_ManagerId",
      schema: "irby", table: "FieldOffice",
      column: "ManagerId",
      principalSchema: "irby", principalTable: "Employee",
      principalColumn: "Id", onDelete: ReferentialAction.Restrict);
    // … repeat for ProjectManagerId & SuperintendentId …
}

Down Method (Reverting to Non-Nullable + Cascade FKs)

protected override void Down(MigrationBuilder migrationBuilder)
{
    // Drop Restrict FKs …
    // Alter columns back to non-nullable w/ default Guid.Empty …
    // Re-add FKs with Cascade …
}

πŸ’‘ Usage Examples

  1. Add Migration
    dotnet ef migrations add ChangeFieldOfficeTable
    
  2. Apply to Database
    dotnet ef database update
    
  3. Verify Schema
    • Check FieldOffice table in SQL Server Management Studio or Azure Data Studio.
    • Confirm columns allow NULL and FKs have Restrict on delete.

πŸ“ UML Diagram

classDiagram
    direction LR
    class FieldOffice {
      +Guid Id
      +Guid? ManagerId
      +Guid? ProjectManagerId
      +Guid? SuperintendentId
    }
    class Employee {
      +Guid Id
      +string Name
      // … other props …
    }
    FieldOffice --> Employee : ManagerId
    FieldOffice --> Employee : ProjectManagerId
    FieldOffice --> Employee : SuperintendentId

🏁 Conclusion

This migration enhances data integrity and business flexibility by:

  • Allowing FieldOffice records to exist without pre-assigned managers.
  • Preventing unintentional deletion of employees still in use via Restrict constraints.

Apply this migration as part of your EF Core deployment pipeline to update the database schema safely and predictably. πŸš€