📄 Documentation: 20240201134411_UpdateFieldOfficeTableWithNewColumns.cs


📑 Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
    1. Up() Method 🔼
    2. Down() Method 🔽
  3. Dependencies & Technologies
  4. Data Models & Schema Changes
  5. Class Diagram (UML)
  6. How It Works: Step by Step
  7. Usage & Examples
  8. Error Handling & Rollback
  9. Conclusion 🎯

Overview & Purpose

File Path:

IrbyImaging.DbContext/Migrations/20240201134411_UpdateFieldOfficeTableWithNewColumns.cs

Purpose:
This Entity Framework Core migration adds three new nullable GUID columns—DirectorId, PrimaryContactId, and SecondaryContactId—to the FieldOffice table, while removing the old free-text PrimaryContact and SecondaryContact columns. It establishes foreign-key relationships to the Employee table, enhancing data integrity and enabling relational joins.


Key Functional Areas

1. Up() Method 🔼

  • Drops:
    • PrimaryContact (string)
    • SecondaryContact (string)
  • Adds:
    • DirectorId (GUID, nullable)
    • PrimaryContactId (GUID, nullable)
    • SecondaryContactId (GUID, nullable)
  • Creates Indexes for each new column
  • Adds Foreign Keys linking to irby.Employee(EmployeeId)
protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop old free-text columns
    migrationBuilder.DropColumn("PrimaryContact", "irby", "FieldOffice");
    migrationBuilder.DropColumn("SecondaryContact", "irby", "FieldOffice");

    // 2️⃣ Add new GUID columns
    migrationBuilder.AddColumn<Guid>("DirectorId", "irby", "FieldOffice", type: "uniqueidentifier", nullable: true);
    migrationBuilder.AddColumn<Guid>("PrimaryContactId", "irby", "FieldOffice", type: "uniqueidentifier", nullable: true);
    migrationBuilder.AddColumn<Guid>("SecondaryContactId", "irby", "FieldOffice", type: "uniqueidentifier", nullable: true);

    // 3️⃣ Create indexes
    migrationBuilder.CreateIndex("IX_FieldOffice_DirectorId", "irby", "FieldOffice", "DirectorId");
    migrationBuilder.CreateIndex("IX_FieldOffice_PrimaryContactId", "irby", "FieldOffice", "PrimaryContactId");
    migrationBuilder.CreateIndex("IX_FieldOffice_SecondaryContactId", "irby", "FieldOffice", "SecondaryContactId");

    // 4️⃣ Add foreign key constraints
    migrationBuilder.AddForeignKey("FK_FieldOffice_Employee_DirectorId", "irby", "FieldOffice", "DirectorId", "irby", "Employee", principalColumn: "EmployeeId");
    migrationBuilder.AddForeignKey("FK_FieldOffice_Employee_PrimaryContactId", "irby", "FieldOffice", "PrimaryContactId", "irby", "Employee", principalColumn: "EmployeeId");
    migrationBuilder.AddForeignKey("FK_FieldOffice_Employee_SecondaryContactId", "irby", "FieldOffice", "SecondaryContactId", "irby", "Employee", principalColumn: "EmployeeId");
}

2. Down() Method 🔽

This reverses all changes made in Up():

  • Drops the three GUID columns, indexes, and FKs
  • Adds Back the old string columns PrimaryContact and SecondaryContact
protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Remove foreign keys & indexes
    migrationBuilder.DropForeignKey("FK_FieldOffice_Employee_DirectorId", "irby", "FieldOffice");
    // ... drop other FKs & indexes similarly

    // 2️⃣ Drop GUID columns
    migrationBuilder.DropColumn("DirectorId", "irby", "FieldOffice");
    // ... drop PrimaryContactId, SecondaryContactId

    // 3️⃣ Re-add old string columns
    migrationBuilder.AddColumn<string>("PrimaryContact", "irby", "FieldOffice", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>("SecondaryContact", "irby", "FieldOffice", type: "nvarchar(max)", nullable: true);
}

Dependencies & Technologies

Technology Version / Details
.NET SDK 6.0+
Entity Framework Core 6.x (Microsoft.EntityFrameworkCore)
Database SQL Server (uniqueidentifier, nvarchar(max))

Data Models & Schema Changes

FieldOffice: Before vs. After

Column Name Type Before After
PrimaryContact nvarchar(max) ✅ Present ❌ Removed
SecondaryContact nvarchar(max) ✅ Present ❌ Removed
DirectorId uniqueidentifier ❌ N/A ✅ Added (FK→Employee)
PrimaryContactId uniqueidentifier ❌ N/A ✅ Added (FK→Employee)
SecondaryContactId uniqueidentifier ❌ N/A ✅ Added (FK→Employee)

Employee Relationship

All three new GUID columns reference irby.Employee(EmployeeId):

erDiagram
    Employee ||--o{ FieldOffice : "as Director/Contact"
    FieldOffice {
        Guid DirectorId
        Guid PrimaryContactId
        Guid SecondaryContactId
    }
    Employee {
        Guid EmployeeId PK
        string Name
        ...
    }

Class Diagram (UML)

@startuml
class Migration {
    +void Up(MigrationBuilder)
    +void Down(MigrationBuilder)
}
class UpdateFieldOfficeTableWithNewColumns {
    +void Up(MigrationBuilder)
    +void Down(MigrationBuilder)
}
Migration <|-- UpdateFieldOfficeTableWithNewColumns
class MigrationBuilder {
    +DropColumn(...)
    +AddColumn<T>(...)
    +CreateIndex(...)
    +AddForeignKey(...)
    ...
}
UpdateFieldOfficeTableWithNewColumns ..> MigrationBuilder : uses
@enduml

How It Works: Step by Step

  1. Build Migration
    • EF Core scaffolds this class when you run:
      dotnet ef migrations add UpdateFieldOfficeTableWithNewColumns
      
  2. Apply Schema Changes
    • The Up() method executes SQL DDL statements to modify the database.
  3. Roll Back
    • If needed, dotnet ef database update <PreviousMigration> invokes Down() to revert changes.

Usage & Examples

1. Generating & Applying

# 1️⃣ Generate the migration (already done)
dotnet ef migrations add UpdateFieldOfficeTableWithNewColumns

# 2️⃣ Apply to your database
dotnet ef database update

2. Verify Schema

-- SQL Server: check new columns
SELECT TOP 0 DirectorId, PrimaryContactId, SecondaryContactId
FROM irby.FieldOffice;

Error Handling & Rollback

  • Foreign Key Violations: Ensure target Employee rows exist or allow nulls before migration.
  • Data Loss: Dropping string columns will remove any existing free-text contacts—consider data migration scripts.
  • Rollback Safety: Down() reinstates old columns but does not repopulate them.

Conclusion 🎯

This migration transitions free-text contacts to relational references, improving data integrity and enabling richer querying of directors and contacts in your FieldOffice table. Remember to handle existing data and test the migration in a staging environment before deploying to production.


Happy coding! 🚀