π Migration Documentation
IrbyImaging.DbContext/Migrations/20240613154527_ChangePncIntFieldsToString.cs
ποΈ Table of Contents
- π Overview & Purpose
- π οΈ Dependencies & Technologies
- π Key Functional Areas
- π Migration Details
- π Data Models Affected
- π Usage & Examples
- π¦ CLI Commands
- π UML Class Diagram
- π 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
stringprovides 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
FullUp/Downsupport 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
- Add the Migration
dotnet ef migrations add ChangePncIntFieldsToString \ -c IrbyImagingDbContext \ --schema irby - Apply to Database
dotnet ef database update \ -c IrbyImagingDbContext - 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/Downmethods 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.