📖 Documentation: Migration “MoreUpdatesToJobTable3”
File:
IrbyImaging.DbContext/Migrations/20240130143555_MoreUpdatesToJobTable3.cs
Type: Entity Framework Core Migration
Created: January 30, 2024
Namespace:IrbyImaging.DbContext.Migrations
📑 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Model:
JobTable Changes - Migration Workflow & Usage Examples
- UML Diagram
- Best Practices & Notes
- Conclusion
🧐 Overview & Purpose
This migration MoreUpdatesToJobTable3 is designed to evolve the database schema for the Job table within the irby schema. It focuses on:
- Renaming an existing foreign-key column from
ProjectManagerIdtoProjectEstimatorId. - Adding four new string-based date fields to track planned vs. actual start/complete dates.
This reflects a business requirement to:
- Differentiate between estimators and managers.
- Capture planned and actual dates as free-form (string) fields, likely for flexibility or custom formatting.
🛠️ Dependencies & Technologies
| Dependency/Technology | Version | Purpose |
|---|---|---|
| Microsoft.EntityFrameworkCore.Migrations | 6.x / 7.x | Core migration support (Up/Down methods) |
| .NET SDK | 6.0 / 7.0 | Runtime & tooling for EF Core |
| SQL Server | 2019 / 2022 | Target relational database (NVARCHAR fields) |
Note: The code uses the
[schema: "irby"]attribute to target a custom schema. Ensure your database user has permission to modify that schema.
🔑 Key Functional Areas
3.1 Renaming a Column
migrationBuilder.RenameColumn(
name: "ProjectManagerId",
schema: "irby",
table: "Job",
newName: "ProjectEstimatorId"
);
- What it does:
Renames the existing columnProjectManagerId→ProjectEstimatorIdwithout losing data. - Why:
To reflect a change in domain language: estimators instead of managers in project roles.
3.2 Adding New Columns
Adds four new fields of type nvarchar(max) (C# → string?):
| Column Name | Type | Nullable | Description |
|---|---|---|---|
DatePlannedStart |
nvarchar(max) | Yes | Planned start date (free text) |
DatePlannedComplete |
nvarchar(max) | Yes | Planned completion date |
DateActualStart |
nvarchar(max) | Yes | Actual start date |
DateActualComplete |
nvarchar(max) | Yes | Actual completion date |
Code Snippet:
migrationBuilder.AddColumn<string>(
name: "DatePlannedStart",
schema: "irby",
table: "Job",
type: "nvarchar(max)",
nullable: true
);
// ... similarly for the other three columns
💡 Tip: Consider switching to
DateTime?in the entity and a matching SQLdatetime2column if you require strict date handling.
3.3 Reverting Changes (Down Method)
The Down method is the inverse:
- Drop all four newly added columns.
- Rename back
ProjectEstimatorId→ProjectManagerId.
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(name: "DateActualComplete", schema: "irby", table: "Job");
// ... drop other three columns
migrationBuilder.RenameColumn(
name: "ProjectEstimatorId",
schema: "irby",
table: "Job",
newName: "ProjectManagerId"
);
}
🗃️ Data Model: Job Table Changes
Below is a diff of the Job table schema before and after this migration.
| Column | Before | After |
|---|---|---|
| ProjectManagerId | nvarchar(450) (FK) |
Renamed to ProjectEstimatorId |
| DatePlannedStart | Not Present | nvarchar(max) |
| DatePlannedComplete | Not Present | nvarchar(max) |
| DateActualStart | Not Present | nvarchar(max) |
| DateActualComplete | Not Present | nvarchar(max) |
🚀 Migration Workflow & Usage Examples
-
Add Migration
dotnet ef migrations add MoreUpdatesToJobTable3 \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api -
Apply to Database
dotnet ef database update \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api -
Verify
SELECT TOP 5 * FROM irby.Job;
📊 UML Diagram
classDiagram
class Job {
+string Id
+string? ProjectEstimatorId <<Renamed>>
+string? DatePlannedStart
+string? DatePlannedComplete
+string? DateActualStart
+string? DateActualComplete
// ... other existing properties
}
🛠️ Note: This diagram highlights only the columns affected by this migration.
💡 Best Practices & Notes
- Type Safety:
Althoughnvarchar(max)andstringprovide maximum flexibility, consider using typedDateTime?properties to leverage database date functions and avoid formatting issues. - Schema Naming:
The migration uses the"irby"schema. Always ensure migrations respect your database schema separation. - Migration Idempotency:
EF Core ensures that each migration is applied once. Do not manually alter the__EFMigrationsHistorytable.
🏁 Conclusion
This migration is a schema evolution step for the Job table, aligning the data model with updated business terminology and tracking requirements. By renaming the ProjectManager column and adding planned/actual date columns, it supports enhanced reporting and scheduling capabilities within the application.
Happy coding! 😃✨