📗 AddColumnsToPayrollFringe Migration Documentation

A detailed EF Core migration that renames existing columns in the irby.PayrollFringe table, adds dozens of new fringe‑benefit and pay‑tracking columns, and introduces a new DBACodeGrossPay table for DBA‑code–level gross‑pay breakdown.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Structure
  4. Up Method 🔼
    4.1 Column Renames 🔄
    4.2 New Columns ➕
    4.3 DBACodeGrossPay Table 📦
  5. Down Method 🔽
  6. Data Model Changes 💾
  7. Sample Usage & Commands ⚙️
  8. UML Diagram 🖼️
  9. Conclusion 🏁

1. Overview & Purpose 🔍

This EF Core migration—AddColumnsToPayrollFringe—modernizes the irby.PayrollFringe table by:

  • Renaming legacy columns into a clear “day‑of‑week” and pay‑period naming scheme.
  • Adding 40+ new columns for granular fringe‑benefit tracking (annual values, pay‑period values, credits, hours, etc.).
  • Creating a new table, DBACodeGrossPay, to associate gross‑pay amounts with specific DBA codes and payroll fringe entries.

These changes support detailed payroll reporting, benefit reconciliation, and audit requirements in the IrbyImaging domain.


2. Dependencies & Technologies 📚

  • .NET 6 or .NET Core 3.x
  • Entity Framework Core
    • Microsoft.EntityFrameworkCore
    • Microsoft.EntityFrameworkCore.SqlServer
  • SQL Server (supports decimal(18,2), datetime2, uniqueidentifier)

3. Migration Structure 🏗️

namespace IrbyImaging.DbContext.Migrations
{
    public partial class AddColumnsToPayrollFringe : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder) { /* Apply changes */ }
        protected override void Down(MigrationBuilder migrationBuilder) { /* Revert changes */ }
    }
}
  • Up: Implements schema changes.
  • Down: Reverts them.

4. Up Method 🔼

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1. Rename Columns
    // 2. Add New Columns
    // 3. Create DBACodeGrossPay Table
    // 4. Create Index on PayrollFringeId
}

4.1 Column Renames 🔄

🔁 Old Name 🔡 New Name 👉 Description
YearlyFringe _401kCredit 401(k) credit per pay period
WeeklyFringe WednesdayRate Rate for Wednesday
Vision WednesdayHours Hours worked Wednesday
Vacation WednesdayGrossPay Gross pay Wednesday
TrainingFringe TuesdayRate Rate for Tuesday
TotalFringe TuesdayHours Hours worked Tuesday
Sick TuesdayGrossPay Gross pay Tuesday
RetirmentMatch TotalGrossPay Total gross pay
Life ThursdayRate Rate for Thursday
LaborClass LaborClassification Labor classification code
HoursWorked ThursdayHours Hours worked Thursday
HourlyRate ThursdayGrossPay Gross pay Thursday
Holiday SundayRate Rate for Sunday
Health SundayHours Hours worked Sunday
FringeHours SundayGrossPay Gross pay Sunday
EAP SaturdayRate Rate for Saturday
Disability SaturdayHours Hours worked Saturday
Dental SaturdayGrossPay Gross pay Saturday
CashPayableFringe PayRateHr Hourly pay rate
CashFringe PayPeriod401KValue 401(k) value per pay period

4.2 New Columns ➕

The migration adds 40+ new columns to capture benefits, credits, pay, and hours.
Below is an illustrative subset—see code for full list:

Column Type Default Description
ActualHrsWorkedForWeek decimal(18,2) 0.00 Weekly actual hours
Annual401KValue decimal(18,2) 0.00 Annualized 401(k) value
AnnualizedFringe decimal(18,2) 0.00 Annual fringe total
ApprenticeshipCredit decimal(18,2) 0.00 Apprenticeship credit
ApprenticeshipValue decimal(18,2) 0.00 Apprenticeship benefit value
CashDue decimal(18,2) 0.00 Cash due for fringe
DentalAnnualValue decimal(18,2) 0.00 Annual dental fringe
DentalCredit decimal(18,2) 0.00 Dental fringe credit
DentalPayPeriodValue decimal(18,2) 0.00 Dental value per pay period
… … … …
EmployeeName nvarchar(max) NULL Employee’s full name
JobId int 0 Linked job/project ID
SelectedDate datetime2 1/1/0001 Payroll date
migrationBuilder.AddColumn<decimal>(
    name: "ActualHrsWorkedForWeek",
    schema: "irby",
    table: "PayrollFringe",
    type: "decimal(18,2)",
    nullable: false,
    defaultValue: 0m);

