π¦ AddEquipmentTable Migration Documentation
π Table of Contents
- π Overview & Purpose
- π§ Dependencies & Technologies
- π Schema Design & Data Models
- βοΈ Migration Operations
- π οΈ Entity Definitions
- π Table Structures
- πΌοΈ UML Diagram
- β Applying & Rolling Back
- βοΈ Usage Examples
- π 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
- NuGet Package:
- SQL Server (using
datetime2,uniqueidentifier,decimal(18,2)types) - C# 10+
π Schema Design & Data Models
The migration adds:
-
irby.Equipment
- Strongly typed properties for asset management.
JobIdforeign key β irby.Job(JobId).
-
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
Jobtable. - Index on
JobIdoptimizes 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
-
Query Equipment by Job
using var context = new IrbyDbContext(); var equipments = context.Equipment .Where(e => e.JobId == someJobId) .ToList(); -
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(); -
ODS Export
SELECT * INTO ODSAssets FROM irby.Equipment;
π Conclusion
This migration enhances the IrbyImaging data model by:
- Introducing a fully constrained
irby.Equipmenttable for operational use. - Creating a lightweight
ODSAssetstable 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. π