📄 Documentation: AddMoreColumnsToClassAndCostCodes Migration


📚 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Changes
  5. Migration Methods
  6. Index & Foreign Key
  7. UML Diagram
  8. Usage Examples
  9. Best Practices & Notes
  10. Conclusion

🔍 Overview & Purpose

This Entity Framework Core Migration adds additional columns to the JobClassification table and a foreign‑key relationship between CostCode and Job.

  • Why?
    • Enrich JobClassification records with geographical context (City, County, State).
    • Associate each CostCode with a parent Job, enforcing referential integrity (cascade delete).

🛠️ Dependencies & Technologies

Technology Version/Notes
.NET (Core/.NET 5+) EF Core Migrations
Microsoft.EntityFrameworkCore MigrationBuilder API
SQL Server (or other RDBMS) nvarchar(max), uniqueidentifier

Note: Migration is declared under the schema irby.


🎯 Key Functional Areas

  1. Schema Evolution
    • Add three nullable nvarchar(max) columns to JobClassification.
  2. Data Integrity
    • Add non‑nullable JobId foreign key to CostCode.
    • Enforce cascade delete when a parent Job is removed.
  3. Rollback Support
    • Provide a matching Down method to revert schema changes.

📦 Data Model Changes

Table Column Type Nullable Default
JobClassification City nvarchar(max) Yes N/A
County nvarchar(max) Yes N/A
State nvarchar(max) Yes N/A
CostCode JobId uniqueidentifier No 00000000-0000-0000-0000-000000000000

🏗️ Migration Methods

Up Method

  • Purpose: Apply schema changes.
  • Key Steps:
    1. Add columns to JobClassification.
    2. Add JobId column to CostCode with a default GUID.
    3. Create index on CostCode.JobId for performance.
    4. Define foreign key constraint to Job (cascade delete).
protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Add geographical fields
    migrationBuilder.AddColumn<string>(
        name: "City",
        schema: "irby",
        table: "JobClassification",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "County",
        schema: "irby",
        table: "JobClassification",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "State",
        schema: "irby",
        table: "JobClassification",
        type: "nvarchar(max)",
        nullable: true);

    // 2️⃣ Add JobId FK field
    migrationBuilder.AddColumn<Guid>(
        name: "JobId",
        schema: "irby",
        table: "CostCode",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty);

    // 3️⃣ Create an index for faster lookups
    migrationBuilder.CreateIndex(
        name: "IX_CostCode_JobId",
        schema: "irby",
        table: "CostCode",
        column: "JobId");

    // 4️⃣ Enforce referential integrity (Cascade on delete)
    migrationBuilder.AddForeignKey(
        name: "FK_CostCode_Job_JobId",
        schema: "irby",
        table: "CostCode",
        column: "JobId",
        principalSchema: "irby",
        principalTable: "Job",
        principalColumn: "JobId",
        onDelete: ReferentialAction.Cascade);
}

Down Method

  • Purpose: Revert changes if rollback is needed.
  • Key Steps:
    1. Drop FK and index.
    2. Remove added columns.
protected override void Down(MigrationBuilder migrationBuilder)
{
    // Remove foreign key & index
    migrationBuilder.DropForeignKey(
        name: "FK_CostCode_Job_JobId",
        schema: "irby",
        table: "CostCode");

    migrationBuilder.DropIndex(
        name: "IX_CostCode_JobId",
        schema: "irby",
        table: "CostCode");

    // Remove the added columns
    migrationBuilder.DropColumn(name: "City",        schema: "irby", table: "JobClassification");
    migrationBuilder.DropColumn(name: "County",      schema: "irby", table: "JobClassification");
    migrationBuilder.DropColumn(name: "State",       schema: "irby", table: "JobClassification");
    migrationBuilder.DropColumn(name: "JobId",       schema: "irby", table: "CostCode");
}

🔗 Index & Foreign Key

Constraint Name Table Column Reference Table On Delete
IX_CostCode_JobId CostCode JobId — —
FK_CostCode_Job_JobId CostCode JobId Job(JobId) Cascade

Why create an index? Improves JOIN/filter performance on CostCode.JobId.


📈 UML Diagram

classDiagram
    direction LR

    class Job {
        +Guid JobId
        // ... other properties ...
    }

    class CostCode {
        +Guid CostCodeId
        +Guid JobId
        // ... other properties ...
    }

    class JobClassification {
        +Guid JobClassificationId
        +string City
        +string County
        +string State
        // ... other properties ...
    }

    Job "1" <|-- "0..*" CostCode : has

🚀 Usage Examples

  1. Add & Apply Migration

    # Add a new migration
    dotnet ef migrations add AddMoreColumnsToClassAndCostCodes \
        --context IrbyDbContext --schema irby
    
    # Apply to database
    dotnet ef database update --context IrbyDbContext
    
  2. Inspect Database

    -- Verify new columns in SQL Server
    SELECT TOP 5 City, County, State
    FROM irby.JobClassification;
    
    -- Verify CostCode → Job relationship
    SELECT c.CostCodeId, c.JobId, j.JobId
    FROM irby.CostCode c
    JOIN irby.Job j ON c.JobId = j.JobId;
    
  3. Rollback Migration

    dotnet ef database update LastGoodMigrationName
    

💡 Best Practices & Notes

  • Nullability:
    • Geographical fields are nullable—existing records won’t break.
  • Default GUID:
    • Guid.Empty ensures existing CostCode rows receive a placeholder; consider back‑filling real values.
  • Cascade Deletes:
    • Use with caution: deleting a Job will automatically remove linked CostCode rows.
  • Version Control:
    • Always check migrations into source control to keep schema changes tracked.
  • Testing:
    • Write integration tests to validate referential constraints and data integrity after migration.

🎯 Conclusion

This migration enhances your data model by:

  • Enriching job classifications with location data.
  • Enforcing a strong parent‑child relationship between jobs and cost codes.

By following the examples and best practices above, you can safely evolve your database schema and maintain data integrity throughout application development. 🚀