📑 Migration Documentation: 20240131195020_AddMoreColumnsToJobTable.cs


📋 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Migration Details
  5. Data Models Changes
  6. UML Diagram
  7. Sample Usage & Examples
  8. Conclusion

🌟 1. Overview & Purpose

This Entity Framework Core migration adds new descriptive columns to two existing tables:

  • ODSJobs (default schema)
  • Job (in the irby schema)

Why?
The additional fields capture more context around each job—such as customer name, project estimator, and superintendent—enabling richer reporting and data analysis.


🛠️ 2. Dependencies & Technologies

Technology / Package Version/Notes
.NET / C# .NET 6.0+ or compatible
Entity Framework Core (EF Core) Microsoft.EntityFrameworkCore ≥ 6.x
SQL Server (or any EF Core provider) The migration uses nvarchar(max) column types
Dotnet-CLI (optional) dotnet ef tool for generating/applying migrations

🗝️ 3. Key Functional Areas

  • Database Schema Evolution
    Adding and removing columns via EF Core migrations.
  • Readiness for Extended Reporting
    New columns surface key stakeholder names against each Job.
  • Safe Rollback
    Down method cleans up by removing these columns if needed.

🔍 4. Migration Details

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    public partial class AddMoreColumnsToJobTable : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Adds columns to ODSJobs
            migrationBuilder.AddColumn<string>(
                name: "CUSTOMER",
                table: "ODSJobs",
                type: "nvarchar(max)",
                nullable: true);

            // ... other ODSJobs columns ...

            // Adds columns to irby.Job
            migrationBuilder.AddColumn<string>(
                name: "CustomerName",
                schema: "irby",
                table: "Job",
                type: "nvarchar(max)",
                nullable: true);

            // ... other irby.Job columns ...
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Drops columns from ODSJobs
            migrationBuilder.DropColumn(name: "CUSTOMER", table: "ODSJobs");
            // ... other ODSJobs columns ...
            
            // Drops columns from irby.Job
            migrationBuilder.DropColumn(
                name: "CustomerName",
                schema: "irby",
                table: "Job");
            // ... other irby.Job columns ...
        }
    }
}

4.1 Up Method (Applying Changes) 🚀

  • Adds the following nullable nvarchar(max) columns to table ODSJobs:

    • CUSTOMER
    • MANAGER_NAME
    • PROJECT_ESTIMATOR_NAME
    • SUPERINTENDENT_NAME
  • Adds the following nullable nvarchar(max) columns to table irby.Job:

    • CustomerName
    • FieldOfficeName
    • ManagerName
    • ProjectEstimatorName
    • SuperintendentName

4.2 Down Method (Reverting Changes) 🔄

  • Removes each of the columns added in the Up method, ensuring a clean rollback.

🗂️ 5. Data Models Changes

Table (Schema) Column Type Nullable Purpose
ODSJobs CUSTOMER nvarchar(max) Yes Customer identifier
ODSJobs MANAGER_NAME nvarchar(max) Yes Job manager’s name
ODSJobs PROJECT_ESTIMATOR_NAME nvarchar(max) Yes Project estimator’s name
ODSJobs SUPERINTENDENT_NAME nvarchar(max) Yes Superintendent’s name
irby.Job CustomerName nvarchar(max) Yes Customer display name
irby.Job FieldOfficeName nvarchar(max) Yes Name of the field office
irby.Job ManagerName nvarchar(max) Yes Job manager’s name (consistent casing)
irby.Job ProjectEstimatorName nvarchar(max) Yes Project estimator’s name (consistent casing)
irby.Job SuperintendentName nvarchar(max) Yes Superintendent’s name (consistent casing)

Note:
The dual presence—in both ODSJobs and irby.Job—supports different layers of the system (ODS vs. relational domain).


📈 6. UML Diagram

classDiagram
    class ODSJobs {
        +string? CUSTOMER
        +string? MANAGER_NAME
        +string? PROJECT_ESTIMATOR_NAME
        +string? SUPERINTENDENT_NAME
    }

    class irby.Job {
        +string? CustomerName
        +string? FieldOfficeName
        +string? ManagerName
        +string? ProjectEstimatorName
        +string? SuperintendentName
    }

    %% Relationship (if any) can be added here
    ODSJobs ..> irby.Job : sync/transform

🔧 7. Sample Usage & Examples

  1. Create the Migration
    dotnet ef migrations add AddMoreColumnsToJobTable
    
  2. Apply to Database
    dotnet ef database update
    
  3. Querying the New Columns
    using var context = new IrbyDbContext();
    var jobs = context.Jobs
        .Select(j => new {
            j.Id,
            j.CustomerName,
            j.ManagerName,
            j.SuperintendentName
        })
        .ToList();
    
  4. Rolling Back
    dotnet ef database update LastGoodMigrationName
    

🏁 8. Conclusion

This migration empowers the application with richer metadata around Jobs by adding key stakeholder name columns.

  • Forward: Enables advanced reporting and user-friendly displays.
  • Backward: Guaranteed safe rollback through the Down method.

🎉 Your database is now ready to capture more detailed job information!


Generated by the IrbyImaging team’s EF Core migration standards.