πŸ“š IrbyImaging.DbContext Migrations Documentation

File: Migrations/20250211125043_AddPayrollMatchTable.cs
Namespace: IrbyImaging.DbContext.Migrations


πŸ“– Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Class: AddPayrollMatchTable
  4. Data Model Changes
  5. Usage & Execution
  6. UML Diagram
  7. Sample SQL Script
  8. Testing & Rollback
  9. 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 PayrollMatch table captures matching contributions between employees and their employers (e.g., 401(k) matching).
  • The CostCodeType column 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 default 0 ensures backward compatibility.
  • CreateTable: Declares primary key & foreign key to Employee(EmployeeId).
  • CreateIndex: Optimizes lookups by EmployeeId.

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

  1. Add Migration
    dotnet ef migrations add AddPayrollMatchTable \
      --context IrbyImagingDbContext \
      --output-dir Migrations
    
  2. Apply to Database
    dotnet ef database update --context IrbyImagingDbContext
    
  3. Verify
    • Check CostCode table for new CostCodeType column.
    • Confirm PayrollMatch table exists and has correct FKs/indices.

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.
  • Manual Verification:
    • Insert a test record and ensure FKs enforce cascade delete.
  • Rollback:
    dotnet ef database update LastGoodMigration
    
    This runs Down() 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