π Documentation: UpdateEmployeeTableWithNewColumns5 Migration
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Class Structure
- Key Functional Areas
- Migration Methods
- Data Model Impact
- UML Diagram
- Usage Examples
- Considerations & Best Practices
- 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
- Altering a Column
- Switch JobNumber from
decimal(18,2)tonvarchar(max).
- Switch JobNumber from
- 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
JobNumberto a nullable string column - Key Parameters:
name: Column nametable: Table nametype: New SQL type (nvarchar(max))nullable:true(allowsNULL)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
JobNumberback to non-nullable decimal with a default of0.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
- Applying Migration
dotnet ef database update 20240201193852_UpdateEmployeeTableWithNewColumns5 - Reverting Migration
dotnet ef database update <PreviousMigrationName> - 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
Downmethod sets a default of0mto avoidNULLconstraints, 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
JobNumberas a string rather than a numeric type.
Generated with care to guide developers through schema changes in the IrbyImaging application. π