📚 Documentation: 20220302171015_NewRefTables.cs
🚀 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration at a Glance
- Schema & Table Definitions
- Relationships & ER Diagram
- Up() Method Walkthrough
- Down() Method Walkthrough
- How to Apply the Migration
- Usage Examples
- 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
- Add Migration (already done):
dotnet ef migrations add NewRefTables
- Update Database:
dotnet ef database update
- 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! 🎉