📚 Documentation: 20220302171015_NewRefTables.cs


🚀 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration at a Glance
  4. Schema & Table Definitions
  5. Relationships & ER Diagram
  6. Up() Method Walkthrough
  7. Down() Method Walkthrough
  8. How to Apply the Migration
  9. Usage Examples
  10. Conclusion

🧐 Overview & Purpose

This Entity Framework Core Migration adds four new reference tables under the irby schema:

  • CarrierType
  • GeneralLedger
  • CarrierTypeDetail
  • GLTypeDetail

These tables store reference data for carrier types and general ledger categories, along with their detailed line items.
They are essential for normalizing lookup lists within the IrbyImaging application, enabling:

  • Centralized management of codes/descriptions
  • Soft‑deletion via IsInactive flags
  • Auditability through AddedBy, AddedDate, ModifiedBy, ModifiedDate

🔧 Dependencies & Technologies

Technology / Package Version (approx.) Purpose
.NET Core / .NET 5+ 5.0+ Runtime platform
Microsoft.EntityFrameworkCore 5.x ORM & Migration framework
SQL Server any Target relational database

📝 Migration at a Glance

public partial class NewRefTables : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create 4 tables + 2 FKs + 2 Indexes
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Drop tables in reverse order
    }
}

📊 Schema & Table Definitions

1. CarrierType

Column Type Nullable Description
Id uniqueidentifier No PK, GUID
Carrier nvarchar(max) Yes Carrier name or code
IsInactive bit No Soft‑delete flag
AddedBy nvarchar(max) Yes Username that created the row
AddedDate datetime2 No Timestamp when created
ModifiedBy nvarchar(max) Yes Username that last modified row
ModifiedDate datetime2 No Timestamp when modified
CREATE TABLE irby.CarrierType
(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  Carrier NVARCHAR(MAX) NULL,
  IsInactive BIT NOT NULL,
  AddedBy NVARCHAR(MAX) NULL,
  AddedDate DATETIME2 NOT NULL,
  ModifiedBy NVARCHAR(MAX) NULL,
  ModifiedDate DATETIME2 NOT NULL
);

2. GeneralLedger

Column Type Nullable Description
Id uniqueidentifier No PK, GUID
Code nvarchar(max) Yes GL code
Description nvarchar(max) Yes GL description
IsInactive bit No Soft‑delete flag
AddedBy nvarchar(max) Yes Creator username
AddedDate datetime2 No Creation timestamp
ModifiedBy nvarchar(max) Yes Modifier username
ModifiedDate datetime2 No Modification timestamp
CREATE TABLE irby.GeneralLedger
(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  Code NVARCHAR(MAX) NULL,
  Description NVARCHAR(MAX) NULL,
  IsInactive BIT NOT NULL,
  AddedBy NVARCHAR(MAX) NULL,
  AddedDate DATETIME2 NOT NULL,
  ModifiedBy NVARCHAR(MAX) NULL,
  ModifiedDate DATETIME2 NOT NULL
);

3. CarrierTypeDetail

Column Type Nullable Description
Id uniqueidentifier No PK, GUID
Carrier nvarchar(max) Yes Specific carrier detail
IsInactive bit No Soft‑delete flag
AddedBy nvarchar(max) Yes Creator
AddedDate datetime2 No Created timestamp
ModifiedBy nvarchar(max) Yes Last modifier
ModifiedDate datetime2 No Last modification timestamp
CarrierTypeId uniqueidentifier Yes  FK → irby.CarrierType(Id)
CREATE TABLE CarrierTypeDetail
(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  Carrier NVARCHAR(MAX) NULL,
  IsInactive BIT NOT NULL,
  AddedBy NVARCHAR(MAX) NULL,
  AddedDate DATETIME2 NOT NULL,
  ModifiedBy NVARCHAR(MAX) NULL,
  ModifiedDate DATETIME2 NOT NULL,
  CarrierTypeId UNIQUEIDENTIFIER NULL,
  FOREIGN KEY (CarrierTypeId)
    REFERENCES irby.CarrierType(Id)
    ON DELETE RESTRICT
);

CREATE INDEX IX_CarrierTypeDetail_CarrierTypeId 
  ON CarrierTypeDetail(CarrierTypeId);

4. GLTypeDetail

