πŸ“„ Migration Documentation

IrbyImaging.DbContext/Migrations/20240613154527_ChangePncIntFieldsToString.cs


πŸ—‚οΈ Table of Contents

  1. πŸš€ Overview & Purpose
  2. πŸ› οΈ Dependencies & Technologies
  3. πŸ”‘ Key Functional Areas
  4. πŸ”„ Migration Details
  5. πŸ“Š Data Models Affected
  6. 🌐 Usage & Examples
  7. πŸ“¦ CLI Commands
  8. πŸ“ˆ UML Class Diagram
  9. πŸ“œ Conclusion

πŸš€ Overview & Purpose

The ChangePncIntFieldsToString migration is part of the IrbyImaging.DbContext project.
It converts several numeric columns in the Pnc table (under the irby schema) to string (nvarchar(max)) columns.

Why?

  • Numeric types (int, decimal) restrict values to digits only.
  • Business requirements evolved: IDs, codes, or work‑order numbers may now contain leading zeros or alphanumeric characters.
  • Converting to string provides flexibility for future scenarios (e.g., β€œA0123”, β€œ00045-B”).

πŸ› οΈ Dependencies & Technologies

Technology Version / Package
.NET / .NET Core 6.0+
Entity Framework Core Microsoft.EntityFrameworkCore (EF Core)
EF Core Migrations Microsoft.EntityFrameworkCore.Migrations
Database Provider Microsoft.EntityFrameworkCore.SqlServer (T‑SQL)

πŸ”‘ Key Functional Areas

  • Schema Evolution
    Alter existing columns without dropping the table.
  • Data Integrity
    Safely convert data types with minimal downtime.
  • Reversibility
    Full Up / Down support to migrate forward or rollback.

πŸ”„ Migration Details

πŸ”Ό Up Method (Apply Changes)

Converts numeric columns to nvarchar(max) (nullable) in irby.Pnc:

Column Old Type New Type Nullable
UnitNumber int nvarchar(max) Yes
TransactionId int nvarchar(max) Yes
Subsidiary decimal(18,2) nvarchar(max) Yes
SubLedger int nvarchar(max) Yes
EmployeeJdeAB int nvarchar(max) Yes
DepartmentOrJobNumber int nvarchar(max) Yes
CustomerWorkOrderNumber int nvarchar(max) Yes
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<string>(
        name: "UnitNumber",
        schema: "irby",
        table: "Pnc",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(int),
        oldType: "int");

    // ... repeat for other fields ...
}

πŸ”½ Down Method (Rollback Changes)

Reverts columns back to their original numeric types, with default values to prevent nullability issues:

Column New Type Reverted Type Default Value
UnitNumber nvarchar(max) int 0
TransactionId nvarchar(max) int 0
Subsidiary nvarchar(max) decimal(18,2) 0.00
SubLedger nvarchar(max) int 0
EmployeeJdeAB nvarchar(max) int 0
DepartmentOrJobNumber nvarchar(max) int 0
CustomerWorkOrderNumber nvarchar(max) int 0
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AlterColumn<int>(
        name: "UnitNumber",
        schema: "irby",
        table: "Pnc",
        type: "int",
        nullable: false,
        defaultValue: 0,
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true);

    // ... repeat for other fields ...
}

πŸ“Š Data Models Affected

Update your EF Core Pnc entity to match the new schema:

namespace IrbyImaging.Models
{
    public class Pnc
    {
        public string? UnitNumber { get; set; }
        public string? TransactionId { get; set; }
        public string? Subsidiary { get; set; }
        public string? SubLedger { get; set; }
        public string? EmployeeJdeAB { get; set; }
        public string? DepartmentOrJobNumber { get; set; }
        public string? CustomerWorkOrderNumber { get; set; }
        // … other properties …
    }
}

🌐 Usage & Examples

  1. Add the Migration
    dotnet ef migrations add ChangePncIntFieldsToString \
      -c IrbyImagingDbContext \
      --schema irby
    
  2. Apply to Database
    dotnet ef database update \
      -c IrbyImagingDbContext
    
  3. Rollback (if needed)
    dotnet ef database update LastGoodMigrationName
    

πŸ“¦ CLI Commands

Command Description
dotnet ef migrations add <Name> Scaffold a new migration
dotnet ef migrations remove Remove the last migration
dotnet ef database update Apply pending migrations to the database
dotnet ef migrations script Generate SQL script for migrations

πŸ“ˆ UML Class Diagram

classDiagram
    class Migration {
        <<abstract>>
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }
    class ChangePncIntFieldsToString {
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }
    Migration <|-- ChangePncIntFieldsToString

Figure: ChangePncIntFieldsToString inherits from EF Core’s abstract Migration class.


πŸ“œ Conclusion

This migration modernizes the Pnc table by switching key fields to string types, unlocking the ability to store alphanumeric identifiers and leading zeros.

  • Versatility: Supports new business scenarios.
  • Reversible: Clean Up/Down methods ensure safe rollbacks.
  • Seamless: Minimal impact on existing data; EF Core handles the type transformation gracefully.

πŸš€ Next Steps:

  • Update your application code to treat these fields as string.
  • Run end-to-end tests to confirm that no data truncation or conversion issues occur.
  • Deploy to staging and production following your standard EF Core deployment strategy.