📚 Documentation: 20240124150448_AddColumnsToJobAndFieldOfficeTables.cs

File Path:
IrbyImaging.DbContext/Migrations/20240124150448_AddColumnsToJobAndFieldOfficeTables.cs


📖 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Schema Changes
  5. Indexes & Foreign Key Constraints
  6. Up Method Flow
  7. Down Method Flow
  8. UML Class Diagram
  9. Usage & Examples
  10. Conclusion

1. Overview & Purpose

This Entity Framework Core migration adds a series of new columns, indexes, and foreign‐key constraints to two existing tables (FieldOffice and Job) in the irby schema:

  • FieldOffice: enriches the table with contact details, addresses, and links to Employee roles (Lead & Assistant PM).
  • Job: introduces a FieldOfficeId foreign key to assign each job to a specific field office.

2. Dependencies & Technologies

  • .NET 6+ or 7
  • Entity Framework Core
  • Relational database (e.g., SQL Server)
  • C# 10+
using System;
using Microsoft.EntityFrameworkCore.Migrations;

3. Key Functional Areas

  • 🔄 Schema Alteration: Add/Remove columns
  • 🔍 Index Management: Improve query performance on FK columns
  • 🔒 Referential Integrity: Establish FK constraints between tables

4. Schema Changes

4.1. FieldOffice Table Additions

Column Name Type Nullable Description
AsstProjectManagerId uniqueidentifier Yes FK → Employee.EmployeeId (Assistant PM)
LeadId uniqueidentifier Yes FK → Employee.EmployeeId (Field Office Lead)
FaxNumber nvarchar(max) Yes Office fax number
MailingAddress nvarchar(max) Yes Mailing address
ShippingAddress nvarchar(max) Yes Shipping address
OfficePhone nvarchar(max) Yes Main office phone
OfficeExtension nvarchar(max) Yes Phone extension
PrimaryContact nvarchar(max) Yes Name of primary contact
PrimaryContactPhone nvarchar(max) Yes Phone of primary contact
SecondaryContact nvarchar(max) Yes Name of secondary contact
SecondaryContactPhone nvarchar(max) Yes Phone of secondary contact
ProjectManagerPhone nvarchar(max) Yes Phone of project manager
SuperintendentPhone nvarchar(max) Yes Superintendent’s phone

4.2. Job Table Additions

Column Name Type Nullable Description
FieldOfficeId uniqueidentifier Yes FK → FieldOffice.Id (assigns job location)

5. Indexes & Foreign Key Constraints

🔍 Indexes Created

Index Name Table Column
IX_FieldOffice_AsstProjectManagerId FieldOffice AsstProjectManagerId
IX_FieldOffice_LeadId FieldOffice LeadId
IX_Job_FieldOfficeId Job FieldOfficeId

🔒 Foreign Keys Added

Constraint Name From Table From Column To Table To Column
FK_FieldOffice_Employee_AsstProjectManagerId FieldOffice AsstProjectManagerId Employee EmployeeId
FK_FieldOffice_Employee_LeadId FieldOffice LeadId Employee EmployeeId
FK_Job_FieldOffice_FieldOfficeId Job FieldOfficeId FieldOffice Id

6. Up Method Flow

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1. Add FieldOfficeId to Job
    migrationBuilder.AddColumn<Guid>(
        name: "FieldOfficeId",
        schema: "irby",
        table: "Job",
        type: "uniqueidentifier",
        nullable: true);

    // 2. Add multiple contact & role columns to FieldOffice
    migrationBuilder.AddColumn<Guid>( name: "AsstProjectManagerId", schema: "irby", table: "FieldOffice", type: "uniqueidentifier", nullable: true);
    migrationBuilder.AddColumn<Guid>( name: "LeadId",              schema: "irby", table: "FieldOffice", type: "uniqueidentifier", nullable: true);
    migrationBuilder.AddColumn<string>(name: "FaxNumber",          schema: "irby", table: "FieldOffice", type: "nvarchar(max)", nullable: true);
    // …and so on for all 11 additional columns…

    // 3. Create Indexes
    migrationBuilder.CreateIndex(
        name: "IX_Job_FieldOfficeId",
        schema: "irby",
        table: "Job",
        column: "FieldOfficeId");

    migrationBuilder.CreateIndex(
        name: "IX_FieldOffice_AsstProjectManagerId",
        schema: "irby",
        table: "FieldOffice",
        column: "AsstProjectManagerId");

    migrationBuilder.CreateIndex(
        name: "IX_FieldOffice_LeadId",
        schema: "irby",
        table: "FieldOffice",
        column: "LeadId");

    // 4. Add Foreign Keys
    migrationBuilder.AddForeignKey(
        name: "FK_FieldOffice_Employee_AsstProjectManagerId",
        schema: "irby", table: "FieldOffice",
        column: "AsstProjectManagerId",
        principalSchema: "irby", principalTable: "Employee", principalColumn: "EmployeeId");

    migrationBuilder.AddForeignKey(
        name: "FK_FieldOffice_Employee_LeadId",
        schema: "irby", table: "FieldOffice",
        column: "LeadId",
        principalSchema: "irby", principalTable: "Employee", principalColumn: "EmployeeId");

    migrationBuilder.AddForeignKey(
        name: "FK_Job_FieldOffice_FieldOfficeId",
        schema: "irby", table: "Job",
        column: "FieldOfficeId",
        principalSchema: "irby", principalTable: "FieldOffice", principalColumn: "Id");
}

7. Down Method Flow

Reverses the Up actions in strict reverse order:

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1. Drop FK constraints
    migrationBuilder.DropForeignKey(name: "FK_FieldOffice_Employee_AsstProjectManagerId", schema: "irby", table: "FieldOffice");
    migrationBuilder.DropForeignKey(name: "FK_FieldOffice_Employee_LeadId",               schema: "irby", table: "FieldOffice");
    migrationBuilder.DropForeignKey(name: "FK_Job_FieldOffice_FieldOfficeId",                schema: "irby", table: "Job");

    // 2. Drop Indexes
    migrationBuilder.DropIndex(name: "IX_Job_FieldOfficeId",             schema: "irby", table: "Job");
    migrationBuilder.DropIndex(name: "IX_FieldOffice_AsstProjectManagerId", schema: "irby", table: "FieldOffice");
    migrationBuilder.DropIndex(name: "IX_FieldOffice_LeadId",             schema: "irby", table: "FieldOffice");

    // 3. Drop Columns
    migrationBuilder.DropColumn(name: "FieldOfficeId",            schema: "irby", table: "Job");
    migrationBuilder.DropColumn(name: "AsstProjectManagerId",     schema: "irby", table: "FieldOffice");
    // …and so on for all 13 added columns…
}

8. UML Class Diagram

+------------------------------------------------+
| AddColumnsToJobAndFieldOfficeTables : Migration |
+------------------------------------------------+
| + Up(MigrationBuilder builder)                 |
| + Down(MigrationBuilder builder)               |
+------------------------------------------------+

9. Usage & Examples

  1. Add the Migration
    dotnet ef migrations add AddColumnsToJobAndFieldOfficeTables \
      --context IrbyDbContext
    
  2. Apply to Database
    dotnet ef database update --context IrbyDbContext
    
  3. Verify Schema
    • Inspect irby.FieldOffice to see new columns.
    • Inspect irby.Job to confirm the FieldOfficeId column.

10. Conclusion

✨ This migration extends your domain by capturing richer contact details for field offices and linking jobs to those offices. It also enforces referential integrity with appropriate indexes and foreign keys, ensuring efficient queries and robust data relationships in the IrbyImaging solution.

Happy coding! 🚀