πŸ“„ Documentation: UpdateEmployeeTableWithNewColumns5 Migration


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Class Structure
  4. Key Functional Areas
  5. Migration Methods
  6. Data Model Impact
  7. UML Diagram
  8. Usage Examples
  9. Considerations & Best Practices
  10. Conclusion

🧐 Overview & Purpose

This migration class is part of the IrbyImaging application’s database context. It performs a schema change on the JobAndFieldOfficeMapping table, specifically altering the data type of the JobNumber column.

  • Migration Name: UpdateEmployeeTableWithNewColumns5
  • Target Table: JobAndFieldOfficeMapping
  • Column Affected: JobNumber

Why?

To accommodate alphanumeric job numbers (e.g., "A123", "JOB-2024"), the column type is changed from a decimal to a string (nvarchar(max)).


πŸ›  Dependencies & Technologies

Dependency/Technology Purpose
Microsoft.EntityFrameworkCore.Migrations Provides base Migration class and methods to alter schema.
.NET 6+ (C#) Language runtime
SQL Server Database engine (nvarchar, decimal types)

🚧 Migration Class Structure

namespace IrbyImaging.DbContext.Migrations
{
    public partial class UpdateEmployeeTableWithNewColumns5 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder) { … }
        protected override void Down(MigrationBuilder migrationBuilder) { … }
    }
}
  • Namespace: IrbyImaging.DbContext.Migrations
  • Class: inherits from Migration
  • Partial: allows splitting across files if needed

🎯 Key Functional Areas

  1. Altering a Column
    • Switch JobNumber from decimal(18,2) to nvarchar(max).
  2. Reversibility
    • Ensures the schema can be rolled back, restoring original data type and defaults.

πŸ”„ Migration Methods

Up Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "JobNumber",
        table: "JobAndFieldOfficeMapping",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(decimal),
        oldType: "decimal(18,2)"
    );
}
  • Purpose: Convert JobNumber to a nullable string column
  • Key Parameters:
    • name: Column name
    • table: Table name
    • type: New SQL type (nvarchar(max))
    • nullable: true (allows NULL)
    • oldClrType/oldType: Original CLR type and SQL type for EF to generate proper SQL

Down Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<decimal>(
        name: "JobNumber",
        table: "JobAndFieldOfficeMapping",
        type: "decimal(18,2)",
        nullable: false,
        defaultValue: 0m,
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true
    );
}
  • Purpose: Revert JobNumber back to non-nullable decimal with a default of 0.00
  • Ensures downward migrations maintain data integrity.

πŸ—„οΈ Data Model Impact

Before Migration:

Column Type Nullable Default
JobNumber decimal(18,2) No N/A

After Migration:

Column Type Nullable Default
JobNumber nvarchar(max) Yes NULL

Note: Existing numeric values will be implicitly converted to string representations.


πŸ“Š UML Diagram

           +-----------------------------+
           |       Microsoft.Entity      |
           |       FrameworkCore         |
           |       .Migration            |
           +-------------+---------------+
                         ^
                         |
     +-------------------------------------------+
     | UpdateEmployeeTableWithNewColumns5       |
     | Β«partialΒ»                                |
     +-------------------------------------------+
     | + Up(migrationBuilder) : void            |
     | + Down(migrationBuilder) : void          |
     +-------------------------------------------+

πŸ“š Usage Examples

  1. Applying Migration
    dotnet ef database update 20240201193852_UpdateEmployeeTableWithNewColumns5
    
  2. Reverting Migration
    dotnet ef database update <PreviousMigrationName>
    
  3. Verifying Column
    EXEC sp_columns @table_name = 'JobAndFieldOfficeMapping';
    

βœ… Considerations & Best Practices

  • Data Conversion: Before deploying, ensure no data loss when converting decimal to string.
  • Nullable Change: Changing from non-nullable to nullable may require updating business logic.
  • Rollback Safety: The Down method sets a default of 0m to avoid NULL constraints, but confirm this aligns with domain rules.
  • Testing: Validate queries and application code that interact with JobNumber.

🏁 Conclusion

This migration enhances flexibility by allowing alphanumeric job identifiers. It follows EF Core’s best practices for schema evolution by providing both Up and Down methods, enabling safe deployments and rollbacks.


πŸ“’ Remember: After applying this migration, update any related data models or validation logic to treat JobNumber as a string rather than a numeric type.


Generated with care to guide developers through schema changes in the IrbyImaging application. πŸš€