📗 AddColumnsToPayrollFringe Migration Documentation
A detailed EF Core migration that renames existing columns in the
irby.PayrollFringetable, adds dozens of new fringe‑benefit and pay‑tracking columns, and introduces a newDBACodeGrossPaytable for DBA‑code–level gross‑pay breakdown.
📑 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Structure
- Up Method 🔼
4.1 Column Renames 🔄
4.2 New Columns ➕
4.3 DBACodeGrossPay Table 📦 - Down Method 🔽
- Data Model Changes 💾
- Sample Usage & Commands ⚙️
- UML Diagram 🖼️
- 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:
- Drop
DBACodeGrossPaytable - Drop all newly added columns from
PayrollFringe - 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 yourDbContextand domain models to reflect new properties.
• Adjust services & repositories for new query patterns.
• Validate payroll reports to ensure data integrity.
Happy coding! 🚀