📚 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
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Model Changes
- UML Class Diagram
- Usage & Examples
- Conclusion
🔎 Overview & Purpose
This Entity Framework Core Migration class extends the existing schema by:
- Adding an AzureUserId column to the
irby.Employeetable - Adding an EmployeeEmail column to the
irby.AuditDetailtable - Making the
EmployeeIdcolumn onirby.AuditDetailnon-nullable - Reconfiguring the foreign-key between
AuditDetail.EmployeeId→Employee.EmployeeIdto use Cascade delete
Why?
- AzureUserId ties the internal
Employeerecord to an Azure Active Directory identity- EmployeeEmail provides quick access to the actor’s email for audit logs
- Enforcing non-nullable
EmployeeIdensures 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
🏃 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!