📚 Documentation: UpdateEmployeeTableWithNewColumns4 Migration


Table of Contents

  1. Overview & Purpose
  2. Migration at a Glance
  3. Key Functional Areas
  4. Detailed Breakdown: Up() & Down() Methods
  5. Dependencies & Technologies
  6. Data Model Changes
  7. UML Class Diagram
  8. Usage & Execution
  9. Rollback & Reversion
  10. Conclusion

1. Overview & Purpose

File:

IrbyImaging.DbContext/Migrations/20240201193312_UpdateEmployeeTableWithNewColumns4.cs

This Entity Framework Core Migration class changes the data type of the ProjectManager column in the JobAndFieldOfficeMapping table from a decimal to string (nvarchar(max)).
It consists of:

  • Up(): Applies the change
  • Down(): Reverts the change

✨ Why is this needed?

  • The application team decided that storing a project manager’s identifier or name as a decimal was too restrictive.
  • Switching to string allows alphanumeric IDs, names, or GUIDs.

2. Migration at a Glance

Method Action Column Old Type New Type
Up Alter column to allow text-based values ProjectManager decimal(18,2) nvarchar(max)
Down Revert column back to numeric format with default 0.0 ProjectManager nvarchar(max) decimal(18,2)

3. Key Functional Areas

  • 🔄 Schema Evolution
    Using EF Core’s migration framework to evolve the database schema safely.
  • ⚙️ Type Conversion
    Converting numeric data to text-based to accommodate a broader range of identifiers.
  • 🛠️ Reversible Operations
    Providing both Up and Down methods ensures easy rollback.

4. Detailed Breakdown: Up() & Down() Methods

4.1 Up(MigrationBuilder)

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "ProjectManager",
        table: "JobAndFieldOfficeMapping",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(decimal),
        oldType: "decimal(18,2)");
}
  • AlterColumn<string>: Changes the CLR type to string.
  • nullable: true: Allows NULL values (optional).
  • type: "nvarchar(max)": SQL Server type for unlimited-length Unicode text.
  • oldClrType & oldType: Metadata for EF to track the prior state.

4.2 Down(MigrationBuilder)

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<decimal>(
        name: "ProjectManager",
        table: "JobAndFieldOfficeMapping",
        type: "decimal(18,2)",
        nullable: false,
        defaultValue: 0m,
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true);
}
  • Revert: Returns the column to decimal(18,2).
  • nullable: false & defaultValue: 0m: Ensures no NULLs — a default of 0.00 is applied for existing rows.

5. Dependencies & Technologies

  • .NET 6+ / .NET Core
  • Entity Framework Core
    • Microsoft.EntityFrameworkCore
    • Microsoft.EntityFrameworkCore.Migrations
  • SQL Server (for nvarchar(max) & decimal(18,2) types)

6. Data Model Changes

This migration does not introduce new tables or relationships. It only changes a column’s data type:

Table Column Before After
JobAndFieldOfficeMapping ProjectManager decimal(18,2), NOT NULL nvarchar(max), NULLABLE

7. UML Class Diagram

+----------------------------------------------------+
|                 UpdateEmployeeTableWithNewColumns4 |
+----------------------------------------------------+
| <<abstract>> Migration                              |
| + Up(migrationBuilder: MigrationBuilder): void      |
| + Down(migrationBuilder: MigrationBuilder): void    |
+----------------------------------------------------+
  • Inherits from Microsoft.EntityFrameworkCore.Migrations.Migration
  • Overrides Up and Down methods

8. Usage & Execution

  1. Add Migration
    dotnet ef migrations add UpdateEmployeeTableWithNewColumns4
    
  2. Apply Migration
    dotnet ef database update
    
  3. Confirm Schema Change
    -- In SQL Server Management Studio
    sp_help 'JobAndFieldOfficeMapping';
    

9. Rollback & Reversion

To revert this specific migration:

dotnet ef database update 20240201193311_PreviousMigrationName

This will trigger the Down() method and restore the column to decimal(18,2) with default 0.0.


10. Conclusion

🚀 UpdateEmployeeTableWithNewColumns4 is a lightweight, targeted migration that evolves the data model by changing one column’s data type. It demonstrates best practices in:

  • Reversible migrations
  • Clear metadata definitions
  • Minimal-impact deployment

Next Steps

  • Review associated application code to ensure it treats ProjectManager as a string (e.g., view models, DTOs).
  • Add validation logic if only specific patterns (GUIDs, user names) are allowed.

Happy Coding! 🎉