Column Type Nullable Description
Id uniqueidentifier No PK, GUID
Code nvarchar(max) Yes GL sub‑code
Description nvarchar(max) Yes GL sub‑description
IsInactive bit No Soft‑delete flag
AddedBy nvarchar(max) Yes Creator
AddedDate datetime2 No Created timestamp
ModifiedBy nvarchar(max) Yes Last modifier
ModifiedDate datetime2 No Last modification timestamp
GeneralLedgerId uniqueidentifier Yes  FK → irby.GeneralLedger(Id)
CREATE TABLE GLTypeDetail
(
  Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
  Code NVARCHAR(MAX) NULL,
  Description NVARCHAR(MAX) NULL,
  IsInactive BIT NOT NULL,
  AddedBy NVARCHAR(MAX) NULL,
  AddedDate DATETIME2 NOT NULL,
  ModifiedBy NVARCHAR(MAX) NULL,
  ModifiedDate DATETIME2 NOT NULL,
  GeneralLedgerId UNIQUEIDENTIFIER NULL,
  FOREIGN KEY (GeneralLedgerId)
    REFERENCES irby.GeneralLedger(Id)
    ON DELETE RESTRICT
);

CREATE INDEX IX_GLTypeDetail_GeneralLedgerId 
  ON GLTypeDetail(GeneralLedgerId);

🔗 Relationships & ER Diagram

erDiagram
    irby.CarrierType ||--o{ CarrierTypeDetail : has
    irby.GeneralLedger ||--o{ GLTypeDetail : has
  • CarrierType 1 → * ⟶ CarrierTypeDetail
  • GeneralLedger 1 → * ⟶ GLTypeDetail

🏗️ Up() Method Walkthrough

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Create CarrierType table in 'irby' schema
    migrationBuilder.CreateTable(...);

    // 2️⃣ Create GeneralLedger table in 'irby' schema
    migrationBuilder.CreateTable(...);

    // 3️⃣ Create CarrierTypeDetail table with FK to CarrierType
    migrationBuilder.CreateTable(...);

    // 4️⃣ Create GLTypeDetail table with FK to GeneralLedger
    migrationBuilder.CreateTable(...);

    // 5️⃣ Build indexes for faster FK lookups
    migrationBuilder.CreateIndex(...);
}
  • Order Matters
    • Parent tables (CarrierType, GeneralLedger) are created before child tables (CarrierTypeDetail, GLTypeDetail).
    • Indexes are added last to optimize foreign-key joins.

🛠️ Down() Method Walkthrough

protected override void Down(MigrationBuilder migrationBuilder)
{
    // Drop children first to avoid FK constraint errors
    migrationBuilder.DropTable("CarrierTypeDetail");
    migrationBuilder.DropTable("GLTypeDetail");
    
    // Drop parent tables in schema 'irby'
    migrationBuilder.DropTable("CarrierType", schema: "irby");
    migrationBuilder.DropTable("GeneralLedger", schema: "irby");
}
  • Reverse Order ensures no conflicts when removing FKs.

⚙️ How to Apply the Migration

  1. Add Migration (already done):
    dotnet ef migrations add NewRefTables
    
  2. Update Database:
    dotnet ef database update
    
  3. Verify
    • Check irby.CarrierType, irby.GeneralLedger tables.
    • Ensure FKs and indexes are in place.

📖 Usage Examples

// Insert a new CarrierType
var carrierType = new CarrierType {
    Id = Guid.NewGuid(),
    Carrier = "Standard Shipping",
    IsInactive = false,
    AddedBy = "system",
    AddedDate = DateTime.UtcNow,
    ModifiedBy = "system",
    ModifiedDate = DateTime.UtcNow
};
context.CarrierTypes.Add(carrierType);
context.SaveChanges();

// Create a detail for that type
var detail = new CarrierTypeDetail {
    Id = Guid.NewGuid(),
    Carrier = "Expedited",
    CarrierTypeId = carrierType.Id,
    IsInactive = false,
    AddedBy = "admin",
    AddedDate = DateTime.UtcNow,
    ModifiedBy = "admin",
    ModifiedDate = DateTime.UtcNow
};
context.CarrierTypeDetails.Add(detail);
context.SaveChanges();

🏁 Conclusion

This migration NewRefTables sets up foundational reference tables for carrier and general ledger data.

  • Normalized design with parent/child relationships
  • Audit fields and soft‑delete flags for robust data governance
  • Seamless integration into EF Core’s migration pipeline

Now, your application can reliably manage and query carriers and GL data in a standardized way! 🎉