π οΈ UpdateEmployeeTableWithNewColumns Migration Documentation
π Table of Contents
- Overview & Purpose
- Migration Structure
- Up() Method
- Down() Method
- Dependencies & Technologies
- Usage & Execution
- Related Data Models & Code Snippets
- UML Diagram (Mermaid)
- Conclusion
π 1. Overview & Purpose
Filename
IrbyImaging.DbContext/Migrations/20240201163255_UpdateEmployeeTableWithNewColumns.cs
This EF Core Migration introduces three new columns to two employee-related tables:
- ODSEmployees (default schema)
- Employee (in the
irbyschema)
π― Purpose:
- Store a managerβs identity (
GUID) and name (string) for each employee. - Enhance reporting, hierarchy traversal, and join queries.
π 2. Migration Structure
public partial class UpdateEmployeeTableWithNewColumns : Migration
{
protected override void Up(MigrationBuilder migrationBuilder) { /* Add columns */ }
protected override void Down(MigrationBuilder migrationBuilder) { /* Remove columns */ }
}
Up: Applies schema changes (adds columns).Down: Rolls back changes (drops columns).
π 3. Up() Method
Applies the schema modifications.
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1. Add Manager ID (GUID) to ODSEmployees
migrationBuilder.AddColumn<Guid>(
name: "MANAGER_ID",
table: "ODSEmployees",
type: "uniqueidentifier",
nullable: false,
defaultValue: Guid.Empty);
// 2. Add Manager Name (string) to ODSEmployees
migrationBuilder.AddColumn<string>(
name: "MANAGER_NAME",
table: "ODSEmployees",
type: "nvarchar(max)",
nullable: true);
// 3. Add ManagerName (string) to irby.Employee
migrationBuilder.AddColumn<string>(
name: "ManagerName",
schema: "irby",
table: "Employee",
type: "nvarchar(max)",
nullable: true);
}
π Added Columns Summary
| Column Name | Table Name | Schema | Data Type | Nullable | Default Value |
|---|---|---|---|---|---|
| MANAGER_ID | ODSEmployees | dbo | uniqueidentifier |
No | 00000000-0000-0000-0000-000000000000 |
| MANAGER_NAME | ODSEmployees | dbo | nvarchar(max) |
Yes | null |
| ManagerName | Employee | irby |
nvarchar(max) |
Yes | null |
π 4. Down() Method
Reverts the changes introduced in Up().
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(name: "MANAGER_ID", table: "ODSEmployees");
migrationBuilder.DropColumn(name: "MANAGER_NAME", table: "ODSEmployees");
migrationBuilder.DropColumn(name: "ManagerName", schema: "irby", table: "Employee");
}
π§ 5. Dependencies & Technologies
- .NET 6.0+ / .NET Core
- Entity Framework Core (Microsoft.EntityFrameworkCore)
- C# 10 features
- SQL Server (via
uniqueidentifier/nvarchar)
π‘ 6. Usage & Execution
- Add Migration
dotnet ef migrations add UpdateEmployeeTableWithNewColumns \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api - Apply Migration
dotnet ef database update \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api - Verify Schema
- Inspect tables
ODSEmployeesandirby.Employeein SQL Server Management Studio (SSMS) or Azure Data Studio.
- Inspect tables
ποΈ 7. Related Data Models & Code Snippets
Updated Employee Model (C#)
namespace IrbyImaging.Domain.Models
{
public class Employee
{
public Guid Id { get; set; }
public string Name { get; set; } = default!;
// New Properties from Migration
public Guid ManagerId { get; set; }
public string? ManagerName { get; set; }
// Navigation (optional)
public Employee? Manager { get; set; }
public ICollection<Employee>? Subordinates { get; set; }
}
}
Sample LINQ Query
// Fetch all employees with their manager names
var result = await _context.Employees
.Select(e => new {
e.Id,
e.Name,
e.ManagerId,
e.ManagerName
})
.ToListAsync();
π 8. UML Diagram (Mermaid)
classDiagram
class Employee {
+ Guid Id
+ string Name
+ Guid ManagerId
+ string? ManagerName
+ ICollection~Employee~? Subordinates
}
Employee --> "0..1" Employee : Manager
Employee --> "*" Employee : Subordinates
π 9. Conclusion
This migration enhances the employee hierarchy support by persisting both a managerβs identifier and display name directly on the employee record.
- Benefits: Faster lookups, simplified joins, improved data integrity.
- Revertible: The
Down()method cleanly rolls back schema changes.
Keep your migrations in source control and apply them in a controlled CI/CD pipeline to ensure schema consistency across environments. π
Generated with care by the IrbyImaging team.