📚 Migration Documentation:
20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs
Location:
IrbyImaging.DbContext/Migrations/
20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs
📖 Table of Contents
- 🔍 Overview & Purpose
- ⚙️ Dependencies & Technologies
- 🛠️ Migration Structure
- 🚀 Key Functional Areas
- 🗄️ Data Models
- 🔧 Usage Examples
- 🖼️ UML/Class Diagram
- 💡 Conclusion
🔍 Overview & Purpose
Migration Name: AddNewAccountsTablesAndColumnsToJobTable
Created On: 2024‑07‑11
Purpose:
This Entity Framework Core migration augments the existing Job table with additional address and budget‑related fields, while also introducing two new tables (AccountBalancesandAccountLedger) under theirbyschema. These enhancements enable the application to:
- Capture detailed job location and budget metrics.
- Store monthly financial snapshots (
AccountBalances).- Track individual ledger entries for audit and reporting (
AccountLedger).
⚙️ Dependencies & Technologies
- .NET 6+ / .NET Core
- Entity Framework Core (Migrations API)
- SQL Server (T-SQL type mappings)
- C# 10 (nullable disabled context)
🛠️ Migration Structure
public partial class AddNewAccountsTablesAndColumnsToJobTable : Migration
{
protected override void Up(MigrationBuilder migrationBuilder) { /* apply changes */ }
protected override void Down(MigrationBuilder migrationBuilder) { /* revert changes */ }
}
Up: Defines schema changes when applying the migration.Down: Rolls back the changes if you remove the migration.
🚀 Key Functional Areas
Up Method (Applying Changes)
-
Add Columns to
irby.Job- Address (Line1, Line2, City, State, Zip, Country)
- Budget fields (BeginningBudget, BudgetApproved, BudgetRequested, BudgetChange, CommitmentChange)
- Progress & metadata (PercentComplete, PostingEdit, SuretyDescription)
-
Create
irby.AccountBalances- Holds monthly summary: revenues, costs, labor hours, etc.
-
Create
irby.AccountLedger- Stores detailed transactions: invoices, batches, descriptions, GL dates, etc.
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1. Add new columns to Job
migrationBuilder.AddColumn<string>("AddressLine1", schema: "irby", table: "Job", type: "nvarchar(max)", nullable: true);
// ... other columns ...
// 2. Create AccountBalances table
migrationBuilder.CreateTable(
name: "AccountBalances",
schema: "irby",
columns: table => new
{
AccountBalanceId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
ReportingYear = table.Column<int>(type: "int", nullable: false),
// ... other columns ...
FieldOffice = table.Column<string>(type: "nvarchar(max)", nullable: true),
Lead = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table => table.PrimaryKey("PK_AccountBalances", x => x.AccountBalanceId)
);
// 3. Create AccountLedger table
migrationBuilder.CreateTable(
name: "AccountLedger",
schema: "irby",
columns: table => new
{
AccountLedgerId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
ReportingYear = table.Column<int>(type: "int", nullable: false),
// ... other columns ...
ProjectManager = table.Column<string>(type: "nvarchar(max)", nullable: true),
JobType = table.Column<string>(type: "nvarchar(max)", nullable: true)
},
constraints: table => table.PrimaryKey("PK_AccountLedger", x => x.AccountLedgerId)
);
}
Down Method (Reverting Changes)
- Drops
AccountBalancesandAccountLedgertables. - Removes all newly added columns from
irby.Job.
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(name: "AccountBalances", schema: "irby");
migrationBuilder.DropTable(name: "AccountLedger", schema: "irby");
// Remove added columns from Job
migrationBuilder.DropColumn("AddressLine1", schema: "irby", table: "Job");
// ... drop all other columns ...
}
🗄️ Data Models
Job Table Enhancements
| Column Name | Type | Nullable | Default Value | Description |
|---|---|---|---|---|
| AddressLine1 | nvarchar(max) | Yes | NULL | Primary street address |
| AddressLine2 | nvarchar(max) | Yes | NULL | Secondary address line |
| City | nvarchar(max) | Yes | NULL | City name |
| State | nvarchar(max) | Yes | NULL | State or province |
| ZipCode | nvarchar(max) | Yes | NULL | Postal code |
| Country | nvarchar(max) | Yes | NULL | Country name |
| BeginningBudget | decimal(18,2) | No | 0.00 | Budget at job start |
| BudgetApproved | decimal(18,2) | No | 0.00 | Approved budget |
| BudgetRequested | decimal(18,2) | No | 0.00 | Requested additional budget |
| BudgetChange | decimal(18,2) | No | 0.00 | Net change in budget |
| CommitmentChange | decimal(18,2) | No | 0.00 | Change in purchase commitments |
| PercentComplete | nvarchar(max) | Yes | NULL | Textual or % completion indicator |
| PostingEdit | nvarchar(max) | Yes | NULL | Notes on posting adjustments |
| SuretyDescription | nvarchar(max) | Yes | NULL | Bonding / surety company details |
AccountBalances Table
| Column Name | Type | Nullable | Description |
|---|---|---|---|
| AccountBalanceId | uniqueidentifier | No | Primary Key (GUID) |
| ReportingYear | int | No | Year of the report |
| ReportingMonth | int | No | Month number (1–12) |
| JobNumber | nvarchar(max) | Yes | Unique job code |
| JobName | nvarchar(max) | Yes | Human‑readable job title |
| CostCode | nvarchar(max) | Yes | Project cost code |
| CostCodeDescription | nvarchar(max) | Yes | Description of the cost code |
| AccountingCode | nvarchar(max) | Yes | Internal accounting category |
| LedgerType | nvarchar(max) | Yes | Type of ledger (e.g., AP, AR) |
| ObjectAccount | nvarchar(max) | Yes | GL object account number |
| Revenue | decimal(18,2) | No | Recognized revenue |
| Cost | decimal(18,2) | No | Recorded cost |
| ManHours | decimal(18,2) | No | Labor hours logged |
| State, Customer, TypeOfWork, FieldOffice, Lead | |||
| nvarchar(max) | Yes | Additional dimensions for slicing and dicing financial data |
AccountLedger Table
| Column Name | Type | Nullable | Description |
|---|---|---|---|
| AccountLedgerId | uniqueidentifier | No | Primary Key (GUID) |
| ReportingYear | int | No | Year of the ledger entry |
| ReportingMonth | int | No | Month number |
| JobNumber, JobName | |||
| nvarchar(max) | Yes | Job identifiers | |
| AccountId | nvarchar(max) | Yes | General ledger account ID |
| Amount | decimal(18,2) | No | Debit or credit amount |
| Description | nvarchar(max) | Yes | Explanation or narrative |
| Remark | nvarchar(max) | Yes | Free‑form notes |
| ObjectAccount, CostCode, CostCodeDescription | |||
| nvarchar(max) | Yes | Cost classification | |
| ReverseOrVoid | nvarchar(max) | Yes | Flag for reversal or voided transactions |
| Offset | nvarchar(max) | Yes | Offset account |
| InvoiceDate | datetime2 | Yes | Invoice posting date |
| InvoiceNumber, PurchaseOrder, ReceiptNumber | |||
| nvarchar(max) | Yes | Reference numbers | |
| GLDate | datetime2 | No | General ledger date |
| BatchDate | datetime2 | No | Batch posting date |
| BatchNumber, DocumentNumber, DocumentType, SerialNumber | |||
| nvarchar(max) | Yes | Document tracking | |
| State, Customer, TypeOfWork, FieldOffice, Lead, Manager, ProjectEstimator, | |||
| Superintendent, ProjectManager, JobType | |||
| nvarchar(max) | Yes | Extended metadata |
🔧 Usage Examples
- Add & Apply Migration
dotnet ef migrations add AddNewAccountsTablesAndColumnsToJobTable dotnet ef database update - Rollback
dotnet ef database update LastGoodMigrationName - Generate SQL Script
dotnet ef migrations script 20240711171136_AddNewAccountsTablesAndColumnsToJobTable
🖼️ UML/Class Diagram
classDiagram
class Job {
+string AddressLine1
+string AddressLine2
+string City
+string State
+string ZipCode
+string Country
+decimal BeginningBudget
+decimal BudgetApproved
+decimal BudgetRequested
+decimal BudgetChange
+decimal CommitmentChange
+string PercentComplete
+string PostingEdit
+string SuretyDescription
}
class AccountBalances {
+Guid AccountBalanceId
+int ReportingYear
+int ReportingMonth
+string JobNumber
+string JobName
+string CostCode
+string CostCodeDescription
+string AccountingCode
+string LedgerType
+string ObjectAccount
+decimal Revenue
+decimal Cost
+decimal ManHours
+string State
+string Customer
+string TypeOfWork
+string FieldOffice
+string Lead
}
class AccountLedger {
+Guid AccountLedgerId
+int ReportingYear
+int ReportingMonth
+string JobNumber
+string JobName
+string AccountId
+decimal Amount
+string Description
+string Remark
+string ObjectAccount
+string CostCode
+string CostCodeDescription
+string ReverseOrVoid
+string Offset
+DateTime? InvoiceDate
+string InvoiceNumber
+string PurchaseOrder
+string ReceiptNumber
+DateTime GLDate
+DateTime BatchDate
+string BatchNumber
+string DocumentNumber
+string DocumentType
+string SerialNumber
+string State
+string Customer
+string TypeOfWork
+string FieldOffice
+string Lead
+string Manager
+string ProjectEstimator
+string Superintendent
+string ProjectManager
+string JobType
}
💡 Conclusion
This migration strengthens the Job domain by embedding address and budget management directly into the core schema. Additionally, the two newly introduced tables—AccountBalances and AccountLedger—provide a robust foundation for:
- ✅ Financial reporting
- ✅ Detailed audit trails
- ✅ Scalable accounting integrations
By following the usage examples above, you can seamlessly integrate these enhancements into your database, enabling richer analytics and tighter fiscal controls in the Irby Imaging system.