📄 Documentation: Add New Tables for Equipment Reference Migration
Filename: IrbyImaging.DbContext/Migrations/20240307190246_AddNewTablesForEquipmentReference.cs
📚 Table of Contents
- Introduction
- Overview & Purpose
- Dependencies & Technologies
- Schema Changes
4.1 Adding Columns toEquipment
4.2 CreatingEquipmentClassTable
4.3 CreatingEquipmentTypeTable - Data Models
5.1Equipment
5.2EquipmentClass
5.3EquipmentType - Migration Methods
6.1Up
6.2Down - Entity Relationship UML Diagram
- Usage Examples
- Applying the Migration
- 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.Equipmenttable with new classification columns. - Introducing two reference tables:
EquipmentClassandEquipmentType. - 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
Equipmentrecord 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
*.csprojto 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&Typecolumns. - Create
EquipmentClass&EquipmentTypetables. - Establish FK + Index.
- Add
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&Typecolumns fromEquipment.
- Drop
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 📂
-
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 */ } ); -
Filtering by Class / Type
var imagers = context.Equipment .Where(e => e.Class == 1) .Include(e => e.EquipmentType) .ToList();
9. Applying the Migration 🚀
-
Add Migration (if not already generated)
dotnet ef migrations add AddNewTablesForEquipmentReference -
Update Database
dotnet ef database update -
Verify
- Confirm new columns in
irby.Equipment - Check tables
EquipmentClass,EquipmentTypeand their FK relationship.
- Confirm new columns in
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! 🚀