🗄️ AddFringeTable Migration Documentation (20241202161634_AddFringeTable.cs)


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Structure
  4. Up() Method Breakdown 🔼
  5. Down() Method Breakdown 🔽
  6. PayrollFringe Table Schema 📋
  7. Entity Relationship & UML Diagram 📊
  8. EF Core Migration Workflow ⚙️
  9. Usage Examples & Snippets 💻
  10. Conclusion 🎯

1. Overview & Purpose

What is this file?
20241202161634_AddFringeTable.cs is an Entity Framework Core migration class that:

  • 🗑️ Drops several obsolete columns from the irby.Ticket table.
  • 🆕 Creates a new irby.PayrollFringe table to store detailed fringe benefit calculations per employee.

Why was it created?
An organizational need arose to:

  • Simplify and refactor the existing Ticket entity by removing rarely used fields.
  • Introduce a dedicated PayrollFringe table to centralize fringe benefit information (health, dental, retirement, etc.) for payroll reporting and audit purposes.

2. Dependencies & Technologies

Technology Version / Role
.NET (C#) Language of implementation
Entity Framework Core Code-first ORM for migrations and models
Microsoft.EntityFrameworkCore.Migrations Provides MigrationBuilder APIs
SQL Server (assumed) Target relational database

3. Migration Structure

public partial class AddFringeTable : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) { /*…*/ }
    protected override void Down(MigrationBuilder migrationBuilder) { /*…*/ }
}
  • Up(): Applies schema changes (drop columns, create table).
  • Down(): Rolls back changes (drop new table, re-add dropped columns).

4. Up() Method Breakdown 🔼

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop obsolete columns from Ticket
    migrationBuilder.DropColumn("AccountType",       "irby", "Ticket");
    migrationBuilder.DropColumn("AccountTypeNotes",  "irby", "Ticket");
    migrationBuilder.DropColumn("AdminNotes",        "irby", "Ticket");
    migrationBuilder.DropColumn("ApproverAuthorization","irby","Ticket");
    migrationBuilder.DropColumn("ExecutorAcknowledgement","irby","Ticket");
    migrationBuilder.DropColumn("ExecutorDate",      "irby", "Ticket");
    migrationBuilder.DropColumn("RequestDetails",    "irby", "Ticket");
    migrationBuilder.DropColumn("ServiceAdminAccess","irby", "Ticket");

    // 2️⃣ Create new PayrollFringe table
    migrationBuilder.CreateTable(
        name: "PayrollFringe",
        schema: "irby",
        columns: table => new
        {
            Id                     = table.Column<Guid>(nullable: false),
            EmployeeId             = table.Column<Guid>(nullable: false),      // FK to Employee
            LaborClass             = table.Column<string>(nullable: true),
            HourlyRate             = table.Column<decimal>(18,2, nullable: false),
            FringeRequirement      = table.Column<decimal>(18,2, nullable: false),
            HoursWorked            = table.Column<decimal>(18,2, nullable: false),
            Health                 = table.Column<decimal>(18,2, nullable: false),
            Dental                 = table.Column<decimal>(18,2, nullable: false),
            Life                   = table.Column<decimal>(18,2, nullable: false),
            Disability             = table.Column<decimal>(18,2, nullable: false),
            Vision                 = table.Column<decimal>(18,2, nullable: false),
            TrainingFringe         = table.Column<decimal>(18,2, nullable: false),
            RetirementMatch        = table.Column<decimal>(18,2, nullable: false),
            Vacation               = table.Column<decimal>(18,2, nullable: false),
            Sick                   = table.Column<decimal>(18,2, nullable: false),
            Holiday                = table.Column<decimal>(18,2, nullable: false),
            EAP                    = table.Column<decimal>(18,2, nullable: false),
            WeeklyFringe           = table.Column<decimal>(18,2, nullable: false),
            YearlyFringe           = table.Column<decimal>(18,2, nullable: false),
            TotalFringe            = table.Column<decimal>(18,2, nullable: false),
            CashFringe             = table.Column<decimal>(18,2, nullable: false),
            FringeHours            = table.Column<decimal>(18,2, nullable: false),
            CashPayableFringe      = table.Column<decimal>(18,2, nullable: false),
            AddedBy                = table.Column<string>(nullable: true),
            AddedDate              = table.Column<DateTime>(nullable: false),
            ModifiedBy             = table.Column<string>(nullable: true),
            ModifiedDate           = table.Column<DateTime>(nullable: false),
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_PayrollFringe", x => x.Id);
            table.ForeignKey(
                name: "FK_PayrollFringe_Employee_EmployeeId",
                column: x => x.EmployeeId,
                principalSchema: "irby",
                principalTable: "Employee",
                principalColumn: "EmployeeId",
                onDelete: ReferentialAction.Cascade);
        });

    // 3️⃣ Create foreign-key index
    migrationBuilder.CreateIndex(
        name: "IX_PayrollFringe_EmployeeId",
        schema: "irby",
        table: "PayrollFringe",
        column: "EmployeeId");
}

