π 20220516185008_ChangeFieldOfficeTable Migration Documentation
π Table of Contents
- Overview & Purpose
- Key Functional Areas
- Dependencies & Technologies
- Data Models Before & After
- Helper Methods / Utilities
- Error Handling & Return Types
- API Endpoints Overview
- Third-Party Integrations
- Sample Code Blocks
- Usage Examples
- UML Diagram
- Conclusion
π― Overview & Purpose
This Entity Framework Core migration modifies the FieldOffice table schema in the irby schema.
-
Primary Goal:
- Make the three GUID columns (
ManagerId,ProjectManagerId,SuperintendentId) nullable. - Change their delete behavior from Cascade β Restrict.
- Make the three GUID columns (
-
Why?
- Allow creation of a
FieldOfficerecord without immediately assigning employees. - Prevent accidental deletion of an
Employeewho is still referenced by aFieldOffice.
- Allow creation of a
π Key Functional Areas
-
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.
- Drops existing foreign keys on
-
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.
- Reverts the above changes:
βοΈ 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()
- Exposed in
β οΈ 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
- Add Migration
dotnet ef migrations add ChangeFieldOfficeTable - Apply to Database
dotnet ef database update - Verify Schema
- Check
FieldOfficetable in SQL Server Management Studio or Azure Data Studio. - Confirm columns allow NULL and FKs have Restrict on delete.
- Check
π 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
FieldOfficerecords 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. π