📄 Documentation: AddMoreColumnsToClassAndCostCodes Migration
📚 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Model Changes
- Migration Methods
- Index & Foreign Key
- UML Diagram
- Usage Examples
- Best Practices & Notes
- Conclusion
🔍 Overview & Purpose
This Entity Framework Core Migration adds additional columns to the JobClassification table and a foreign‑key relationship between CostCode and Job.
- Why?
- Enrich
JobClassificationrecords with geographical context (City,County,State). - Associate each
CostCodewith a parentJob, enforcing referential integrity (cascade delete).
- Enrich
🛠️ Dependencies & Technologies
| Technology | Version/Notes |
|---|---|
| .NET (Core/.NET 5+) | EF Core Migrations |
| Microsoft.EntityFrameworkCore | MigrationBuilder API |
| SQL Server (or other RDBMS) | nvarchar(max), uniqueidentifier |
Note: Migration is declared under the schema
irby.
🎯 Key Functional Areas
- Schema Evolution
- Add three nullable
nvarchar(max)columns toJobClassification.
- Add three nullable
- Data Integrity
- Add non‑nullable
JobIdforeign key toCostCode. - Enforce cascade delete when a parent
Jobis removed.
- Add non‑nullable
- Rollback Support
- Provide a matching
Downmethod to revert schema changes.
- Provide a matching
📦 Data Model Changes
| Table | Column | Type | Nullable | Default |
|---|---|---|---|---|
| JobClassification | City | nvarchar(max) |
Yes | N/A |
| County | nvarchar(max) |
Yes | N/A | |
| State | nvarchar(max) |
Yes | N/A | |
| CostCode | JobId | uniqueidentifier |
No | 00000000-0000-0000-0000-000000000000 |
🏗️ Migration Methods
Up Method
- Purpose: Apply schema changes.
- Key Steps:
- Add columns to JobClassification.
- Add
JobIdcolumn to CostCode with a default GUID. - Create index on CostCode.JobId for performance.
- Define foreign key constraint to Job (cascade delete).
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1️⃣ Add geographical fields
migrationBuilder.AddColumn<string>(
name: "City",
schema: "irby",
table: "JobClassification",
type: "nvarchar(max)",
nullable: true);
migrationBuilder.AddColumn<string>(
name: "County",
schema: "irby",
table: "JobClassification",
type: "nvarchar(max)",
nullable: true);
migrationBuilder.AddColumn<string>(
name: "State",
schema: "irby",
table: "JobClassification",
type: "nvarchar(max)",
nullable: true);
// 2️⃣ Add JobId FK field
migrationBuilder.AddColumn<Guid>(
name: "JobId",
schema: "irby",
table: "CostCode",
type: "uniqueidentifier",
nullable: false,
defaultValue: Guid.Empty);
// 3️⃣ Create an index for faster lookups
migrationBuilder.CreateIndex(
name: "IX_CostCode_JobId",
schema: "irby",
table: "CostCode",
column: "JobId");
// 4️⃣ Enforce referential integrity (Cascade on delete)
migrationBuilder.AddForeignKey(
name: "FK_CostCode_Job_JobId",
schema: "irby",
table: "CostCode",
column: "JobId",
principalSchema: "irby",
principalTable: "Job",
principalColumn: "JobId",
onDelete: ReferentialAction.Cascade);
}
Down Method
- Purpose: Revert changes if rollback is needed.
- Key Steps:
- Drop FK and index.
- Remove added columns.
protected override void Down(MigrationBuilder migrationBuilder)
{
// Remove foreign key & index
migrationBuilder.DropForeignKey(
name: "FK_CostCode_Job_JobId",
schema: "irby",
table: "CostCode");
migrationBuilder.DropIndex(
name: "IX_CostCode_JobId",
schema: "irby",
table: "CostCode");
// Remove the added columns
migrationBuilder.DropColumn(name: "City", schema: "irby", table: "JobClassification");
migrationBuilder.DropColumn(name: "County", schema: "irby", table: "JobClassification");
migrationBuilder.DropColumn(name: "State", schema: "irby", table: "JobClassification");
migrationBuilder.DropColumn(name: "JobId", schema: "irby", table: "CostCode");
}
🔗 Index & Foreign Key
| Constraint Name | Table | Column | Reference Table | On Delete |
|---|---|---|---|---|
IX_CostCode_JobId |
CostCode | JobId | — | — |
FK_CostCode_Job_JobId |
CostCode | JobId | Job(JobId) | Cascade |
Why create an index? Improves JOIN/filter performance on
CostCode.JobId.
📈 UML Diagram
classDiagram
direction LR
class Job {
+Guid JobId
// ... other properties ...
}
class CostCode {
+Guid CostCodeId
+Guid JobId
// ... other properties ...
}
class JobClassification {
+Guid JobClassificationId
+string City
+string County
+string State
// ... other properties ...
}
Job "1" <|-- "0..*" CostCode : has
🚀 Usage Examples
-
Add & Apply Migration
# Add a new migration dotnet ef migrations add AddMoreColumnsToClassAndCostCodes \ --context IrbyDbContext --schema irby # Apply to database dotnet ef database update --context IrbyDbContext -
Inspect Database
-- Verify new columns in SQL Server SELECT TOP 5 City, County, State FROM irby.JobClassification; -- Verify CostCode → Job relationship SELECT c.CostCodeId, c.JobId, j.JobId FROM irby.CostCode c JOIN irby.Job j ON c.JobId = j.JobId; -
Rollback Migration
dotnet ef database update LastGoodMigrationName
💡 Best Practices & Notes
- Nullability:
- Geographical fields are nullable—existing records won’t break.
- Default GUID:
Guid.Emptyensures existing CostCode rows receive a placeholder; consider back‑filling real values.
- Cascade Deletes:
- Use with caution: deleting a
Jobwill automatically remove linkedCostCoderows.
- Use with caution: deleting a
- Version Control:
- Always check migrations into source control to keep schema changes tracked.
- Testing:
- Write integration tests to validate referential constraints and data integrity after migration.
🎯 Conclusion
This migration enhances your data model by:
- Enriching job classifications with location data.
- Enforcing a strong parent‑child relationship between jobs and cost codes.
By following the examples and best practices above, you can safely evolve your database schema and maintain data integrity throughout application development. 🚀