Key Steps

  • DropColumn: Removes legacy fields from Ticket.
  • CreateTable: Defines PayrollFringe with 25+ columns, capturing every detail of fringe benefits.
  • ForeignKey & Index: Links PayrollFringe.EmployeeId → Employee.EmployeeId for referential integrity.

5. Down() Method Breakdown 🔽

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop the PayrollFringe table
    migrationBuilder.DropTable("PayrollFringe", schema: "irby");

    // 2️⃣ Revert Ticket table schema to its previous state
    migrationBuilder.AddColumn<string>( "AccountType",          "irby", "Ticket", type:"nvarchar(max)", nullable:true);
    migrationBuilder.AddColumn<string>( "AccountTypeNotes",     "irby", "Ticket", type:"nvarchar(max)", nullable:true);
    migrationBuilder.AddColumn<string>( "AdminNotes",           "irby", "Ticket", type:"nvarchar(max)", nullable:true);
    migrationBuilder.AddColumn<string>( "ApproverAuthorization","irby", "Ticket", type:"nvarchar(max)", nullable:true);
    migrationBuilder.AddColumn<bool>(   "ExecutorAcknowledgement","irby","Ticket", type:"bit", defaultValue:false);
    migrationBuilder.AddColumn<DateTime>("ExecutorDate",         "irby","Ticket", type:"datetime2", nullable:true);
    migrationBuilder.AddColumn<string>( "RequestDetails",        "irby","Ticket", type:"nvarchar(max)", nullable:true);
    migrationBuilder.AddColumn<string>( "ServiceAdminAccess",    "irby","Ticket", type:"nvarchar(max)", nullable:true);
}

Why Down() Matters

  • Ensures safe rollback if the migration needs to be reverted.
  • Restores both the dropped columns and drops the newly created table.

6. PayrollFringe Table Schema 📋

Column Name Type Nullable Description
Id uniqueidentifier No PK. Surrogate ID.
EmployeeId uniqueidentifier No FK to Employee(EmployeeId)
LaborClass nvarchar(max) Yes Classification of labor
HourlyRate decimal(18,2) No Employee’s hourly pay rate
FringeRequirement decimal(18,2) No Required fringe %, as decimal
HoursWorked decimal(18,2) No Total hours worked
Health, Dental, … decimal(18,2) each No Benefit-specific costs
WeeklyFringe decimal(18,2) No Fringe cost per week
YearlyFringe decimal(18,2) No Fringe cost per year
TotalFringe decimal(18,2) No Sum of all fringes
CashFringe decimal(18,2) No Portion payable in cash
FringeHours decimal(18,2) No Hours eligible for fringe
CashPayableFringe decimal(18,2) No Cash portion actually payable
AddedBy, ModifiedBy nvarchar(max) Yes Audit: who created/modified the row
AddedDate, ModifiedDate datetime2 No Audit: when row was created/modified

7. Entity Relationship & UML Diagram 📊

+-----------------------+         +----------------------+
|      Employee         | 1     * |    PayrollFringe     |
+-----------------------+---------+----------------------+
| EmployeeId : Guid     |<--------| EmployeeId : Guid    |
| FirstName  : string   |         | Id         : Guid    |
| LastName   : string   |         | LaborClass : string  |
| …                     |         | …                    |
+-----------------------+         +----------------------+
  • One-to-Many: An Employee can have multiple PayrollFringe records (e.g., weekly snapshots).

8. EF Core Migration Workflow ⚙️

  1. Generate migration

    dotnet ef migrations add AddFringeTable --project IrbyImaging.DbContext
    
  2. Review the generated code (Up() / Down()).

  3. Apply migration to database

    dotnet ef database update --project IrbyImaging.DbContext
    
  4. Verify schema changes via SQL Server Management Studio or a query:

    SELECT * FROM irby.PayrollFringe;
    

9. Usage Examples & Snippets 💻

Querying Fringe Records

using(var context = new IrbyDbContext())
{
    var fringes = context.PayrollFringe
                         .Where(f => f.EmployeeId == someEmployeeId)
                         .OrderByDescending(f => f.AddedDate)
                         .ToList();
    fringes.ForEach(f => Console.WriteLine($"Week Fringe: {f.WeeklyFringe:C}"));
}

Inserting a New Fringe Snapshot

var fringe = new PayrollFringe
{
    Id = Guid.NewGuid(),
    EmployeeId = employee.Id,
    LaborClass = "Engineering",
    HourlyRate = 50.00m,
    HoursWorked = 40,
    Health = 100m,
    Dental = 20m,
    // … other fields …
    AddedBy = "system",
    AddedDate = DateTime.UtcNow,
    ModifiedBy = "system",
    ModifiedDate = DateTime.UtcNow
};

context.PayrollFringe.Add(fringe);
context.SaveChanges();

10. Conclusion 🎯

The AddFringeTable migration centralizes the management of employee fringe benefits by introducing a robust, audit-friendly PayrollFringe table and cleans up legacy fields from the Ticket entity.

  • Modular: Isolates fringe data in its own table.
  • Auditable: Tracks creation/modification metadata.
  • Scalable: Supports detailed per-week or per-year reporting.

Apply this migration to streamline payroll benefit calculations and simplify your domain model!