📄 Documentation: 20240201134411_UpdateFieldOfficeTableWithNewColumns.cs
📑 Table of Contents
- Overview & Purpose
- Key Functional Areas
- Dependencies & Technologies
- Data Models & Schema Changes
- Class Diagram (UML)
- How It Works: Step by Step
- Usage & Examples
- Error Handling & Rollback
- 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
PrimaryContactandSecondaryContact
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
- Build Migration
- EF Core scaffolds this class when you run:
dotnet ef migrations add UpdateFieldOfficeTableWithNewColumns
- EF Core scaffolds this class when you run:
- Apply Schema Changes
- The
Up()method executes SQL DDL statements to modify the database.
- The
- Roll Back
- If needed,
dotnet ef database update <PreviousMigration>invokesDown()to revert changes.
- If needed,
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
Employeerows 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! 🚀