📖 Documentation:

IrbyImaging.DbContext/Migrations/20240711171136_AddNewAccountsTablesAndColumnsToJobTable.Designer.cs

⚠️ Auto‑generated by Entity Framework Core.
Do not modify manually—changes will be overwritten by subsequent migrations.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Models Introduced or Updated
  5. Code Snippets & Examples
  6. UML Class Diagram (Mermaid)
  7. Usage & Execution
  8. Best Practices & Tips
  9. Conclusion

🔍 1. Overview & Purpose

This Designer.cs file is a snapshot of your EF Core model at the time you ran:

dotnet ef migrations add AddNewAccountsTablesAndColumnsToJobTable
  • It lives alongside the human‑editable 20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs migration.
  • BuildTargetModel describes the entire conceptual model (all entities, properties, relationships, schemas, indexes).
  • The migration’s Up() and Down() methods reference this snapshot to compute diffs against earlier versions.

In this particular migration we:

  1. Add two new tables:
    • AccountBalances
    • AccountLedger
  2. Extend the Job table by adding accounting‑related columns.

🔑 Key goal: capture on‑chain financial records for each job, enabling detailed reporting and reconciliation.


🛠 2. Dependencies & Technologies

Component Version / Notes
.NET / C# .NET 7.0 (C# 10)
Entity Framework Core EF Core 7.0.7
Database Provider Microsoft.EntityFrameworkCore.SqlServer
Schema Uses irby schema for domain tables
Value Conversions UseIdentityColumns() for SQL Server identity

🔑 3. Key Functional Areas

3.1 BuildTargetModel Method

protected override void BuildTargetModel(ModelBuilder modelBuilder)
{
    modelBuilder
        .HasAnnotation("ProductVersion", "7.0.7")
        .HasAnnotation("Relational:MaxIdentifierLength", 128);
    SqlServerModelBuilderExtensions.UseIdentityColumns(modelBuilder);

    // … entity configurations follow …
}
  • Purpose: reconstruct the final shape of your EF model so EF can compare it to the snapshot in the Migrations history.
  • Annotations:
    • ProductVersion — EF Core version
    • Relational:MaxIdentifierLength — ensures SQL Server limits are honored
  • UseIdentityColumns: globally configures integer primary keys as IDENTITY columns.

3.2 Entity Configurations

For each entity you’ll see:

  1. b.Property<…>(“Name”) — defines CLR → DB column mapping
  2. HasColumnType(…) — low‑level SQL data type
  3. ValueGeneratedOnAdd() — identity/auto‑GUID generation
  4. HasKey(…) — primary key
  5. HasIndex(…) — non‑clustered / filtering indexes
  6. ToTable(…) — table name and schema

Example for AccountBalances:

modelBuilder.Entity("IrbyImaging.DomainClasses.AccountBalances", b =>
{
    b.Property<Guid>("AccountBalanceId")
        .ValueGeneratedOnAdd()
        .HasColumnType("uniqueidentifier");
    // … other properties …
    b.HasKey("AccountBalanceId");
    b.ToTable("AccountBalances","irby");
});

3.3 Relationships & Navigation Properties

Toward the end of the method, EF wires up FKs and Navigations:

modelBuilder.Entity("IrbyImaging.DomainClasses.Asset", b =>
{
    b.HasOne("IrbyImaging.DomainClasses.AssetModel", "Model")
     .WithMany()
     .HasForeignKey("ModelId")
     .OnDelete(DeleteBehavior.Cascade)
     .IsRequired();
    // … etc …
});
  • HasOne/WithMany — 1:N relationships
  • OnDelete — cascade behaviour in the database
  • Navigation("…") — hints for EF’s change tracker

💾 4. Data Models Introduced or Updated

4.1 AccountBalances Table

Column Type Notes
AccountBalanceId uniqueidentifier PK, autogenerated GUID
AccountingCode nvarchar(max) ledger classification
Cost decimal(18,2) actual cost
CostCode nvarchar(max) sub‑ledger code
CostCodeDescription nvarchar(max) description of cost code
Customer nvarchar(max) customer name
FieldOffice nvarchar(max) branch office
JobName nvarchar(max) friendly job title
JobNumber nvarchar(max) job identifier
Lead nvarchar(max) job lead
LedgerType nvarchar(max) revenue/cost/…
ManHours decimal(18,2) labor hours
ReportingMonth int reporting period month
ReportingYear int reporting period year
Revenue decimal(18,2) billed amount
State nvarchar(max) state code
TypeOfWork nvarchar(max) e.g. Construction, A&E…

📦 Schema: irby.AccountBalances


4.2 AccountLedger Table

Column Type Notes
AccountLedgerId uniqueidentifier PK, autogenerated GUID
AccountId nvarchar(max) reference to general account
Amount decimal(18,2) transaction amount
BatchDate datetime2 posting date
BatchNumber nvarchar(max) system batch identifier
CostCode nvarchar(max) cost ledger code
CostCodeDescription nvarchar(max) description
Customer nvarchar(max) customer
DocumentType nvarchar(max) invoice/credit/debit type
DocumentNumber nvarchar(max) invoice/PO number
GLDate datetime2 GL posting date
InvoiceDate datetime2 (null) actual invoice date
JobName nvarchar(max) job title
JobNumber nvarchar(max) job identifier
… and many more columns capturing job, department, manager, etc.

