π Documentation: 20250116171432_AddJobColumnToClassificationTable.cs
Module Path:
IrbyImaging.DbContext/Migrations/20250116171432_AddJobColumnToClassificationTable.cs
π Table of Contents
- Overview & Purpose
- Schema Changes Summary
- Detailed Code Walkthrough
3.1. Up Method
3.2. Down Method - Entity Relationship Diagram (UML)
- Dependencies & Technologies
- Applying the Migration
- Best Practices & Considerations
- Sample Usage & Code Examples
- Conclusion
1. Overview & Purpose
This migration class AddJobColumnToClassificationTable extends the database schema by introducing a JobId column in the JobClassification table, linking each classification record back to its parent Job.
Why?
- π Referential Integrity: Ensures every classification is associated with a valid job.
- π Query Simplicity: Simplifies JOINs between
JobClassificationandJob. - π Future Features: Enables filtering or aggregating classifications by job.
2. Schema Changes Summary
| Change | Table | Details |
|---|---|---|
| Add Column | irby.JobClassification |
JobId of type uniqueidentifier, default 0000...000 |
| Create Index | irby.JobClassification |
Index on JobId |
| Add Foreign Key Constraint | irby.JobClassification β irby.Job |
On delete CASCADE ensures child rows are removed with parent |
3. Detailed Code Walkthrough
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace IrbyImaging.DbContext.Migrations
{
/// <inheritdoc />
public partial class AddJobColumnToClassificationTable : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1οΈβ£ Add the JobId column
migrationBuilder.AddColumn<Guid>(
name: "JobId",
schema: "irby",
table: "JobClassification",
type: "uniqueidentifier",
nullable: false,
defaultValue: new Guid("00000000-0000-0000-0000-000000000000"));
// 2οΈβ£ Create index to speed up lookups by JobId
migrationBuilder.CreateIndex(
name: "IX_JobClassification_JobId",
schema: "irby",
table: "JobClassification",
column: "JobId");
// 3οΈβ£ Add foreign key referencing irby.Job(JobId)
migrationBuilder.AddForeignKey(
name: "FK_JobClassification_Job_JobId",
schema: "irby",
table: "JobClassification",
column: "JobId",
principalSchema: "irby",
principalTable: "Job",
principalColumn: "JobId",
onDelete: ReferentialAction.Cascade);
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
// Reverse the above changes in exact opposite order
migrationBuilder.DropForeignKey(
name: "FK_JobClassification_Job_JobId",
schema: "irby",
table: "JobClassification");
migrationBuilder.DropIndex(
name: "IX_JobClassification_JobId",
schema: "irby",
table: "JobClassification");
migrationBuilder.DropColumn(
name: "JobId",
schema: "irby",
table: "JobClassification");
}
}
}
3.1 Up Method
β AddColumn
Adds a non-nullable JobId (Guid) with a default of all-zero Guid.
β CreateIndex
Improves JOIN and WHERE performance on JobId.
β AddForeignKey
Links to primary key of irby.Job table, enforcing CASCADE delete.
3.2 Down Method
Reverts all changes in reverse:
- DropForeignKey
- DropIndex
- DropColumn
4. Entity Relationship Diagram (UML)
erDiagram
irby.Job {
Guid JobId PK
string Name
DateTime CreatedOn
}
irby.JobClassification {
Guid JobClassificationId PK
string ClassificationName
Guid JobId FK
}
irby.Job ||--o{ irby.JobClassification : has
π Note: You may extend this diagram as you add more fields to each entity.
5. Dependencies & Technologies
| Technology | Purpose |
|---|---|
| π Entity Framework Core | Code-first migrations & DB schema management |
| .NET 6+ | Target framework |
| SQL Server (uniqueidentifier) | Storage of Guids in database |
6. Applying the Migration
# 1. Add the migration (if not already present)
dotnet ef migrations add AddJobColumnToClassificationTable \
--context YourDbContext \
--output-dir IrbyImaging.DbContext/Migrations
# 2. Apply to the database
dotnet ef database update \
--context YourDbContext
Result:
- Table
irby.JobClassificationnow has aJobIdcolumn.- A foreign-key constraint ensures cascading deletes.
7. Best Practices & Considerations
- π Data Backfill: If existing rows exist, update them with valid
JobIdvalues before making the column non-nullable. - π Zero-Guid Risks: The default
000β¦000can hide missing associations; consider a valid default or make it nullable initially. - π Transactional Safety: EF Core migrations are transactional on supported databasesβrollbacks occur on failures.
- π Migration Naming: Use descriptive timestamps and names for clarity.
8. Sample Usage & Code Examples
8.1 C# Data Model Snippet
public class Job
{
public Guid JobId { get; set; }
public string Name { get; set; }
public ICollection<JobClassification> Classifications { get; set; }
}
public class JobClassification
{
public Guid JobClassificationId { get; set; }
public string ClassificationName { get; set; }
// β NEW foreign key property
public Guid JobId { get; set; }
public Job Job { get; set; } // navigation
}
8.2 LINQ Query Example
// Get all classifications for a specific job
var classifications = dbContext.JobClassifications
.Where(jc => jc.JobId == someJobId)
.Select(jc => new {
jc.JobClassificationId,
jc.ClassificationName
})
.ToList();
9. Conclusion
This migration π§ solidifies the relationship between jobs and their classifications, improving data integrity and query performance. By following the steps above, you can seamlessly integrate this schema change into your EF Coreβpowered application.
π Next Steps:
- Update your application logic to populate
JobIdwhen creating new classifications.- Adjust any existing seed data scripts.
- Add unit tests or integration tests to cover the new relationship.
Happy coding! π