📄 Documentation: Add New Tables for Equipment Reference Migration

Filename: IrbyImaging.DbContext/Migrations/20240307190246_AddNewTablesForEquipmentReference.cs


📚 Table of Contents

  1. Introduction
  2. Overview & Purpose
  3. Dependencies & Technologies
  4. Schema Changes
    4.1 Adding Columns to Equipment
    4.2 Creating EquipmentClass Table
    4.3 Creating EquipmentType Table
  5. Data Models
    5.1 Equipment
    5.2 EquipmentClass
    5.3 EquipmentType
  6. Migration Methods
    6.1 Up
    6.2 Down
  7. Entity Relationship UML Diagram
  8. Usage Examples
  9. Applying the Migration
  10. Conclusion

1. Introduction 📘

This document dives deep into the Entity Framework Core migration AddNewTablesForEquipmentReference. The migration augments your database schema by:

  • Extending the existing irby.Equipment table with new classification columns.
  • Introducing two reference tables: EquipmentClass and EquipmentType.
  • Establishing a parent–child relationship between classes and types.

2. Overview & Purpose 🎯

As applications evolve, we often need to maintain reference data (lookup tables) to categorize or type existing entities. This migration:

  • Classifies each Equipment record by category (Class) and sub‑category (Type).
  • Provides normalized lookup tables to store human‑readable names and identifiers for classes/types.
  • Supports referential integrity via foreign keys and indexes.

Key benefits:

  • Centralized management of equipment metadata.
  • Easier filtering, reporting, and dynamic dropdown population in UIs.
  • Clean separation between transactional data (Equipment) and reference data (EquipmentClass/EquipmentType).

3. Dependencies & Technologies 🔧

Technology Version / Notes
.NET Framework / .NET Core 6.0+
Entity Framework Core 6.x
Microsoft.EntityFrameworkCore MigrationBuilder API
SQL Server (or any EF Core provider) Uses uniqueidentifier, int, nvarchar(max)

Note: Update your *.csproj to include:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.*" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.*" />

4. Schema Changes 🚀

4.1 Adding Columns to Equipment

Column Type Nullable Default Description
Class int No 0 FK to EquipmentClass.ClassId (optional).
Type int No 0 FK to EquipmentType.TypeId (optional).
migrationBuilder.AddColumn<int>(
    name: "Class",
    schema: "irby",
    table: "Equipment",
    type: "int",
    nullable: false,
    defaultValue: 0);

migrationBuilder.AddColumn<int>(
    name: "Type",
    schema: "irby",
    table: "Equipment",
    type: "int",
    nullable: false,
    defaultValue: 0);

4.2 Creating EquipmentClass Table

Column Type Nullable Description
EquipmentClassId uniqueidentifier No Primary Key (GUID)
ClassId int No Business identifier for the equipment class
Name nvarchar(max) Yes Display name (e.g., “Imager”, “Laser”)
migrationBuilder.CreateTable(
    name: "EquipmentClass",
    columns: table => new
    {
        EquipmentClassId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
        ClassId          = table.Column<int>(type: "int", nullable: false),
        Name             = table.Column<string>(type: "nvarchar(max)", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_EquipmentClass", x => x.EquipmentClassId);
    });

4.3 Creating EquipmentType Table

Column Type Nullable Description
EquipmentTypeId uniqueidentifier No Primary Key (GUID)
TypeId int No Business identifier for the equipment type
Name nvarchar(max) Yes Display name (e.g., “MRI Scanner”, “Ultrasound”)
EquipmentClassId uniqueidentifier Yes FK to EquipmentClass.EquipmentClassId
migrationBuilder.CreateTable(
    name: "EquipmentType",
    columns: table => new
    {
        EquipmentTypeId  = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
        TypeId           = table.Column<int>(type: "int", nullable: false),
        Name             = table.Column<string>(type: "nvarchar(max)", nullable: true),
        EquipmentClassId = table.Column<Guid>(type: "uniqueidentifier", nullable: true)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_EquipmentType", x => x.EquipmentTypeId);
        table.ForeignKey(
            name: "FK_EquipmentType_EquipmentClass_EquipmentClassId",
            column: x => x.EquipmentClassId,
            principalTable: "EquipmentClass",
            principalColumn: "EquipmentClassId");
    });

migrationBuilder.CreateIndex(
    name: "IX_EquipmentType_EquipmentClassId",
    table: "EquipmentType",
    column: "EquipmentClassId");

5. Data Models 📊

Below are proposed EF Core entity classes matching the new schema.

5.1 Equipment

[Table("Equipment", Schema = "irby")]
public class Equipment
{
    [Key]
    public Guid EquipmentId { get; set; }
    // … existing props …

    public int Class { get; set; }  // FK (numeric)
    public int Type  { get; set; }  // FK (numeric)

    // Navigation (optional)
    [ForeignKey(nameof(Class))]
    public EquipmentClass? EquipmentClass { get; set; }
    [ForeignKey(nameof(Type))]
    public EquipmentType?  EquipmentType  { get; set; }
}

5.2 EquipmentClass

public class EquipmentClass
{
    [Key]
    public Guid EquipmentClassId { get; set; } 
    public int ClassId          { get; set; } 
    public string? Name         { get; set; } 

    public ICollection<EquipmentType> Types { get; set; }
        = new List<EquipmentType>();
}

5.3 EquipmentType

public class EquipmentType
{
    [Key]
    public Guid EquipmentTypeId { get; set; }
    public int TypeId           { get; set; }
    public string? Name         { get; set; }

    public Guid? EquipmentClassId { get; set; }
    public EquipmentClass? EquipmentClass { get; set; }
}

6. Migration Methods ⚙️

6.1 Up Method

  • Purpose: Apply schema changes.
  • Actions:
    • Add Class & Type columns.
    • Create EquipmentClass & EquipmentType tables.
    • Establish FK + Index.
protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1) Extend Equipment
    migrationBuilder.AddColumn<int>(/* … */);
    migrationBuilder.AddColumn<int>(/* … */);

    // 2) Create reference tables
    migrationBuilder.CreateTable("EquipmentClass", /* … */);
    migrationBuilder.CreateTable("EquipmentType", /* … */);

    // 3) Add Index on FK
    migrationBuilder.CreateIndex(
        name: "IX_EquipmentType_EquipmentClassId",
        table: "EquipmentType",
        column: "EquipmentClassId");
}

