📚 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Class: UpdateColumnTypeOnPayrollFringe
  4. Key Functional Areas
  5. Detailed Code Walkthrough
  6. UML Class Diagram
  7. Applying & Reverting the Migration
  8. Usage Example
  9. Conclusion

📝 Overview & Purpose

The UpdateColumnTypeOnPayrollFringe migration is part of the IrbyImaging.DbContext project. It modifies the schema of the irby.PayrollFringe table by dropping a set of daily payroll-related columns (hours, rates, gross pay) and the JobId column. This refactor may support one of the following scenarios:

  • 🌐 Normalization: Consolidate daily columns into a separate child table or JSON column.
  • 💡 Feature Simplification: Remove unused fields to streamline the data model.
  • 🔄 Refactored Business Logic: The daily breakdown is now computed on-the-fly rather than stored.

This migration is fully reversible: the Up method applies the drop operations, and the Down method re-adds the columns with their original types and defaults.


⚙️ Dependencies & Technologies

Technology Version/Details
.NET / C# C# 10+, .NET 6+ (EF Core 6+)
Entity Framework Core Migrations Microsoft.EntityFrameworkCore.Migrations
Database Provider (e.g., SQL Server, PostgreSQL)
Schema irby

🚀 Migration Class: UpdateColumnTypeOnPayrollFringe

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    public partial class UpdateColumnTypeOnPayrollFringe : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder) { /* ... */ }
        protected override void Down(MigrationBuilder migrationBuilder) { /* ... */ }
    }
}
  • Namespace: IrbyImaging.DbContext.Migrations
  • Class: UpdateColumnTypeOnPayrollFringe
  • Base: Migration (EF Core)

🔑 Key Functional Areas

  1. Schema Refactoring

    • Removes daily pay breakdown columns.
    • Removes JobId foreign key column.
  2. Reversibility

    • Up: Drops columns.
    • Down: Recreates columns with decimal(18,2) or int types and default values.
  3. Data Integrity

    • On Down, ensures non-nullable columns are re-added with defaults (0m for decimals, 0 for ints).

🔍 Detailed Code Walkthrough

5.1 Up Method (Schema Changes)

protected override void Up(MigrationBuilder migrationBuilder)
{
    // Drop all day-specific payroll fields and the JobId
    foreach (var col in new[]
    {
        "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
    })
    {
        migrationBuilder.DropColumn(name: $"{col}Hours",    schema: "irby", table: "PayrollFringe");
        migrationBuilder.DropColumn(name: $"{col}Rate",     schema: "irby", table: "PayrollFringe");
        migrationBuilder.DropColumn(name: $"{col}GrossPay", schema: "irby", table: "PayrollFringe");
    }

    migrationBuilder.DropColumn(name: "JobId", schema: "irby", table: "PayrollFringe");
}

What It Does

  • Iterates through each weekday, dropping Hours, Rate, and GrossPay.
  • Drops the integer JobId column.

5.2 Down Method (Rollback)

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.AddColumn<int>(
        name: "JobId",
        schema: "irby",
        table: "PayrollFringe",
        type: "int",
        nullable: false,
        defaultValue: 0);

    foreach (var col in new[]
    {
        "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday", "Sunday"
    })
    {
        migrationBuilder.AddColumn<decimal>(
            name: $"{col}Hours",
            schema: "irby",
            table: "PayrollFringe",
            type: "decimal(18,2)",
            nullable: false,
            defaultValue: 0m);

        migrationBuilder.AddColumn<decimal>(
            name: $"{col}Rate",
            schema: "irby",
            table: "PayrollFringe",
            type: "decimal(18,2)",
            nullable: false,
            defaultValue: 0m);

        migrationBuilder.AddColumn<decimal>(
            name: $"{col}GrossPay",
            schema: "irby",
            table: "PayrollFringe",
            type: "decimal(18,2)",
            nullable: false,
            defaultValue: 0m);
    }
}

Key Points

  • Ensures idempotency: running Down after Up restores the exact original schema.
  • Sets sensible defaults to avoid nullability conflicts.

📈 UML Class Diagram

classDiagram
    class Migration {
        <<abstract>>
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }

    class UpdateColumnTypeOnPayrollFringe {
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }

    Migration <|-- UpdateColumnTypeOnPayrollFringe

⚡ Applying & Reverting the Migration

  1. Add Migration (already done):
    dotnet ef migrations add UpdateColumnTypeOnPayrollFringe
    
  2. Apply to Database:
    dotnet ef database update
    
  3. Revert (Rollback):
    dotnet ef database update LastGoodMigrationName
    

💡 Usage Example

Imagine you have a service that computes weekly payroll breakdown on-the-fly instead of storing daily fields. After applying this migration:

var fringe = db.PayrollFringes.Find(fringeId);
// The older properties like MondayHours no longer exist
var summary = fringe.ComputeWeeklySummary(); 
Console.WriteLine($"Total Hours: {summary.TotalHours}");

🎯 Conclusion

The UpdateColumnTypeOnPayrollFringe migration simplifies the PayrollFringe table by removing per-day columns and JobId. This paves the way for:

  • Better normalization or dynamic computation
  • Cleaner domain models
  • Reduced table width and maintenance overhead

🙌 Next Steps:

  • Update your DbContext and entity classes to reflect the removed properties.
  • Implement dynamic logic (e.g., JSON arrays or child tables) to replace the dropped columns if needed.
  • Run your full test suite to ensure no breaking changes.