📚 Documentation: 20240124150448_AddColumnsToJobAndFieldOfficeTables.cs
File Path:
IrbyImaging.DbContext/Migrations/20240124150448_AddColumnsToJobAndFieldOfficeTables.cs
📖 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Schema Changes
- Indexes & Foreign Key Constraints
UpMethod FlowDownMethod Flow- UML Class Diagram
- Usage & Examples
- 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 toEmployeeroles (Lead & Assistant PM).Job: introduces aFieldOfficeIdforeign 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
- Add the Migration
dotnet ef migrations add AddColumnsToJobAndFieldOfficeTables \ --context IrbyDbContext - Apply to Database
dotnet ef database update --context IrbyDbContext - Verify Schema
- Inspect
irby.FieldOfficeto see new columns. - Inspect
irby.Jobto confirm theFieldOfficeIdcolumn.
- Inspect
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! 🚀