πŸ“š AddColumnsToJobTable Migration Documentation


Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
  3. Dependencies & Technologies
  4. Migration Class: AddColumnsToJobTable
  5. Schema Changes Summary πŸ“Š
  6. Applying the Migration πŸš€
  7. UML Diagram: Updated Job Entity πŸ“ˆ
  8. Common Scenarios & Usage Examples πŸ› οΈ
  9. Conclusion 🎯

1. Overview & Purpose

This migration AddColumnsToJobTable extends the existing Job table in the irby schema by adding three nullable GUID columns:

  • ManagerId
  • ProjectManagerId
  • SuperintendentId

These new fields link each job record to different user roles, enabling richer relationship mapping (e.g., one job can now track which manager, project manager, or superintendent is responsible).


2. Key Functional Areas

  • Database Schema Evolution
    Introduces new columns without dropping existing data.

  • Maintainability
    Uses EF Core’s MigrationBuilder for reversible changes.

  • Role Tracking
    Allows assignment of distinct user roles to a job.


3. Dependencies & Technologies

Technology/Library Version Purpose
Microsoft.EntityFrameworkCore β‰₯ 6.x Core ORM & Migrations
Microsoft.EntityFrameworkCore.Tools β‰₯ 6.x CLI tooling for generating/applying Migrations
.NET (Core/5/6/7) ~6.0 or later Runtime hosting the DbContext

No third‑party integrations are required for this migration.


4. Migration Class: AddColumnsToJobTable

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    /// <inheritdoc />
    public partial class AddColumnsToJobTable : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<Guid>(
                name: "ManagerId",
                schema: "irby",
                table: "Job",
                type: "uniqueidentifier",
                nullable: true);

            migrationBuilder.AddColumn<Guid>(
                name: "ProjectManagerId",
                schema: "irby",
                table: "Job",
                type: "uniqueidentifier",
                nullable: true);

            migrationBuilder.AddColumn<Guid>(
                name: "SuperintendentId",
                schema: "irby",
                table: "Job",
                type: "uniqueidentifier",
                nullable: true);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "ManagerId",
                schema: "irby",
                table: "Job");

            migrationBuilder.DropColumn(
                name: "ProjectManagerId",
                schema: "irby",
                table: "Job");

            migrationBuilder.DropColumn(
                name: "SuperintendentId",
                schema: "irby",
                table: "Job");
        }
    }
}

4.1 Up Method

  • Purpose: Adds three nullable uniqueidentifier columns.
  • Key API:
    • migrationBuilder.AddColumn<T>(...)
  • Parameters:
    • name: Column name in the database
    • schema: Database schema ("irby")
    • table: Target table ("Job")
    • type: SQL type ("uniqueidentifier")
    • nullable: Allows NULL values

4.2 Down Method

  • Purpose: Reverts the above changes by dropping the added columns.
  • Key API:
    • migrationBuilder.DropColumn(...)
  • Safe Rollback: Ensures database can be reverted to pre-migration state.

5. Schema Changes Summary

Column Name Data Type Nullable Schema Table
ManagerId uniqueidentifier Yes irby Job
ProjectManagerId uniqueidentifier Yes irby Job
SuperintendentId uniqueidentifier Yes irby Job

6. Applying the Migration πŸš€

  1. Add & Commit Migration
    dotnet ef migrations add AddColumnsToJobTable
    
  2. Apply to Database
    dotnet ef database update
    
  3. Verify
    • Inspect the Job table in your DB tool (e.g., SSMS, Azure Data Studio).
    • Ensure the new columns appear under irby.Job.

7. UML Diagram: Updated Job Entity πŸ“ˆ

classDiagram
    class Job {
        +Guid? ManagerId
        +Guid? ProjectManagerId
        +Guid? SuperintendentId
        -- other existing properties --
    }

Note: The above diagram shows only the newly added properties.


8. Common Scenarios & Usage Examples πŸ› οΈ

  • Assigning Roles in Application Code:

    var job = await _context.Jobs.FindAsync(jobId);
    job.ManagerId = currentUser.Id;
    job.ProjectManagerId = projectManagerUser.Id;
    job.SuperintendentId = superintendentUser.Id;
    await _context.SaveChangesAsync();
    
  • Querying by Role:

    // Find all jobs overseen by a specific manager
    var jobsByManager = _context.Jobs
      .Where(j => j.ManagerId == managerId)
      .ToList();
    
  • Rollback Scenario: If you must revert:

    dotnet ef database update LastGoodMigrationName
    

9. Conclusion 🎯

This migration is a non-breaking schema update that enriches the Job entity with three new GUID-based associations. By following the outlined steps, you can safely apply or rollback the changes, ensuring your database evolves alongside application requirements.

Happy coding! πŸš€