6.2 Down Method

  • Purpose: Rollback changes.
  • Actions:
    • Drop EquipmentType & EquipmentClass.
    • Remove Class & Type columns from Equipment.
protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(name: "EquipmentType");
    migrationBuilder.DropTable(name: "EquipmentClass");
    migrationBuilder.DropColumn(name: "Class", schema: "irby", table: "Equipment");
    migrationBuilder.DropColumn(name: "Type",  schema: "irby", table: "Equipment");
}

7. Entity Relationship UML Diagram 🖼️

@startuml
entity "EquipmentClass" as EC {
  * EquipmentClassId : GUID <<PK>>
  * ClassId          : int
  * Name             : string
}
entity "EquipmentType" as ET {
  * EquipmentTypeId  : GUID <<PK>>
  * TypeId           : int
  * Name             : string
  --
  EquipmentClassId   : GUID <<FK>>
}
EC ||--o{ ET : "has types"
@enduml

💡 Tip: Paste the above into PlantUML to render the diagram.


8. Usage Examples 📂

  1. Seeding Reference Data

    modelBuilder.Entity<EquipmentClass>().HasData(
      new EquipmentClass { EquipmentClassId = Guid.NewGuid(), ClassId = 1, Name = "Imager" },
      new EquipmentClass { EquipmentClassId = Guid.NewGuid(), ClassId = 2, Name = "Laser" }
    );
    modelBuilder.Entity<EquipmentType>().HasData(
      new EquipmentType { EquipmentTypeId = Guid.NewGuid(), TypeId = 10, Name = "MRI Scanner", EquipmentClassId = /* GUID of Imager */ },
      new EquipmentType { EquipmentTypeId = Guid.NewGuid(), TypeId = 20, Name = "CO₂ Laser",    EquipmentClassId = /* GUID of Laser */ }
    );
    
  2. Filtering by Class / Type

    var imagers = context.Equipment
        .Where(e => e.Class == 1)
        .Include(e => e.EquipmentType)
        .ToList();
    

9. Applying the Migration 🚀

  1. Add Migration (if not already generated)

    dotnet ef migrations add AddNewTablesForEquipmentReference
    
  2. Update Database

    dotnet ef database update
    
  3. Verify

    • Confirm new columns in irby.Equipment
    • Check tables EquipmentClass, EquipmentType and their FK relationship.

10. Conclusion 🏁

This migration modernizes and normalizes equipment metadata by introducing dedicated lookup tables and linking them to the primary Equipment entity. With this structure, your application gains:

  • ✅ Flexibility to add/remove classes and types without altering core entities.
  • ✅ Data integrity via strong PK/FK constraints.
  • ✅ Simplified querying for reporting and UI lookups.

Happy coding! 🚀