π AddColumnsToJobTable Migration Documentation
Table of Contents
- Overview & Purpose
- Key Functional Areas
- Dependencies & Technologies
- Migration Class: AddColumnsToJobTable
- 4.1 Up Method π
- 4.2 Down Method π½
- Schema Changes Summary π
- Applying the Migration π
- UML Diagram: Updated Job Entity π
- Common Scenarios & Usage Examples π οΈ
- Conclusion π―
1. Overview & Purpose
This migration AddColumnsToJobTable extends the existing Job table in the irby schema by adding three nullable GUID columns:
- ManagerId
- ProjectManagerId
- SuperintendentId
These new fields link each job record to different user roles, enabling richer relationship mapping (e.g., one job can now track which manager, project manager, or superintendent is responsible).
2. Key Functional Areas
-
Database Schema Evolution
Introduces new columns without dropping existing data. -
Maintainability
Uses EF CoreβsMigrationBuilderfor reversible changes. -
Role Tracking
Allows assignment of distinct user roles to a job.
3. Dependencies & Technologies
| Technology/Library | Version | Purpose |
|---|---|---|
| Microsoft.EntityFrameworkCore | β₯ 6.x | Core ORM & Migrations |
| Microsoft.EntityFrameworkCore.Tools | β₯ 6.x | CLI tooling for generating/applying Migrations |
| .NET (Core/5/6/7) | ~6.0 or later | Runtime hosting the DbContext |
No thirdβparty integrations are required for this migration.
4. Migration Class: AddColumnsToJobTable
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace IrbyImaging.DbContext.Migrations
{
/// <inheritdoc />
public partial class AddColumnsToJobTable : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.AddColumn<Guid>(
name: "ManagerId",
schema: "irby",
table: "Job",
type: "uniqueidentifier",
nullable: true);
migrationBuilder.AddColumn<Guid>(
name: "ProjectManagerId",
schema: "irby",
table: "Job",
type: "uniqueidentifier",
nullable: true);
migrationBuilder.AddColumn<Guid>(
name: "SuperintendentId",
schema: "irby",
table: "Job",
type: "uniqueidentifier",
nullable: true);
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "ManagerId",
schema: "irby",
table: "Job");
migrationBuilder.DropColumn(
name: "ProjectManagerId",
schema: "irby",
table: "Job");
migrationBuilder.DropColumn(
name: "SuperintendentId",
schema: "irby",
table: "Job");
}
}
}
4.1 Up Method
- Purpose: Adds three nullable
uniqueidentifiercolumns. - Key API:
migrationBuilder.AddColumn<T>(...)
- Parameters:
name: Column name in the databaseschema: Database schema ("irby")table: Target table ("Job")type: SQL type ("uniqueidentifier")nullable: AllowsNULLvalues
4.2 Down Method
- Purpose: Reverts the above changes by dropping the added columns.
- Key API:
migrationBuilder.DropColumn(...)
- Safe Rollback: Ensures database can be reverted to pre-migration state.
5. Schema Changes Summary
| Column Name | Data Type | Nullable | Schema | Table |
|---|---|---|---|---|
| ManagerId | uniqueidentifier | Yes | irby | Job |
| ProjectManagerId | uniqueidentifier | Yes | irby | Job |
| SuperintendentId | uniqueidentifier | Yes | irby | Job |
6. Applying the Migration π
- Add & Commit Migration
dotnet ef migrations add AddColumnsToJobTable - Apply to Database
dotnet ef database update - Verify
- Inspect the
Jobtable in your DB tool (e.g., SSMS, Azure Data Studio). - Ensure the new columns appear under
irby.Job.
- Inspect the
7. UML Diagram: Updated Job Entity π
classDiagram
class Job {
+Guid? ManagerId
+Guid? ProjectManagerId
+Guid? SuperintendentId
-- other existing properties --
}
Note: The above diagram shows only the newly added properties.
8. Common Scenarios & Usage Examples π οΈ
-
Assigning Roles in Application Code:
var job = await _context.Jobs.FindAsync(jobId); job.ManagerId = currentUser.Id; job.ProjectManagerId = projectManagerUser.Id; job.SuperintendentId = superintendentUser.Id; await _context.SaveChangesAsync(); -
Querying by Role:
// Find all jobs overseen by a specific manager var jobsByManager = _context.Jobs .Where(j => j.ManagerId == managerId) .ToList(); -
Rollback Scenario: If you must revert:
dotnet ef database update LastGoodMigrationName
9. Conclusion π―
This migration is a non-breaking schema update that enriches the Job entity with three new GUID-based associations. By following the outlined steps, you can safely apply or rollback the changes, ensuring your database evolves alongside application requirements.
Happy coding! π