📚 Table of Contents
- Overview & Purpose
- Configuration & Dependencies
- Key Functional Areas
- Data Models
- Migration Methods
- 5.1
Up()Method - 5.2
Down()Method
- 5.1
- UML Diagram
- Usage & Examples
- Best Practices & Considerations
- Conclusion
🧐 Overview & Purpose
This C# file defines an Entity Framework Core migration named AddJobClassAndCostCodeTables. It resides in the IrbyImaging.DbContext.Migrations namespace and is responsible for:
- Altering the existing
Assettable’sDiscriminatorcolumn to constrain its length. - Adding two new tables:
- CostCodes
- JobClassification
These additions support the domain requirements for tracking cost codes and job classifications within the Irby Imaging application.
🔗 Configuration & Dependencies
| Technology / Package | Version / Notes |
|---|---|
| .NET (Core / 5 / 6 / 7+) | Any EF Core–compatible runtime |
| Microsoft.EntityFrameworkCore | ≥ 5.0 |
| Microsoft.EntityFrameworkCore.Design | For migrations tooling |
| Microsoft.EntityFrameworkCore.SqlServer | For SQL Server provider |
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace IrbyImaging.DbContext.Migrations
{
public partial class AddJobClassAndCostCodeTables : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
// ...
}
protected override void Down(MigrationBuilder migrationBuilder)
{
// ...
}
}
}
🚀 Key Functional Areas
3.1 Alter Asset Discriminator Column
- Schema:
irby - Table:
Asset - Column:
Discriminator - Change: from
nvarchar(max)tonvarchar(21)withmaxLength: 21 - Reason: Enforce a tighter constraint on inheritance discriminator values.
3.2 Create CostCodes Table
| Column Name | Type | Nullable | Description |
|---|---|---|---|
| CostCodeId | uniqueidentifier (PK) | No | GUID primary key |
| CostCodeNumber | int | No | Numeric identifier for the code |
| Name | nvarchar(max) | Yes | Human-readable cost code name |
- Primary Key:
CostCodeId
3.3 Create JobClassification Table
| Column Name | Type | Nullable | Description |
|---|---|---|---|
| JobClassificationId | uniqueidentifier (PK) | No | GUID primary key |
| ClassNumber | int | No | Numeric job class identifier |
| Name | nvarchar(max) | Yes | Description of the job class |
| FringeAmount | decimal(18,2) | No | Additional cost (e.g., benefits) |
| SAMReference | nvarchar(max) | Yes | External system reference (e.g., SAM.gov) |
| ImageOCRContents | varbinary(max) | Yes | Binary data of OCR’d image contents |
| ImageOCRText | nvarchar(max) | Yes | Extracted OCR text |
- Primary Key:
JobClassificationId
📊 Data Models
4.1 CostCodes Entity
public class CostCode
{
public Guid CostCodeId { get; set; }
public int CostCodeNumber { get; set; }
public string? Name { get; set; }
}
- Use Case: Store and lookup cost codes for budgeting and expense reporting.
4.2 JobClassification Entity
public class JobClassification
{
public Guid JobClassificationId { get; set; }
public int ClassNumber { get; set; }
public string? Name { get; set; }
public decimal FringeAmount { get; set; }
public string? SAMReference { get; set; }
public byte[]? ImageOCRContents { get; set; }
public string? ImageOCRText { get; set; }
}
- Use Case: Define job classes with associated fringe benefits and optional OCR data for reference documents.
🔄 Migration Methods
5.1 Up() Method
This method applies schema changes:
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1) Alter existing column
migrationBuilder.AlterColumn<string>(
name: "Discriminator",
schema: "irby",
table: "Asset",
type: "nvarchar(21)",
maxLength: 21,
nullable: false,
oldClrType: typeof(string),
oldType: "nvarchar(max)");
// 2) Create CostCodes table
migrationBuilder.CreateTable(
name: "CostCodes",
columns: table => new
{
CostCodeId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
CostCodeNumber = table.Column<int>(type: "int", nullable: false),
Name = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table => { table.PrimaryKey("PK_CostCodes", x => x.CostCodeId); }
);
// 3) Create JobClassification table
migrationBuilder.CreateTable(
name: "JobClassification",
schema: "irby",
columns: table => new
{
JobClassificationId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
ClassNumber = table.Column<int>(type: "int", nullable: false),
Name = table.Column<string>(type: "nvarchar(max)", nullable: true),
FringeAmount = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
SAMReference = table.Column<string>(type: "nvarchar(max)", nullable: true),
ImageOCRContents = table.Column<byte[]>(type: "varbinary(max)", nullable: true),
ImageOCRText = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table => { table.PrimaryKey("PK_JobClassification", x => x.JobClassificationId); }
);
}
5.2 Down() Method
This method rolls back the changes:
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "CostCodes");
migrationBuilder.DropTable(name: "JobClassification", schema: "irby");
migrationBuilder.AlterColumn<string>(
name: "Discriminator",
schema: "irby",
table: "Asset",
type: "nvarchar(max)",
nullable: false,
oldClrType: typeof(string),
oldType: "nvarchar(21)",
oldMaxLength: 21);
}
📈 UML Diagram
classDiagram
direction LR
class Asset {
+Guid Id
+string Discriminator
// ... other properties
}
class CostCode {
+Guid CostCodeId
+int CostCodeNumber
+string? Name
}
class JobClassification {
+Guid JobClassificationId
+int ClassNumber
+string? Name
+decimal FringeAmount
+string? SAMReference
+byte[]? ImageOCRContents
+string? ImageOCRText
}
Asset <|-- CostCode : none
Asset <|-- JobClassification : none
Note: There are no foreign-key relationships in this migration; the entities stand alone.
🛠️ Usage & Examples
1. Add & Generate Migration
Run the following in the Package Manager Console or CLI:
# Using .NET CLI
dotnet ef migrations add AddJobClassAndCostCodeTables \
--project IrbyImaging.DbContext \
--startup-project IrbyImaging.Api
# Using PMC
Add-Migration AddJobClassAndCostCodeTables -Context IrbyDbContext
2. Apply Migration to Database
dotnet ef database update --project IrbyImaging.DbContext
3. Verifying Changes
- Inspect the SQL Server database:
- CostCodes table
- irby.JobClassification table
- irby.Asset.Discriminator column length = 21
💡 Best Practices & Considerations
- Backups: Always backup your database before applying structural migrations.
- Seed Data: Consider adding initial seed data for CostCodes and JobClassification if your application expects default entries.
- Transaction Safety: EF Core wraps migrations in a transaction by default—ensure long-running migrations are safe.
- Review SQL Scripts: Use
dotnet ef migrations scriptto inspect generated SQL for compliance.
🎉 Conclusion
The AddJobClassAndCostCodeTables migration enhances the schema by:
- Tightening the
Asset.Discriminatorconstraint. - Introducing two new domain tables (CostCodes & JobClassification).
These changes enable richer financial and workforce classification features within the Irby Imaging system. By following the usage examples above, developers can smoothly integrate and apply these schema updates.
Enjoy building with a more robust data model! 🚀