// … (repeated for each new column) …

4.3 DBACodeGrossPay Table 📦

A new table to track gross‑pay by DBA code and day:

migrationBuilder.CreateTable(
    name: "DBACodeGrossPay",
    columns: table => new
    {
        Id                   = table.Column<int>(type: "int").Annotation("SqlServer:Identity","1, 1"),
        FringeBenefitEntryId = table.Column<int>(type: "int",    nullable: false),
        Day                  = table.Column<string>(type: "nvarchar(max)", nullable: true),
        DBACode              = table.Column<string>(type: "nvarchar(max)", nullable: true),
        GrossPayAmount       = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
        PayrollFringeId      = table.Column<Guid>(type: "uniqueidentifier", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_DBACodeGrossPay", x => x.Id);
        table.ForeignKey(
            name: "FK_DBACodeGrossPay_PayrollFringe_PayrollFringeId",
            column: x => x.PayrollFringeId,
            principalSchema: "irby",
            principalTable: "PayrollFringe",
            principalColumn: "Id");
    });

migrationBuilder.CreateIndex(
    name: "IX_DBACodeGrossPay_PayrollFringeId",
    table: "DBACodeGrossPay",
    column: "PayrollFringeId");
  • PK on Id
  • FK to irby.PayrollFringe.Id
  • Index on PayrollFringeId

5. Down Method 🔽

Reverses every change in Up:

  1. Drop DBACodeGrossPay table
  2. Drop all newly added columns from PayrollFringe
  3. Rename columns back to their original names

This rollback ensures database consistency if the migration must be undone.


6. Data Model Changes 💾

Entity Classes

public class PayrollFringe
{
    public Guid Id { get; set; }
    public decimal PayRateHr { get; set; }
    public decimal TuesdayRate { get; set; }
    // … all new day‑of‑week, credit, annual, pay‑period properties …
    public string EmployeeName { get; set; }
    public DateTime SelectedDate { get; set; }

    public ICollection<DBACodeGrossPay> DBACodeGrossPays { get; set; }
}

public class DBACodeGrossPay
{
    public int Id { get; set; }
    public int FringeBenefitEntryId { get; set; }
    public string Day { get; set; }
    public string DBACode { get; set; }
    public decimal GrossPayAmount { get; set; }
    public Guid? PayrollFringeId { get; set; }
    public PayrollFringe PayrollFringe { get; set; }
}

Fluent API Configuration (optional)

modelBuilder.Entity<DBACodeGrossPay>()
    .HasOne(d => d.PayrollFringe)
    .WithMany(p => p.DBACodeGrossPays)
    .HasForeignKey(d => d.PayrollFringeId);

7. Sample Usage & Commands ⚙️

Adding & Applying the Migration

# In Package Manager Console
Add-Migration AddColumnsToPayrollFringe
Update-Database

Querying the Expanded Model

var report = context.PayrollFringes
    .Include(p => p.DBACodeGrossPays)
    .Where(p => p.SelectedDate.Month == 2)
    .Select(p => new {
        p.EmployeeName,
        WeekTotal = p.TotalGrossPay,
        DBAEntries = p.DBACodeGrossPays
    })
    .ToList();

8. UML Diagram 🖼️

@startuml
class PayrollFringe {
  +Guid Id
  +decimal PayRateHr
  +decimal WednesdayRate
  +decimal WednesdayHours
  +decimal WednesdayGrossPay
  +string EmployeeName
  +DateTime SelectedDate
  +ICollection<DBACodeGrossPay> DBACodeGrossPays
}

class DBACodeGrossPay {
  +int Id
  +int FringeBenefitEntryId
  +string Day
  +string DBACode
  +decimal GrossPayAmount
  +Guid? PayrollFringeId
}

PayrollFringe "1" *-- "0..*" DBACodeGrossPay
@enduml

Render with a PlantUML tool for visual reference.


9. Conclusion 🏁

This migration transforms irby.PayrollFringe into a rich, day‑by‑day, and benefit‑aware model, ready for advanced payroll analytics and reporting.
By executing Up, you unify your schema naming, expand benefit‑tracking fields, and establish a new DBACodeGrossPay table for precise gross‑pay breakdowns.
If needed, Down cleanly reverts all changes.

Next Steps:
• Update your DbContext and domain models to reflect new properties.
• Adjust services & repositories for new query patterns.
• Validate payroll reports to ensure data integrity.

Happy coding! 🚀