📚 Documentation: Migration “20240130145814_MoreUpdatesToJobTable4.cs”

Location: IrbyImaging.DbContext/Migrations/20240130145814_MoreUpdatesToJobTable4.cs
Type: Entity Framework Core Migration
Purpose: Change data types of key columns in ODSJobs and irby.Job tables


📑 Table of Contents

  1. 🚀 Overview & Purpose
  2. 🛠 Dependencies & Technologies
  3. 📁 Migration Class Structure
  4. 🔄 Up Method – Applying Changes
    • 4.1 🔹 ODSJobs Table Updates
    • 4.2 🔹 irby.Job Table Updates
  5. ↩ Down Method – Reverting Changes
  6. 📊 Data Model Impact (ERD/UML)
  7. 🏃‍♂️ Usage & Execution
  8. 💡 Considerations & Best Practices
  9. 📋 Error Handling & Return Types
  10. 🔗 Third‑party Integrations
  11. 🧩 Sample Code Blocks & Examples
  12. 🏁 Conclusion

1. 🚀 Overview & Purpose

This migration adjusts the column data types in two tables:

  • ODSJobs
    • Convert four numeric columns (decimal(18,0)) to GUIDs (uniqueidentifier).
  • irby.Job
    • Convert four date‑related columns from unstructured text (nvarchar(max)) to strongly‑typed DateTime (datetime2).

Why?

  • 🎯 Data Integrity: GUIDs improve cross‑system identifier consistency.
  • 🗓 Type Safety: DateTime fields enable calendar operations, sorting & range queries.

2. 🛠 Dependencies & Technologies

Category Details
Framework .NET 6+ / .NET Core
ORM Entity Framework Core (EF Core)
Database Microsoft SQL Server
C# Version 10.0+
Migration API Microsoft.EntityFrameworkCore.Migrations

3. 📁 Migration Class Structure

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    /// <inheritdoc />
    public partial class MoreUpdatesToJobTable4 : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // ALTER COLUMN calls...
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // REVERT COLUMN calls...
        }
    }
}
  • partial class MoreUpdatesToJobTable4 : Migration
    Defines Up and Down methods to apply/revert schema changes.
  • #nullable disable
    Ensures legacy nullability behavior.

4. 🔄 Up Method – Applying Changes

The Up method contains a series of AlterColumn calls to change existing columns.

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ ODSJobs table: convert decimals → GUIDs
    migrationBuilder.AlterColumn<Guid>(
        name: "SUPERINTENDENT",
        table: "ODSJobs",
        type: "uniqueidentifier",
        nullable: true,
        oldClrType: typeof(decimal),
        oldType: "decimal(18,0)",
        oldNullable: true);

    // ... PROJECT_ESTIMATOR, MANAGER, CUSTOMER_NUMBER ...

    // 2️⃣ irby.Job table: convert strings → DateTime
    migrationBuilder.AlterColumn<DateTime>(
        name: "DatePlannedStart",
        schema: "irby",
        table: "Job",
        type: "datetime2",
        nullable: true,
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true);

    // ... DatePlannedComplete, DateActualStart, DateActualComplete ...
}

4.1 🔹 ODSJobs Table Updates

Column Old Type New Type Nullable
SUPERINTENDENT decimal(18,0) uniqueidentifier Yes
PROJECT_ESTIMATOR decimal(18,0) uniqueidentifier Yes
MANAGER decimal(18,0) uniqueidentifier Yes
CUSTOMER_NUMBER decimal(18,0) uniqueidentifier Yes

Why GUIDs?

  • Globally unique across distributed systems
  • Non‑guessable, enhancing security for record identifiers

4.2 🔹 irby.Job Table Updates

Column Old Type New Type Nullable
DatePlannedStart nvarchar(max) datetime2 Yes
DatePlannedComplete nvarchar(max) datetime2 Yes
DateActualStart nvarchar(max) datetime2 Yes
DateActualComplete nvarchar(max) datetime2 Yes

Benefits of DateTime

  • Native date manipulations & validations
  • Improved indexing & range queries

5. ↩ Down Method – Reverting Changes

The Down method rolls back the changes, reverting each column to its original type:

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ ODSJobs: GUIDs → decimal
    migrationBuilder.AlterColumn<decimal>(
        name: "SUPERINTENDENT",
        table: "ODSJobs",
        type: "decimal(18,0)",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier",
        oldNullable: true);

    // ... PROJECT_ESTIMATOR, MANAGER, CUSTOMER_NUMBER ...

    // 2️⃣ irby.Job: DateTime → string
    migrationBuilder.AlterColumn<string>(
        name: "DatePlannedStart",
        schema: "irby",
        table: "Job",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(DateTime),
        oldType: "datetime2",
        oldNullable: true);

    // ... DatePlannedComplete, DateActualStart, DateActualComplete ...
}

6. 📊 Data Model Impact (ERD/UML)

Before Migration

erDiagram
    ODSJobs {
      decimal SUPERINTENDENT
      decimal PROJECT_ESTIMATOR
      decimal MANAGER
      decimal CUSTOMER_NUMBER
    }
    irby_Job {
      string DatePlannedStart
      string DatePlannedComplete
      string DateActualStart
      string DateActualComplete
    }

After Migration

erDiagram
    ODSJobs {
      uniqueidentifier SUPERINTENDENT
      uniqueidentifier PROJECT_ESTIMATOR
      uniqueidentifier MANAGER
      uniqueidentifier CUSTOMER_NUMBER
    }
    irby_Job {
      datetime2 DatePlannedStart
      datetime2 DatePlannedComplete
      datetime2 DateActualStart
      datetime2 DateActualComplete
    }

7. 🏃‍♂️ Usage & Execution

  1. Add Migration (already done):
    dotnet ef migrations add MoreUpdatesToJobTable4
    
  2. Apply to Database:
    dotnet ef database update
    
  3. Verify by inspecting column types in SQL Server Management Studio or via a query:
    EXEC sp_help 'ODSJobs';
    EXEC sp_help 'irby.Job';
    

8. 💡 Considerations & Best Practices

  • Data Compatibility:
    • Converting existing decimal IDs to GUIDs requires either empty GUIDs or mapping logic.
    • Converting free‑text dates may fail if format is invalid – consider a pre‑migration data cleanup.
  • Backups: Always back up your database before running destructive migrations.
  • Testing: Run migration in a staging environment to catch type‑conversion errors.
  • Nullability: All columns remain nullable to avoid immediate data-loss.

9. 📋 Error Handling & Return Types

  • EF Core Migrations throw exceptions on invalid SQL or type mismatches.
  • No explicit return values (void methods); success indicated by no exception.

10. 🔗 Third‑party Integrations

  • None. This migration uses only EF Core’s built‑in migration APIs.

11. 🧩 Sample Code Blocks & Examples

Inspecting a Column in SQL

-- Check the new data type of SUPERINTENDENT
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ODSJobs' AND COLUMN_NAME = 'SUPERINTENDENT';

12. 🏁 Conclusion

This migration brings strong typing and better data integrity to key parts of the Jobs domain:

  • ✔️ Numeric → GUID for identifiers
  • ✔️ String → DateTime for scheduling fields

By following best practices—backups, testing, and data cleanup—you can ensure a smooth rollout of these schema improvements. 🎉


Developed by the IrbyImaging team for robust, maintainable, and type‑safe database schemas.