📜 Documentation: 20240229193731_AddPurchaseOrderTable.cs
📚 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Schema Changes
- Data Models Affected
- EF Core Migration Workflow
- Usage Examples
- UML Diagram: Entity Relationship
- Conclusion
1. Overview & Purpose 🎯
This C# file is an Entity Framework Core migration that:
- Drops the old
ODSAssetstable. - Creates a new
PurchaseOrdertable under theirbyschema. - Establishes a foreign key relationship between
PurchaseOrderand the existingJobtable.
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 🗂️
- Migration Class
- Named
AddPurchaseOrderTable - Inherits from
Migration
- Named
- Up(MigrationBuilder)
- Defines the forward schema changes.
- 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
ODSAssetsto remove outdated asset-tracking schema. - Creates
irby.PurchaseOrderwith a GUID PK. - Adds nullable fields for organizational, descriptive, and vendor-related data.
- Enforces referential integrity with
Jobvia a cascade delete. - Adds an index on
JobIdto 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
ODSAssetstable 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:
PurchaseOrderGuid PurchaseOrderIdstring? Org_Bustring? Org_IntDivisionstring? Namestring? DisplayNameDateTime? OrderDatestring? OrderNumberstring? Typestring? Descriptionstring? VendorNamestring? VendorNumberGuid JobId(FK toJob)
- Entity:
Job(pre-existing)Guid JobId- … other properties …
6. EF Core Migration Workflow ⚙️
- Add Migration
dotnet ef migrations add AddPurchaseOrderTable \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api - Review & Tweak (if necessary)
- Apply Migration
dotnet ef database update \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api - Commit the generated
.csfile 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
Downmethod.
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! 🚀