📚 Documentation: Migration “20240130145814_MoreUpdatesToJobTable4.cs”
Location:
IrbyImaging.DbContext/Migrations/20240130145814_MoreUpdatesToJobTable4.cs
Type: Entity Framework Core Migration
Purpose: Change data types of key columns in ODSJobs and irby.Job tables
📑 Table of Contents
- 🚀 Overview & Purpose
- 🛠 Dependencies & Technologies
- 📁 Migration Class Structure
- 🔄 Up Method – Applying Changes
- 4.1 🔹 ODSJobs Table Updates
- 4.2 🔹 irby.Job Table Updates
- ↩ Down Method – Reverting Changes
- 📊 Data Model Impact (ERD/UML)
- 🏃♂️ Usage & Execution
- 💡 Considerations & Best Practices
- 📋 Error Handling & Return Types
- 🔗 Third‑party Integrations
- 🧩 Sample Code Blocks & Examples
- 🏁 Conclusion
1. 🚀 Overview & Purpose
This migration adjusts the column data types in two tables:
- ODSJobs
• Convert four numeric columns (decimal(18,0)) to GUIDs (uniqueidentifier). - irby.Job
• Convert four date‑related columns from unstructured text (nvarchar(max)) to strongly‑typedDateTime(datetime2).
Why?
- 🎯 Data Integrity: GUIDs improve cross‑system identifier consistency.
- 🗓 Type Safety: DateTime fields enable calendar operations, sorting & range queries.
2. 🛠 Dependencies & Technologies
| Category | Details |
|---|---|
| Framework | .NET 6+ / .NET Core |
| ORM | Entity Framework Core (EF Core) |
| Database | Microsoft SQL Server |
| C# Version | 10.0+ |
| Migration API | Microsoft.EntityFrameworkCore.Migrations |
3. 📁 Migration Class Structure
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace IrbyImaging.DbContext.Migrations
{
/// <inheritdoc />
public partial class MoreUpdatesToJobTable4 : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
// ALTER COLUMN calls...
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
// REVERT COLUMN calls...
}
}
}
partial class MoreUpdatesToJobTable4 : Migration
Defines Up and Down methods to apply/revert schema changes.#nullable disable
Ensures legacy nullability behavior.
4. 🔄 Up Method – Applying Changes
The Up method contains a series of AlterColumn calls to change existing columns.
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1️⃣ ODSJobs table: convert decimals → GUIDs
migrationBuilder.AlterColumn<Guid>(
name: "SUPERINTENDENT",
table: "ODSJobs",
type: "uniqueidentifier",
nullable: true,
oldClrType: typeof(decimal),
oldType: "decimal(18,0)",
oldNullable: true);
// ... PROJECT_ESTIMATOR, MANAGER, CUSTOMER_NUMBER ...
// 2️⃣ irby.Job table: convert strings → DateTime
migrationBuilder.AlterColumn<DateTime>(
name: "DatePlannedStart",
schema: "irby",
table: "Job",
type: "datetime2",
nullable: true,
oldClrType: typeof(string),
oldType: "nvarchar(max)",
oldNullable: true);
// ... DatePlannedComplete, DateActualStart, DateActualComplete ...
}
4.1 🔹 ODSJobs Table Updates
| Column | Old Type | New Type | Nullable |
|---|---|---|---|
SUPERINTENDENT |
decimal(18,0) |
uniqueidentifier |
Yes |
PROJECT_ESTIMATOR |
decimal(18,0) |
uniqueidentifier |
Yes |
MANAGER |
decimal(18,0) |
uniqueidentifier |
Yes |
CUSTOMER_NUMBER |
decimal(18,0) |
uniqueidentifier |
Yes |
Why GUIDs?
- Globally unique across distributed systems
- Non‑guessable, enhancing security for record identifiers
4.2 🔹 irby.Job Table Updates
| Column | Old Type | New Type | Nullable |
|---|---|---|---|
DatePlannedStart |
nvarchar(max) |
datetime2 |
Yes |
DatePlannedComplete |
nvarchar(max) |
datetime2 |
Yes |
DateActualStart |
nvarchar(max) |
datetime2 |
Yes |
DateActualComplete |
nvarchar(max) |
datetime2 |
Yes |
Benefits of DateTime
- Native date manipulations & validations
- Improved indexing & range queries
5. ↩ Down Method – Reverting Changes
The Down method rolls back the changes, reverting each column to its original type:
protected override void Down(MigrationBuilder migrationBuilder)
{
// 1️⃣ ODSJobs: GUIDs → decimal
migrationBuilder.AlterColumn<decimal>(
name: "SUPERINTENDENT",
table: "ODSJobs",
type: "decimal(18,0)",
nullable: true,
oldClrType: typeof(Guid),
oldType: "uniqueidentifier",
oldNullable: true);
// ... PROJECT_ESTIMATOR, MANAGER, CUSTOMER_NUMBER ...
// 2️⃣ irby.Job: DateTime → string
migrationBuilder.AlterColumn<string>(
name: "DatePlannedStart",
schema: "irby",
table: "Job",
type: "nvarchar(max)",
nullable: true,
oldClrType: typeof(DateTime),
oldType: "datetime2",
oldNullable: true);
// ... DatePlannedComplete, DateActualStart, DateActualComplete ...
}
6. 📊 Data Model Impact (ERD/UML)
Before Migration
erDiagram
ODSJobs {
decimal SUPERINTENDENT
decimal PROJECT_ESTIMATOR
decimal MANAGER
decimal CUSTOMER_NUMBER
}
irby_Job {
string DatePlannedStart
string DatePlannedComplete
string DateActualStart
string DateActualComplete
}
After Migration
erDiagram
ODSJobs {
uniqueidentifier SUPERINTENDENT
uniqueidentifier PROJECT_ESTIMATOR
uniqueidentifier MANAGER
uniqueidentifier CUSTOMER_NUMBER
}
irby_Job {
datetime2 DatePlannedStart
datetime2 DatePlannedComplete
datetime2 DateActualStart
datetime2 DateActualComplete
}
7. 🏃♂️ Usage & Execution
- Add Migration (already done):
dotnet ef migrations add MoreUpdatesToJobTable4 - Apply to Database:
dotnet ef database update - Verify by inspecting column types in SQL Server Management Studio or via a query:
EXEC sp_help 'ODSJobs'; EXEC sp_help 'irby.Job';
8. 💡 Considerations & Best Practices
- Data Compatibility:
- Converting existing decimal IDs to GUIDs requires either empty GUIDs or mapping logic.
- Converting free‑text dates may fail if format is invalid – consider a pre‑migration data cleanup.
- Backups: Always back up your database before running destructive migrations.
- Testing: Run migration in a staging environment to catch type‑conversion errors.
- Nullability: All columns remain nullable to avoid immediate data-loss.
9. 📋 Error Handling & Return Types
- EF Core Migrations throw exceptions on invalid SQL or type mismatches.
- No explicit return values (
voidmethods); success indicated by no exception.
10. 🔗 Third‑party Integrations
- None. This migration uses only EF Core’s built‑in migration APIs.
11. 🧩 Sample Code Blocks & Examples
Inspecting a Column in SQL
-- Check the new data type of SUPERINTENDENT
SELECT COLUMN_NAME, DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ODSJobs' AND COLUMN_NAME = 'SUPERINTENDENT';
12. 🏁 Conclusion
This migration brings strong typing and better data integrity to key parts of the Jobs domain:
- ✔️ Numeric → GUID for identifiers
- ✔️ String → DateTime for scheduling fields
By following best practices—backups, testing, and data cleanup—you can ensure a smooth rollout of these schema improvements. 🎉
Developed by the IrbyImaging team for robust, maintainable, and type‑safe database schemas.