📝 UpdateCrewEmployeeTable Migration Documentation

Filename: IrbyImaging.DbContext/Migrations/20250306060435_UpdateCrewEmployeeTable.cs
Namespace: IrbyImaging.DbContext.Migrations
EF Core Version: 6.x or above


📚 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Migration Details
  5. Data Models Impact
  6. UML Diagram
  7. Sample Code Blocks
  8. Usage Examples
  9. Third‑party Integrations
  10. Conclusion

1. Overview & Purpose

This Entity Framework Core migration adds new columns and foreign‑key constraints to the irby.CrewEmployee table to:

  • Track Cost Codes and Job Classifications for each crew member
  • Introduce audit fields (CreatedBy, CreatedDate)
  • Support soft deletes via the IsDeleted flag

✨ Why?:

  • CostCodeId & JobClassificationId enforce referential integrity
  • CreatedBy & CreatedDate capture metadata for changes
  • IsDeleted enables marking records as inactive without physical deletion

2. Dependencies & Technologies

Technology / Library Purpose
Microsoft.EntityFrameworkCore Core ORM for .NET, including migration API
Microsoft.EntityFrameworkCore.Migrations Provides MigrationBuilder methods (AddColumn, AddForeignKey, etc.)
.NET 6+ (or later) Target framework for EF Core 6.x and above

No external third‑party packages beyond EF Core are required in this migration.


3. Key Functional Areas

  1. Schema Evolution
    • Adding/removing columns
    • Defining default values
  2. Referential Integrity
    • Creating indexes on FK columns
    • Establishing ForeignKey constraints with Restrict delete behavior
  3. Audit & Soft‑Delete
    • Adding CreatedBy/CreatedDate for change tracking
    • Introducing IsDeleted for logical deletion

4. Migration Details

