📚 Documentation: UpdateEmployeeTableWithNewColumns4 Migration
Table of Contents
- Overview & Purpose
- Migration at a Glance
- Key Functional Areas
- Detailed Breakdown:
Up()&Down()Methods - Dependencies & Technologies
- Data Model Changes
- UML Class Diagram
- Usage & Execution
- Rollback & Reversion
- 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
stringallows 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 tostring.nullable: true: AllowsNULLvalues (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 noNULLs — a default of0.00is applied for existing rows.
5. Dependencies & Technologies
- .NET 6+ / .NET Core
- Entity Framework Core
Microsoft.EntityFrameworkCoreMicrosoft.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
- Add Migration
dotnet ef migrations add UpdateEmployeeTableWithNewColumns4 - Apply Migration
dotnet ef database update - 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
ProjectManageras astring(e.g., view models, DTOs).- Add validation logic if only specific patterns (GUIDs, user names) are allowed.
Happy Coding! 🎉