πŸ“¦ AddEquipmentTable Migration Documentation


πŸ“‘ Table of Contents

  1. πŸš€ Overview & Purpose
  2. πŸ”§ Dependencies & Technologies
  3. πŸ“ Schema Design & Data Models
  4. βš™οΈ Migration Operations
  5. πŸ› οΈ Entity Definitions
  6. πŸ“Š Table Structures
  7. πŸ–ΌοΈ UML Diagram
  8. βœ… Applying & Rolling Back
  9. βš™οΈ Usage Examples
  10. πŸ“ Conclusion

πŸš€ Overview & Purpose

This migration file, 20240229185324_AddEquipmentTable.cs, is part of the IrbyImaging.DbContext project. It introduces two new tablesβ€”Equipment (within the irby schema) and ODSAssetsβ€”to support equipment tracking and ODS reporting.

  • Primary Goal:
    • Create a robust Equipment table with a foreign key to the existing Job table.
    • Introduce ODSAssets for data warehousing or ETL processes without constraints.

πŸ”§ Dependencies & Technologies

  • .NET 6+ or later
  • Entity Framework Core
    • NuGet Package: Microsoft.EntityFrameworkCore.Migrations
  • SQL Server (using datetime2, uniqueidentifier, decimal(18,2) types)
  • C# 10+

πŸ“ Schema Design & Data Models

The migration adds:

  1. irby.Equipment

    • Strongly typed properties for asset management.
    • JobId foreign key β†’ irby.Job(JobId).
  2. ODSAssets

    • Mirror of Equipment structure, but without primary/foreign key constraints.
    • Designed for ODS (Operational Data Store) or reporting layers.

βš™οΈ Migration Operations

Up Method (Apply Migration)

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Create irby.Equipment table
    migrationBuilder.CreateTable(
        name: "Equipment",
        schema: "irby",
        columns: table => new
        {
            EquipmentId        = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            // ... other columns ...
            PurchasePrice      = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            JobId              = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Equipment", x => x.EquipmentId);
            table.ForeignKey(
                name: "FK_Equipment_Job_JobId",
                column: x => x.JobId,
                principalSchema: "irby",
                principalTable: "Job",
                principalColumn: "JobId",
                onDelete: ReferentialAction.Cascade);
        });

    // 2️⃣ Create ODSAssets table (no PK/FK)
    migrationBuilder.CreateTable(
        name: "ODSAssets",
        columns: table => new
        {
            EquipmentId   = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            // ... same columns as Equipment ...
            PurchasePrice = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            JobId         = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
        },
        constraints: table => { });

    // 3️⃣ Index on Equipment.JobId
    migrationBuilder.CreateIndex(
        name: "IX_Equipment_JobId",
        schema: "irby",
        table: "Equipment",
        column: "JobId");
}

Key Points

  • Primary Key on EquipmentId.
  • Foreign Key enforces referential integrity with Job table.
  • Index on JobId optimizes joins and lookups.

Down Method (Revert Migration)

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(name: "Equipment", schema: "irby");
    migrationBuilder.DropTable(name: "ODSAssets");
}
  • Drops both tables to rollback the schema changes.

πŸ› οΈ Entity Definitions

Although this file is a migration, the corresponding EF Core entity might look like:

namespace IrbyImaging.Core.Models
{
    public class Equipment
    {
        public Guid EquipmentId { get; set; }
        public string Name { get; set; }
        public string DisplayName { get; set; }
        // ... other properties ...
        public decimal PurchasePrice { get; set; }
        public Guid JobId { get; set; }
        public Job Job { get; set; }
    }

    public class ODSAsset
    {
        public Guid EquipmentId { get; set; }
        // Mirror of Equipment properties without nav props
    }
}

πŸ“Š Table Structures

1. irby.Equipment

Column Type Nullable Description
EquipmentId uniqueidentifier No PK, unique key
Name nvarchar(max) Yes Internal equipment identifier
DisplayName nvarchar(max) Yes User-friendly name
AssetNumber nvarchar(max) Yes Financial asset tracking number
UnitTagNumber nvarchar(max) Yes Tag number
SerialNumber nvarchar(max) Yes Manufacturer’s serial number
DateAcquired datetime2 Yes Acquisition date
DateDisposed datetime2 Yes Disposal date
Description nvarchar(max) Yes Rich-text description
FinanceMethod nvarchar(max) Yes Lease / Purchase / Rental etc.
Status nvarchar(max) Yes Active / Inactive / Maintenance
RateGroup nvarchar(max) Yes Billing or rate classification
AccountingClass nvarchar(max) Yes GL account classification
EquipmentClass nvarchar(max) Yes Asset class grouping
AssetManufacturer nvarchar(max) Yes OEM name
Year nvarchar(max) Yes Year of manufacture
FuelType nvarchar(max) Yes Gas / Diesel / Electric etc.
PreventiveMaintCatDesc nvarchar(max) Yes PM category description
DeviceType nvarchar(max) Yes Hardware / Software / Consumable
Division nvarchar(max) Yes Business unit
AssetRemark nvarchar(max) Yes Free-text remark
AssetRemark2 nvarchar(max) Yes Additional remark
PurchasePrice decimal(18,2) No Cost at purchase
JobId uniqueidentifier No FK β†’ irby.Job(JobId)

2. ODSAssets

Note: No PK/FK constraints hereβ€”designed for data exports.

Column Type Nullable
EquipmentId uniqueidentifier No
Name nvarchar(max) Yes
… … …
PurchasePrice decimal(18,2) No
JobId uniqueidentifier No

πŸ–ΌοΈ UML Diagram

classDiagram
    class Equipment {
      +Guid EquipmentId
      +string Name
      +string DisplayName
      +decimal PurchasePrice
      +Guid JobId
    }
    class Job {
      +Guid JobId
      +string Title
      // other properties…
    }
    Equipment --> Job : FK_JobId

    class ODSAssets {
      +Guid EquipmentId
      // mirror properties...
      +decimal PurchasePrice
      +Guid JobId
    }

βœ… Applying & Rolling Back

Apply Migration

# 1. Add migration (already created)
dotnet ef migrations add AddEquipmentTable

# 2. Update database
dotnet ef database update

Rollback Migration

# Rollback to previous migration
dotnet ef database update <PreviousMigrationName>

βš™οΈ Usage Examples

  1. Query Equipment by Job

    using var context = new IrbyDbContext();
    var equipments = context.Equipment
                             .Where(e => e.JobId == someJobId)
                             .ToList();
    
  2. Insert New Equipment

    var newEquip = new Equipment {
        EquipmentId = Guid.NewGuid(),
        Name        = "X-Ray Machine",
        PurchasePrice = 25000.00m,
        JobId       = existingJobId
    };
    context.Equipment.Add(newEquip);
    context.SaveChanges();
    
  3. ODS Export

    SELECT * 
    INTO ODSAssets 
    FROM irby.Equipment;
    

πŸ“ Conclusion

This migration enhances the IrbyImaging data model by:

  • Introducing a fully constrained irby.Equipment table for operational use.
  • Creating a lightweight ODSAssets table for analytics and reporting.

By integrating these changes, the system can now track and report equipment-related data more effectively, leveraging EF Core's migration capabilities for seamless schema evolution. πŸŽ‰