4.1 Up Method (Applying Changes)

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Add new columns
    migrationBuilder.AddColumn<Guid>(
        name: "CostCodeId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty);

    migrationBuilder.AddColumn<string>(
        name: "CreatedBy",
        schema: "irby",
        table: "CrewEmployee",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<string>(
        name: "CreatedDate",
        schema: "irby",
        table: "CrewEmployee",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<bool>(
        name: "IsDeleted",
        schema: "irby",
        table: "CrewEmployee",
        type: "bit",
        nullable: false,
        defaultValue: false);

    migrationBuilder.AddColumn<Guid>(
        name: "JobClassificationId",
        schema: "irby",
        table: "CrewEmployee",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty);

    // 2️⃣ Create Indexes for FKs
    migrationBuilder.CreateIndex(
        name: "IX_CrewEmployee_CostCodeId",
        schema: "irby",
        table: "CrewEmployee",
        column: "CostCodeId");

    migrationBuilder.CreateIndex(
        name: "IX_CrewEmployee_JobClassificationId",
        schema: "irby",
        table: "CrewEmployee",
        column: "JobClassificationId");

    // 3️⃣ Add Foreign Key Constraints
    migrationBuilder.AddForeignKey(
        name: "FK_CrewEmployee_CostCode_CostCodeId",
        schema: "irby",
        table: "CrewEmployee",
        column: "CostCodeId",
        principalSchema: "irby",
        principalTable: "CostCode",
        principalColumn: "CostCodeId",
        onDelete: ReferentialAction.Restrict);

    migrationBuilder.AddForeignKey(
        name: "FK_CrewEmployee_JobClassification_JobClassificationId",
        schema: "irby",
        table: "CrewEmployee",
        column: "JobClassificationId",
        principalSchema: "irby",
        principalTable: "JobClassification",
        principalColumn: "JobClassificationId",
        onDelete: ReferentialAction.Restrict);
}

Highlights

  • Default GUID is Guid.Empty (00000000-0000-0000-0000-000000000000)
  • Soft‑delete default set to false
  • Restrict delete ensures no cascading deletes

4.2 Down Method (Reverting Changes)

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Remove Foreign Key Constraints
    migrationBuilder.DropForeignKey(
        name: "FK_CrewEmployee_CostCode_CostCodeId",
        schema: "irby",
        table: "CrewEmployee");

    migrationBuilder.DropForeignKey(
        name: "FK_CrewEmployee_JobClassification_JobClassificationId",
        schema: "irby",
        table: "CrewEmployee");

    // 2️⃣ Drop Indexes
    migrationBuilder.DropIndex(
        name: "IX_CrewEmployee_CostCodeId",
        schema: "irby",
        table: "CrewEmployee");

    migrationBuilder.DropIndex(
        name: "IX_CrewEmployee_JobClassificationId",
        schema: "irby",
        table: "CrewEmployee");

    // 3️⃣ Drop Added Columns
    migrationBuilder.DropColumn(name: "CostCodeId", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn(name: "CreatedBy", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn(name: "CreatedDate", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn(name: "IsDeleted", schema: "irby", table: "CrewEmployee");
    migrationBuilder.DropColumn(name: "JobClassificationId", schema: "irby", table: "CrewEmployee");
}

5. Data Models Impact

After applying this migration, the CrewEmployee Entity (in your DbContext) should include:

Property CLR Type Nullable Description
CostCodeId Guid No FK to CostCode.CostCodeId
JobClassificationId Guid No FK to JobClassification.JobClassificationId
CreatedBy string Yes Username/identifier who created the row
CreatedDate string Yes Timestamp (ISO string) for creation
IsDeleted bool No Soft-delete flag

Tip: Update your CrewEmployee POCO and OnModelCreating configuration accordingly.


6. UML Diagram

classDiagram
    class CrewEmployee {
        +Guid CostCodeId
        +Guid JobClassificationId
        +string CreatedBy
        +string CreatedDate
        +bool IsDeleted
    }
    class CostCode {
        +Guid CostCodeId
        +string Name
        +decimal Rate
    }
    class JobClassification {
        +Guid JobClassificationId
        +string Title
        +string Description
    }

    CrewEmployee --> CostCode : CostCodeId
    CrewEmployee --> JobClassification : JobClassificationId

7. Sample Code Blocks

Applying the Migration

# 1. Add migration (already done)
dotnet ef migrations add UpdateCrewEmployeeTable

# 2. Update database
dotnet ef database update

Updated Entity (Pseudocode)

public class CrewEmployee
{
    public Guid CrewEmployeeId { get; set; }

    // 👇 Newly added properties
    public Guid CostCodeId { get; set; }
    public CostCode CostCode { get; set; }

    public Guid JobClassificationId { get; set; }
    public JobClassification JobClassification { get; set; }

    public string CreatedBy { get; set; }
    public string CreatedDate { get; set; }
    public bool IsDeleted { get; set; }
}

8. Usage Examples

// 1. Creating a new employee record
var employee = new CrewEmployee
{
    CostCodeId = costCode.Id,
    JobClassificationId = jobClass.Id,
    CreatedBy = "[email protected]",
    CreatedDate = DateTime.UtcNow.ToString("o"),
    IsDeleted = false
};

dbContext.CrewEmployees.Add(employee);
await dbContext.SaveChangesAsync();
// 2. Soft-deleting an employee
var emp = await dbContext.CrewEmployees.FindAsync(employeeId);
if (emp != null)
{
    emp.IsDeleted = true;
    await dbContext.SaveChangesAsync();
}

9. Third‑party Integrations

  • Entity Framework Core Migrations
    Built‑in EF Core provider; no external integrations required for this migration.

10. Conclusion

🎉 This migration enhances the CrewEmployee table by introducing:

  • Referential integrity to CostCode & JobClassification
  • Audit fields for tracking creation metadata
  • Soft-delete capability

By running dotnet ef database update, your database schema will align with your application’s data model. Ensure you update the corresponding Poco classes and DbContext configurations to reflect these changes.


Document generated on 2025‑03‑06 for IrbyImaging.DbContext.Migrations. 🚀