πŸ“– Documentation: 20250116171432_AddJobColumnToClassificationTable.cs

Module Path:
IrbyImaging.DbContext/Migrations/20250116171432_AddJobColumnToClassificationTable.cs


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Schema Changes Summary
  3. Detailed Code Walkthrough
    3.1. Up Method
    3.2. Down Method
  4. Entity Relationship Diagram (UML)
  5. Dependencies & Technologies
  6. Applying the Migration
  7. Best Practices & Considerations
  8. Sample Usage & Code Examples
  9. 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 JobClassification and Job.
  • πŸš€ 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:

  1. DropForeignKey
  2. DropIndex
  3. 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.JobClassification now has a JobId column.
  • A foreign-key constraint ensures cascading deletes.

7. Best Practices & Considerations

  • πŸ”’ Data Backfill: If existing rows exist, update them with valid JobId values before making the column non-nullable.
  • πŸ”„ Zero-Guid Risks: The default 000…000 can 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 JobId when creating new classifications.
  • Adjust any existing seed data scripts.
  • Add unit tests or integration tests to cover the new relationship.

Happy coding! πŸš€