📚 Documentation: 20230828165122_AddNewColumnsEmployeeAndAuditTable.cs

Location: IrbyImaging.DbContext/Migrations/20230828165122_AddNewColumnsEmployeeAndAuditTable.cs
Purpose: Introduce new columns to the Employee and AuditDetail tables and adjust foreign‑key behavior.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Changes
  5. UML Class Diagram
  6. Usage & Examples
  7. Conclusion

🔎 Overview & Purpose

This Entity Framework Core Migration class extends the existing schema by:

  • Adding an AzureUserId column to the irby.Employee table
  • Adding an EmployeeEmail column to the irby.AuditDetail table
  • Making the EmployeeId column on irby.AuditDetail non-nullable
  • Reconfiguring the foreign-key between AuditDetail.EmployeeId → Employee.EmployeeId to use Cascade delete

Why?

  • AzureUserId ties the internal Employee record to an Azure Active Directory identity
  • EmployeeEmail provides quick access to the actor’s email for audit logs
  • Enforcing non-nullable EmployeeId ensures every audit entry is associated with a valid employee
  • Cascade delete keeps referential integrity if an employee is removed

🛠 Dependencies & Technologies

Technology / Package Version / Note
.NET / C# .NET 6+ (C# 10+)
Entity Framework Core EF Core 6+
Microsoft.EntityFrameworkCore MigrationBuilder APIs
Database SQL Server (assumed, uniqueidentifier types)

🚀 Key Functional Areas

Up Migration

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop existing FK to allow column modifications
    migrationBuilder.DropForeignKey(
        name: "FK_AuditDetail_Employee_EmployeeId",
        schema: "irby",
        table: "AuditDetail");

    // 2️⃣ Add AzureUserId to Employee
    migrationBuilder.AddColumn<Guid>(
        name: "AzureUserId",
        schema: "irby",
        table: "Employee",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty);

    // 3️⃣ Make AuditDetail.EmployeeId non-nullable
    migrationBuilder.AlterColumn<Guid>(
        name: "EmployeeId",
        schema: "irby",
        table: "AuditDetail",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier",
        oldNullable: true);

    // 4️⃣ Add EmployeeEmail to AuditDetail
    migrationBuilder.AddColumn<string>(
        name: "EmployeeEmail",
        schema: "irby",
        table: "AuditDetail",
        type: "nvarchar(max)",
        nullable: true);

    // 5️⃣ Re-create FK with Cascade delete
    migrationBuilder.AddForeignKey(
        name: "FK_AuditDetail_Employee_EmployeeId",
        schema: "irby",
        table: "AuditDetail",
        column: "EmployeeId",
        principalSchema: "irby",
        principalTable: "Employee",
        principalColumn: "EmployeeId",
        onDelete: ReferentialAction.Cascade);
}

Down Migration

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop updated FK
    migrationBuilder.DropForeignKey(
        name: "FK_AuditDetail_Employee_EmployeeId",
        schema: "irby",
        table: "AuditDetail");

    // 2️⃣ Remove AzureUserId from Employee
    migrationBuilder.DropColumn(
        name: "AzureUserId",
        schema: "irby",
        table: "Employee");

    // 3️⃣ Remove EmployeeEmail from AuditDetail
    migrationBuilder.DropColumn(
        name: "EmployeeEmail",
        schema: "irby",
        table: "AuditDetail");

    // 4️⃣ Revert AuditDetail.EmployeeId back to nullable
    migrationBuilder.AlterColumn<Guid>(
        name: "EmployeeId",
        schema: "irby",
        table: "AuditDetail",
        type: "uniqueidentifier",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier");

    // 5️⃣ Restore original FK (no cascade)
    migrationBuilder.AddForeignKey(
        name: "FK_AuditDetail_Employee_EmployeeId",
        schema: "irby",
        table: "AuditDetail",
        column: "EmployeeId",
        principalSchema: "irby",
        principalTable: "Employee",
        principalColumn: "EmployeeId");
}

🧮 Data Model Changes

Employee Table

Column Type Nullable Default Description
EmployeeId uniqueidentifier ❌ N/A Primary key
… existing … … … … …
AzureUserId uniqueidentifier ❌ 00000000-0000-0000-0000-000000000000 Link to AAD object ID (new)

AuditDetail Table

Column Type Nullable Default Description
AuditDetailId uniqueidentifier ❌ N/A Primary key
EmployeeId uniqueidentifier ❌ 00000000-0000-0000-0000-000000000000 Foreign key to Employee (made non-nullable)
EmployeeEmail nvarchar(max) ✅ NULL Snapshot of employee’s email (new)
… other cols … … … … …

📊 UML Class Diagram

@startuml
package IrbyImaging.DbContext.Migrations {
  class AddNewColumnsEmployeeAndAuditTable {
    +Up(MigrationBuilder)
    +Down(MigrationBuilder)
  }
  class MigrationBuilder
  AddNewColumnsEmployeeAndAuditTable ..> MigrationBuilder : uses >
}
@enduml

UML Preview


🏃 Usage & Examples

1. Add & Apply Migration

# 1️⃣ Create migration (if not already present)
dotnet ef migrations add AddNewColumnsEmployeeAndAuditTable \
    --context IrbyDbContext \
    --output-dir Migrations

# 2️⃣ Apply to database
dotnet ef database update --context IrbyDbContext

2. Verify Schema Changes

-- AzureUserId on Employee:
SELECT TOP 1 AzureUserId FROM irby.Employee;

-- EmployeeEmail on AuditDetail:
SELECT TOP 1 EmployeeEmail FROM irby.AuditDetail;

📝 Conclusion

This migration strengthens referential integrity between AuditDetail and Employee, enriches audit logs with an EmployeeEmail, and integrates with Azure AD via AzureUserId.
Keep migrations in version control, review diffs carefully, and ensure your CI/CD pipeline runs dotnet ef database update to sync the live schema.

🚀 Happy Coding!