📜 Documentation: 20240229193731_AddPurchaseOrderTable.cs


📚 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Schema Changes
  5. Data Models Affected
  6. EF Core Migration Workflow
  7. Usage Examples
  8. UML Diagram: Entity Relationship
  9. Conclusion

1. Overview & Purpose 🎯

This C# file is an Entity Framework Core migration that:

  • Drops the old ODSAssets table.
  • Creates a new PurchaseOrder table under the irby schema.
  • Establishes a foreign key relationship between PurchaseOrder and the existing Job table.

Why?
To refactor the database schema and introduce a dedicated table for purchase orders, improving data organization and alignment with business requirements.


2. Dependencies & Technologies 🔧

Technology / Package Version (Example) Purpose
.NET 6+ / .NET 7+ 6.0, 7.0 Runtime
Microsoft.EntityFrameworkCore 6.x / 7.x Core ORM for data access
Microsoft.EntityFrameworkCore.Tools 6.x / 7.x EF migrations & CLI tooling
C# 10 / 11 Language

3. Key Functional Areas 🗂️

  1. Migration Class
    • Named AddPurchaseOrderTable
    • Inherits from Migration
  2. Up(MigrationBuilder)
    • Defines the forward schema changes.
  3. Down(MigrationBuilder)
    • Defines the rollback (reverse) changes.

4. Schema Changes 🔄

Up() Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop old table
    migrationBuilder.DropTable(name: "ODSAssets");

    // 2️⃣ Create new PurchaseOrder table
    migrationBuilder.CreateTable(
        name: "PurchaseOrder",
        schema: "irby",
        columns: table => new
        {
            PurchaseOrderId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            Org_Bu           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Org_IntDivision  = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Name             = table.Column<string>(type: "nvarchar(max)", nullable: true),
            DisplayName      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            OrderDate        = table.Column<DateTime>(type: "datetime2", nullable: true),
            OrderNumber      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Type             = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Description      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            VendorName       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            VendorNumber     = table.Column<string>(type: "nvarchar(max)", nullable: true),
            JobId            = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_PurchaseOrder", x => x.PurchaseOrderId);
            table.ForeignKey(
                name: "FK_PurchaseOrder_Job_JobId",
                column: x => x.JobId,
                principalSchema: "irby",
                principalTable: "Job",
                principalColumn: "JobId",
                onDelete: ReferentialAction.Cascade);
        });

    // 3️⃣ Create index for performance on foreign key
    migrationBuilder.CreateIndex(
        name: "IX_PurchaseOrder_JobId",
        schema: "irby",
        table: "PurchaseOrder",
        column: "JobId");
}

What happens?

  • Drops ODSAssets to remove outdated asset-tracking schema.
  • Creates irby.PurchaseOrder with a GUID PK.
  • Adds nullable fields for organizational, descriptive, and vendor-related data.
  • Enforces referential integrity with Job via a cascade delete.
  • Adds an index on JobId to speed up joins and lookups.

Down() Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop the PurchaseOrder table
    migrationBuilder.DropTable(
        name: "PurchaseOrder",
        schema: "irby");

    // 2️⃣ Recreate the old ODSAssets table
    migrationBuilder.CreateTable(
        name: "ODSAssets",
        columns: table => new
        {
            EquipmentId           = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            AccountingClass       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            AssetManufacturer     = table.Column<string>(type: "nvarchar(max)", nullable: true),
            AssetNumber           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            AssetRemark           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            AssetRemark2          = table.Column<string>(type: "nvarchar(max)", nullable: true),
            DateAcquired          = table.Column<DateTime>(type: "datetime2", nullable: true),
            DateDisposed          = table.Column<DateTime>(type: "datetime2", nullable: true),
            Description           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            DeviceType            = table.Column<string>(type: "nvarchar(max)", nullable: true),
            DisplayName           = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Division              = table.Column<string>(type: "nvarchar(max)", nullable: true),
            EquipmentClass        = table.Column<string>(type: "nvarchar(max)", nullable: true),
            FinanceMethod         = table.Column<string>(type: "nvarchar(max)", nullable: true),
            FuelType              = table.Column<string>(type: "nvarchar(max)", nullable: true),
            JobId                 = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            Name                  = table.Column<string>(type: "nvarchar(max)", nullable: true),
            PreventiveMaintCatDesc = table.Column<string>(type: "nvarchar(max)", nullable: true),
            PurchasePrice         = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
            RateGroup             = table.Column<string>(type: "nvarchar(max)", nullable: true),
            SerialNumber          = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Status                = table.Column<string>(type: "nvarchar(max)", nullable: true),
            UnitTagNumber         = table.Column<string>(type: "nvarchar(max)", nullable: true),
            Year                  = table.Column<string>(type: "nvarchar(max)", nullable: true)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_ODSAssets", x => x.EquipmentId);
        });
}

What happens?

  • Rolls back the migration by dropping PurchaseOrder.
  • Restores the original ODSAssets table exactly as it was prior to this migration.

5. Data Models Affected 🏗️

While this migration itself is auto-generated, it corresponds to EF Core model classes:

  • Entity: PurchaseOrder
    • Guid PurchaseOrderId
    • string? Org_Bu
    • string? Org_IntDivision
    • string? Name
    • string? DisplayName
    • DateTime? OrderDate
    • string? OrderNumber
    • string? Type
    • string? Description
    • string? VendorName
    • string? VendorNumber
    • Guid JobId (FK to Job)
  • Entity: Job (pre-existing)
    • Guid JobId
    • … other properties …

6. EF Core Migration Workflow ⚙️

  1. Add Migration
    dotnet ef migrations add AddPurchaseOrderTable \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  2. Review & Tweak (if necessary)
  3. Apply Migration
    dotnet ef database update \
        --project IrbyImaging.DbContext \
        --startup-project IrbyImaging.Api
    
  4. Commit the generated .cs file to source control.

7. Usage Examples 🧩

  • Querying Purchase Orders
    using var context = new IrbyDbContext();
    var orders = await context.Set<PurchaseOrder>()
                              .Include(po => po.Job)
                              .Where(po => po.OrderDate >= DateTime.UtcNow.AddMonths(-1))
                              .ToListAsync();
    
  • Creating a New Purchase Order
    var newPo = new PurchaseOrder {
        PurchaseOrderId = Guid.NewGuid(),
        Org_Bu          = "North",
        Name            = "PO for Equipment",
        OrderNumber     = "PO-2024-0001",
        OrderDate       = DateTime.UtcNow,
        JobId           = existingJobId
    };
    context.PurchaseOrders.Add(newPo);
    await context.SaveChangesAsync();
    

8. UML Diagram: Entity Relationship 📈

erDiagram
    irby.PurchaseOrder {
        GUID PurchaseOrderId PK
        string Org_Bu
        string Org_IntDivision
        string Name
        string DisplayName
        datetime OrderDate
        string OrderNumber
        string Type
        string Description
        string VendorName
        string VendorNumber
        GUID JobId FK
    }

    irby.Job {
        GUID JobId PK
        // … other Job fields …
    }

    PurchaseOrder ||--|| Job : "belongs to"

9. Conclusion ✅

This migration file AddPurchaseOrderTable refines the database schema by replacing an obsolete ODSAssets table with a new, purpose-built PurchaseOrder table. It ensures:

  • Clean separation of purchase-order data.
  • Strong referential integrity via FK to Job.
  • Easy rollback via the Down method.

Use standard EF Core commands to apply or revert these changes, and leverage the new table in your data-access layer for structured order management! 🚀