πŸ› οΈ UpdateEmployeeTableWithNewColumns Migration Documentation


πŸ“‹ Table of Contents

  1. Overview & Purpose
  2. Migration Structure
  3. Up() Method
  4. Down() Method
  5. Dependencies & Technologies
  6. Usage & Execution
  7. Related Data Models & Code Snippets
  8. UML Diagram (Mermaid)
  9. 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 irby schema)

🎯 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

  1. Add Migration
    dotnet ef migrations add UpdateEmployeeTableWithNewColumns \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  2. Apply Migration
    dotnet ef database update \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  3. Verify Schema
    • Inspect tables ODSEmployees and irby.Employee in SQL Server Management Studio (SSMS) or Azure Data Studio.

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.