π IrbyImaging.DbContext Migrations Documentation
File: Migrations/20250211125043_AddPayrollMatchTable.cs
Namespace: IrbyImaging.DbContext.Migrations
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Class:
AddPayrollMatchTable- 3.1 Up Method
- 3.2 Down Method
- Data Model Changes
- Usage & Execution
- UML Diagram
- Sample SQL Script
- Testing & Rollback
- Conclusion
1. Overview & Purpose
π Purpose:
This Entity Framework Core migration introduces a new table, PayrollMatch, into the irby schema and extends the existing CostCode table by adding a CostCodeType column.
- The
PayrollMatchtable captures matching contributions between employees and their employers (e.g., 401(k) matching). - The
CostCodeTypecolumn extends cost code definitions with a typed classification (enum-backed).
π― Intended Use:
- Track employer vs. employee contribution matching.
- Provide richer cost code metadata for reporting and business logic.
2. Dependencies & Technologies
| Technology | Version/Details |
|---|---|
| .NET / C# | .NET 6+ / C# 10 (nullable disabled) |
| Entity Framework Core | EF Core Migrations (Microsoft.EntityFrameworkCore.Migrations) |
| SQL Server | uniqueidentifier, decimal(18,2), datetime2, int |
| Database Schema | irby |
3. Migration Class: AddPayrollMatchTable
public partial class AddPayrollMatchTable : Migration
Implements two core methods: Up() to apply changes and Down() to revert.
3.1 Up Method
π Purpose: Apply schema changes to the database.
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1. Add CostCodeType column to CostCode table
migrationBuilder.AddColumn<int>(
name: "CostCodeType",
schema: "irby",
table: "CostCode",
type: "int",
nullable: false,
defaultValue: 0);
// 2. Create PayrollMatch table
migrationBuilder.CreateTable(
name: "PayrollMatch",
schema: "irby",
columns: table => new
{
PayrollMatchId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
EmployeeId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
EmployerMatch = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
EmployeeMatch = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
AddedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
AddedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
ModifiedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
ModifiedDate = table.Column<DateTime>(type: "datetime2", nullable: false),
},
constraints: table =>
{
table.PrimaryKey("PK_PayrollMatch", x => x.PayrollMatchId);
table.ForeignKey(
name: "FK_PayrollMatch_Employee_EmployeeId",
column: x => x.EmployeeId,
principalSchema: "irby",
principalTable: "Employee",
principalColumn: "EmployeeId",
onDelete: ReferentialAction.Cascade);
});
// 3. Index for fast Employee lookups
migrationBuilder.CreateIndex(
name: "IX_PayrollMatch_EmployeeId",
schema: "irby",
table: "PayrollMatch",
column: "EmployeeId");
}
Highlights:
AddColumn<int>: Non-nullable with default0ensures backward compatibility.CreateTable: Declares primary key & foreign key toEmployee(EmployeeId).CreateIndex: Optimizes lookups byEmployeeId.
3.2 Down Method
β©οΈ Purpose: Roll back changes if needed.
protected override void Down(MigrationBuilder migrationBuilder)
{
// Drop PayrollMatch table
migrationBuilder.DropTable(
name: "PayrollMatch",
schema: "irby");
// Remove CostCodeType column
migrationBuilder.DropColumn(
name: "CostCodeType",
schema: "irby",
table: "CostCode");
}
- Ensures clean rollback: table removed then column removed.
4. Data Model Changes
| Table | Column | Type | Nullable | Constraints |
|---|---|---|---|---|
| CostCode | CostCodeType | int |
No | Default = 0 |
| PayrollMatch | PayrollMatchId | uniqueidentifier |
No | Primary Key |
| EmployeeId | uniqueidentifier |
No | FK β Employee(EmployeeId) | |
| EmployerMatch | decimal(18,2) |
No | ||
| EmployeeMatch | decimal(18,2) |
No | ||
| AddedBy | nvarchar(max) |
Yes | ||
| AddedDate | datetime2 |
No | ||
| ModifiedBy | nvarchar(max) |
Yes | ||
| ModifiedDate | datetime2 |
No |
5. Usage & Execution
- Add Migration
dotnet ef migrations add AddPayrollMatchTable \ --context IrbyImagingDbContext \ --output-dir Migrations - Apply to Database
dotnet ef database update --context IrbyImagingDbContext - Verify
- Check
CostCodetable for newCostCodeTypecolumn. - Confirm
PayrollMatchtable exists and has correct FKs/indices.
- Check
6. UML Diagram
ββββββββββββββββββββββββββ 1 βββββββββββββββββββββββ
β Employee βββββββββββ PayrollMatch β
ββββββββββββββββββββββββββ€ βββββββββββββββββββββββ€
β EmployeeId : Guid (PK) β β PayrollMatchId : Guid (PK) β
β Name : string β β EmployeeId : Guid (FK) β
β ... β β EmployerMatch : decimal β
ββββββββββββββββββββββββββ β EmployeeMatch : decimal β
β AddedBy : string β
β AddedDate : DateTime β
β ModifiedBy : string β
β ModifiedDate : DateTime β
ββββββββββββββββββββββββββββββββ
βββββββββββββββββββββββββββββββ
β CostCode β
βββββββββββββββββββββββββββββββ€
β CostCodeId : Guid (PK) β
β Code : string β
β Description : string β
β CostCodeType : int β βββ Newly Added
βββββββββββββββββββββββββββββββ
7. Sample SQL Script
Below is the equivalent TβSQL the migration would generate on SQL Server:
ALTER TABLE [irby].[CostCode]
ADD [CostCodeType] INT NOT NULL DEFAULT(0);
CREATE TABLE [irby].[PayrollMatch](
[PayrollMatchId] UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
[EmployeeId] UNIQUEIDENTIFIER NOT NULL,
[EmployerMatch] DECIMAL(18,2) NOT NULL,
[EmployeeMatch] DECIMAL(18,2) NOT NULL,
[AddedBy] NVARCHAR(MAX) NULL,
[AddedDate] DATETIME2 NOT NULL,
[ModifiedBy] NVARCHAR(MAX) NULL,
[ModifiedDate] DATETIME2 NOT NULL
);
ALTER TABLE [irby].[PayrollMatch]
ADD CONSTRAINT [FK_PayrollMatch_Employee_EmployeeId]
FOREIGN KEY ([EmployeeId])
REFERENCES [irby].[Employee]([EmployeeId])
ON DELETE CASCADE;
CREATE INDEX [IX_PayrollMatch_EmployeeId]
ON [irby].[PayrollMatch]([EmployeeId]);
8. Testing & Rollback
- Unit Tests:
- Validate that after migration, EF Core model snapshot includes
PayrollMatch&CostCodeType.
- Validate that after migration, EF Core model snapshot includes
- Manual Verification:
- Insert a test record and ensure FKs enforce cascade delete.
- Rollback:
This runsdotnet ef database update LastGoodMigrationDown()to remove the added table & column.
9. Conclusion
β
AddPayrollMatchTable brings critical payrollβmatching functionality to the irby database, enabling:
- Precise tracking of employer vs. employee contributions.
- Enhanced cost code classification with a typed enum field.
- Seamless integration using EF Core Migrations for versioned schema evolution.
βWith this migration in place, developers and database administrators can confidently deploy payroll match features while preserving backward compatibility.β π
End of Documentation