📖 Documentation: Migration “MoreUpdatesToJobTable3”

File: IrbyImaging.DbContext/Migrations/20240130143555_MoreUpdatesToJobTable3.cs
Type: Entity Framework Core Migration
Created: January 30, 2024
Namespace: IrbyImaging.DbContext.Migrations


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model: Job Table Changes
  5. Migration Workflow & Usage Examples
  6. UML Diagram
  7. Best Practices & Notes
  8. Conclusion

🧐 Overview & Purpose

This migration MoreUpdatesToJobTable3 is designed to evolve the database schema for the Job table within the irby schema. It focuses on:

  • Renaming an existing foreign-key column from ProjectManagerId to ProjectEstimatorId.
  • Adding four new string-based date fields to track planned vs. actual start/complete dates.

This reflects a business requirement to:

  • Differentiate between estimators and managers.
  • Capture planned and actual dates as free-form (string) fields, likely for flexibility or custom formatting.

🛠️ Dependencies & Technologies

Dependency/Technology Version Purpose
Microsoft.EntityFrameworkCore.Migrations 6.x / 7.x Core migration support (Up/Down methods)
.NET SDK 6.0 / 7.0 Runtime & tooling for EF Core
SQL Server 2019 / 2022 Target relational database (NVARCHAR fields)

Note: The code uses the [schema: "irby"] attribute to target a custom schema. Ensure your database user has permission to modify that schema.


🔑 Key Functional Areas

3.1 Renaming a Column

migrationBuilder.RenameColumn(
    name: "ProjectManagerId",
    schema: "irby",
    table: "Job",
    newName: "ProjectEstimatorId"
);
  • What it does:
    Renames the existing column ProjectManagerId → ProjectEstimatorId without losing data.
  • Why:
    To reflect a change in domain language: estimators instead of managers in project roles.

3.2 Adding New Columns

Adds four new fields of type nvarchar(max) (C# → string?):

Column Name Type Nullable Description
DatePlannedStart nvarchar(max) Yes Planned start date (free text)
DatePlannedComplete nvarchar(max) Yes Planned completion date
DateActualStart nvarchar(max) Yes Actual start date
DateActualComplete nvarchar(max) Yes Actual completion date

Code Snippet:

migrationBuilder.AddColumn<string>(
    name: "DatePlannedStart",
    schema: "irby",
    table: "Job",
    type: "nvarchar(max)",
    nullable: true
);
// ... similarly for the other three columns

💡 Tip: Consider switching to DateTime? in the entity and a matching SQL datetime2 column if you require strict date handling.


3.3 Reverting Changes (Down Method)

The Down method is the inverse:

  1. Drop all four newly added columns.
  2. Rename back ProjectEstimatorId → ProjectManagerId.
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropColumn(name: "DateActualComplete", schema: "irby", table: "Job");
    // ... drop other three columns
    migrationBuilder.RenameColumn(
        name: "ProjectEstimatorId",
        schema: "irby",
        table: "Job",
        newName: "ProjectManagerId"
    );
}

🗃️ Data Model: Job Table Changes

Below is a diff of the Job table schema before and after this migration.

Column Before After
ProjectManagerId nvarchar(450) (FK) Renamed to ProjectEstimatorId
DatePlannedStart Not Present nvarchar(max)
DatePlannedComplete Not Present nvarchar(max)
DateActualStart Not Present nvarchar(max)
DateActualComplete Not Present nvarchar(max)

🚀 Migration Workflow & Usage Examples

  1. Add Migration

    dotnet ef migrations add MoreUpdatesToJobTable3 \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  2. Apply to Database

    dotnet ef database update \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  3. Verify

    SELECT TOP 5 *
    FROM irby.Job;
    

📊 UML Diagram

classDiagram
    class Job {
        +string Id
        +string? ProjectEstimatorId  <<Renamed>>
        +string? DatePlannedStart
        +string? DatePlannedComplete
        +string? DateActualStart
        +string? DateActualComplete
        // ... other existing properties
    }

🛠️ Note: This diagram highlights only the columns affected by this migration.


💡 Best Practices & Notes

  • Type Safety:
    Although nvarchar(max) and string provide maximum flexibility, consider using typed DateTime? properties to leverage database date functions and avoid formatting issues.
  • Schema Naming:
    The migration uses the "irby" schema. Always ensure migrations respect your database schema separation.
  • Migration Idempotency:
    EF Core ensures that each migration is applied once. Do not manually alter the __EFMigrationsHistory table.

🏁 Conclusion

This migration is a schema evolution step for the Job table, aligning the data model with updated business terminology and tracking requirements. By renaming the ProjectManager column and adding planned/actual date columns, it supports enhanced reporting and scheduling capabilities within the application.

Happy coding! 😃✨