🗄️ AddFringeTable Migration Documentation (20241202161634_AddFringeTable.cs)
📑 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Structure
- Up() Method Breakdown 🔼
- Down() Method Breakdown 🔽
- PayrollFringe Table Schema 📋
- Entity Relationship & UML Diagram 📊
- EF Core Migration Workflow ⚙️
- Usage Examples & Snippets 💻
- 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.Tickettable. - 🆕 Creates a new
irby.PayrollFringetable 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
PayrollFringewith 25+ columns, capturing every detail of fringe benefits. - ForeignKey & Index: Links
PayrollFringe.EmployeeId→Employee.EmployeeIdfor 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
Employeecan have multiplePayrollFringerecords (e.g., weekly snapshots).
8. EF Core Migration Workflow ⚙️
-
Generate migration
dotnet ef migrations add AddFringeTable --project IrbyImaging.DbContext -
Review the generated code (
Up()/Down()). -
Apply migration to database
dotnet ef database update --project IrbyImaging.DbContext -
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!