πŸ“„ Documentation: AddFksToRoleAndDepartmentTables Migration


πŸ—‚οΈ Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
  3. Dependencies & Technologies
  4. View Models & Data Models
  5. Helper Methods / Utilities
  6. Error Handling & Return Types
  7. API Endpoints Overview
  8. Third-party Integrations
  9. Sample Code Blocks
  10. Usage Examples
  11. UML Diagram
  12. Conclusion

1. Overview & Purpose πŸš€

File Path

IrbyImaging.DbContext/Migrations/20230721142520_AddFksToRoleAndDepartmentTables.cs

This migration introduces a foreign key relationship between the Role and Department tables in the irby schema. It ensures that each Role can optionally be associated with a Department, enforcing referential integrity at the database level.

  • Up(): Adds a nullable DepartmentId column to Role, creates an index, and sets up the FK constraint.
  • Down(): Rolls back these changes (drops FK, index, column).

2. Key Functional Areas πŸ”‘

Operation Description
Add Column DepartmentId on Role
Create Index Index on Role.DepartmentId
Add Foreign Key Constraint Role.DepartmentId β†’ Department.DepartmentId
Rollback (Down) Remove FK, index, and column

Why this matters

  • Enforces one-to-many relationship: one Department ↔️ many Roles
  • Optimizes lookups on departmental roles via index

3. Dependencies & Technologies βš™οΈ

  • .NET / C#
  • Entity Framework Core
    • Microsoft.EntityFrameworkCore.Migrations
  • SQL Server (using uniqueidentifier type for GUIDs)

4. View Models & Data Models πŸ“Š

Although this file is purely a migration, it reflects changes to the underlying EF Core data models:

Class Property Type Notes
Department DepartmentId Guid Primary Key (PK)
Role DepartmentId Guid? New Foreign Key (FK) to Department

Tip: After running this migration, update your Role entity class:

public class Role {
    public Guid RoleId { get; set; }
    public string Name { get; set; }
    
    // New navigation property
    public Guid? DepartmentId { get; set; }
    public Department? Department { get; set; }
}

5. Helper Methods / Utilities πŸ› οΈ

The migration uses the following API on MigrationBuilder:

Method Purpose
AddColumn<T>() Adds a new column to an existing table
CreateIndex() Creates a database index
AddForeignKey() Establishes a new foreign key constraint
DropForeignKey(), DropIndex() Clean up in the Down migration
DropColumn() Removes the column on rollback

6. Error Handling & Return Types ⚠️

  • Migrations rely on EF Core’s automatic transaction management.
  • No explicit try/catch is needed: failures during Up() or Down() will roll back.

7. API Endpoints Overview 🚫

This file does not define any HTTP API endpoints.
It purely affects the database schema via EF Core migrations.


8. Third-party Integrations πŸ”Œ

  • Entity Framework Core
    • Core library for migrations, schema evolution, and change tracking.

9. Sample Code Blocks πŸ“

Migration Class (Full Source)

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    public partial class AddFksToRoleAndDepartmentTables : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<Guid>(
                name: "DepartmentId",
                schema: "irby",
                table: "Role",
                type: "uniqueidentifier",
                nullable: true);

            migrationBuilder.CreateIndex(
                name: "IX_Role_DepartmentId",
                schema: "irby",
                table: "Role",
                column: "DepartmentId");

            migrationBuilder.AddForeignKey(
                name: "FK_Role_Department_DepartmentId",
                schema: "irby",
                table: "Role",
                column: "DepartmentId",
                principalSchema: "irby",
                principalTable: "Department",
                principalColumn: "DepartmentId");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropForeignKey(
                name: "FK_Role_Department_DepartmentId",
                schema: "irby",
                table: "Role");

            migrationBuilder.DropIndex(
                name: "IX_Role_DepartmentId",
                schema: "irby",
                table: "Role");

            migrationBuilder.DropColumn(
                name: "DepartmentId",
                schema: "irby",
                table: "Role");
        }
    }
}

10. Usage Examples πŸƒ

  1. Add the Migration
    dotnet ef migrations add AddFksToRoleAndDepartmentTables
    
  2. Apply to Database
    dotnet ef database update
    
  3. Rolling Back (if needed)
    dotnet ef database update [PreviousMigrationName]
    

11. UML Diagram πŸ“ˆ

classDiagram
    schema "irby" {
      class Department {
        +Guid DepartmentId
        +string Name
      }
      class Role {
        +Guid RoleId
        +string Name
        +Guid? DepartmentId
      }
      Department <|-- Role : DepartmentId FK
    }

This simple diagram shows the one-to-many relationship: each Role may belong to one Department.


12. Conclusion 🎯

This migration is a critical modernization step for IrbyImaging’s data layer:

  • It links roles and departments, enabling richer business logic.
  • It enforces data integrity via foreign key constraints.
  • It optimizes queries with proper indexing.

Once applied, update your domain model and service layer to leverage the new Department navigation on the Role entity. Enjoy safer, more expressive data relationships!