📦 Schema: irby.AccountLedger


4.3 Job Table – New Columns

The Job entity snapshot shows existing columns plus newly added ones:

Column Type Description
BeginningBudget decimal(18,2) initial approved budget
BudgetRequested decimal(18,2) change-requested amount
BudgetApproved decimal(18,2) approved budget after change
BudgetChange decimal(18,2) difference between Approved/Beginning
CommitmentChange decimal(18,2) net change to commitments
ContractType nvarchar(max) e.g. Lump‑sum, Time & Materials
RevenueDerivative nvarchar(max) system‑derived revenue forecast
… … …

🔍 Index on JdeNumber ensures uniqueness in the database:

b.HasIndex("JdeNumber")
 .IsUnique()
 .HasFilter("[JdeNumber] IS NOT NULL");

🖥️ 5. Code Snippets & Examples

5.1 Typical Migration Commands

# Create the migration (Designer + Up/Down stubs)
dotnet ef migrations add AddNewAccountsTablesAndColumnsToJobTable

# Apply to database
dotnet ef database update

5.2 Example Up() & Down() Methods

Note: This resides in 20240711171136_AddNewAccountsTablesAndColumnsToJobTable.cs (non-designer)!

protected override void Up(MigrationBuilder migrationBuilder)
{
  migrationBuilder.CreateTable(
    name: "AccountBalances",
    schema: "irby",
    columns: table => new
    {
      AccountBalanceId = table.Column<Guid>(nullable: false),
      AccountingCode    = table.Column<string>(nullable: true),
      Cost              = table.Column<decimal>(type: "decimal(18,2)", nullable: false),
      // … other columns …
    },
    constraints: table => { table.PrimaryKey("PK_AccountBalances", x => x.AccountBalanceId); });

  migrationBuilder.CreateTable(
    name: "AccountLedger",
    schema: "irby",
    // … definitions …
  );

  // Add new columns to existing Job table
  migrationBuilder.AddColumn<decimal>(
    name: "BudgetRequested",
    schema: "irby",
    table: "Job",
    type: "decimal(18,2)",
    nullable: false,
    defaultValue: 0m);

  // … other columns …
}

protected override void Down(MigrationBuilder migrationBuilder)
{
  migrationBuilder.DropColumn(name: "BudgetRequested", schema:"irby", table:"Job");
  // … drop others …

  migrationBuilder.DropTable(name: "AccountLedger", schema: "irby");
  migrationBuilder.DropTable(name: "AccountBalances", schema: "irby");
}

5.3 BuildTargetModel Excerpt

modelBuilder.Entity("IrbyImaging.DomainClasses.AccountBalances", b => {
    b.Property<Guid>("AccountBalanceId")
        .ValueGeneratedOnAdd()
        .HasColumnType("uniqueidentifier");
    b.Property<decimal>("Cost")
        .HasColumnType("decimal(18,2)");
    // … all properties …
    b.HasKey("AccountBalanceId");
    b.ToTable("AccountBalances","irby");
});

🌳 6. UML Class Diagram (Mermaid)

classDiagram
    direction LR
    AccountBalances --> Job : JobNumber, JobName
    AccountLedger --> Job : JobNumber, JobName

    class AccountBalances {
      +Guid AccountBalanceId
      +string AccountingCode
      +decimal Cost
      +...           
    }

    class AccountLedger {
      +Guid AccountLedgerId
      +string AccountId
      +decimal Amount
      +DateTime BatchDate
      +...           
    }

    class Job {
      +Guid JobId
      +string Name
      +string JdeNumber
      +decimal BeginningBudget
      +decimal BudgetApproved
      +decimal BudgetRequested
      +...           
    }

🌟 Note: relationships are conceptual (no direct FK in this migration), but join by JobNumber in reporting.


▶️ 7. Usage & Execution

  1. Ensure your IrbyContext is registered in Startup.cs / Program.cs:
    services.AddDbContext<IrbyContext>(options =>
       options.UseSqlServer(Configuration["ConnectionStrings:IrbyDb"]));
    
  2. Run migrations on your dev database:
    dotnet ef database update
    
  3. Validate tables irby.AccountBalances and irby.AccountLedger exist.
  4. Query sample:
    var ledger = await context.Set<AccountLedger>()
        .Where(l => l.ReportingYear == DateTime.Now.Year)
        .OrderBy(l => l.BatchDate)
        .ToListAsync();
    

💡 8. Best Practices & Tips

  • Do not modify the Designer file directly.

  • Keep your Up() / Down() changes minimal and idempotent.

  • Use explicit column types (decimal(18,2)) to avoid precision surprises.

  • Back up production data before applying migrations.

  • Use EF Tools to script migrations for code reviews:

    dotnet ef migrations script --from 20240711171135_PreviousMigration --to 20240711171136_AddNewAccountsTablesAndColumnsToJobTable -o FinancialUpdate.sql
    

🎉 9. Conclusion

This EF Core migration Designer snapshot captures the introduction of two accounting‑focused tables, AccountBalances and AccountLedger, and enhances the Job entity with budget and contract columns. The BuildTargetModel method provides a complete blueprint of your domain model at this version, enabling EF Core to orchestrate database schema updates in a structured, reversible manner.

Happy coding! 🚀