📚 Table of Contents

  1. Overview & Purpose
  2. Configuration & Dependencies
  3. Key Functional Areas
  4. Data Models
  5. Migration Methods
  6. UML Diagram
  7. Usage & Examples
  8. Best Practices & Considerations
  9. 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 Asset table’s Discriminator column to constrain its length.
  • Adding two new tables:
    1. CostCodes
    2. 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) to nvarchar(21) with maxLength: 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 script to inspect generated SQL for compliance.

🎉 Conclusion

The AddJobClassAndCostCodeTables migration enhances the schema by:

  • Tightening the Asset.Discriminator constraint.
  • 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! 🚀