📋 Documentation: AddColumnsToJobAndFieldOfficeTables.Designer.cs

Location: IrbyImaging.DbContext/Migrations/20240124150448_AddColumnsToJobAndFieldOfficeTables.Designer.cs


📑 Table of Contents

  1. Overview & Purpose
  2. Migration Context
  3. Key Changes Introduced 🛠️
  4. Entity Mapping & Data Models
  5. Usage & Applying the Migration 🚀
  6. UML Diagram 🖼️
  7. Conclusion 🎯

🔍 Overview & Purpose

This auto-generated designer file is part of an Entity Framework Core (EF Core) migration named:

20240124150448_AddColumnsToJobAndFieldOfficeTables

It contains the BuildTargetModel method, which EF Core uses to describe the shape of your database schema after this migration runs. In particular, this migration introduces new columns to the Job and FieldOffice tables to support JDE integration and Office‑Job relationships.


🔧 Migration Context

  • EF Core Version: 7.0.7
  • Database Provider: Microsoft SQL Server (uses identity columns)
  • DbContext: IrbyContext
  • Migration Attribute:
    [Migration("20240124150448_AddColumnsToJobAndFieldOfficeTables")]
    [DbContext(typeof(IrbyContext))]
    partial class AddColumnsToJobAndFieldOfficeTables { … }
    
  • Purpose: Extend the Job and FieldOffice tables with:
    • JdeNumber: A JV‑derived number for ERP integration
    • FieldOfficeId: A FK in Job linking to FieldOffice

🛠️ Key Changes Introduced

Entity Column SQL Type Nullable Indexed Unique Constraint
Job JdeNumber nvarchar(450) Yes Yes (unique) Yes (filter [JdeNumber] IS NOT NULL)
Job FieldOfficeId uniqueidentifier Yes Yes No
FieldOffice JdeNumber nvarchar(450) Yes Yes (unique) Yes (filter [JdeNumber] IS NOT NULL)

🏗️ Entity Mapping & Data Models

Job Entity

modelBuilder.Entity("IrbyImaging.DomainClasses.Job", b => {
    b.Property<Guid>("JobId")
     .ValueGeneratedOnAdd()
     .HasColumnType("uniqueidentifier");

    b.Property<Guid?>("FieldOfficeId")
     .HasColumnType("uniqueidentifier");    // ← New FK

    b.Property<string>("JdeNumber")
     .HasColumnType("nvarchar(450)");      // ← New ERP number

    b.Property<string>("Name")
     .HasColumnType("nvarchar(max)");

    b.HasKey("JobId");
    b.HasIndex("FieldOfficeId");           // New index
    b.HasIndex("JdeNumber")
     .IsUnique()
     .HasFilter("[JdeNumber] IS NOT NULL");

    b.ToTable("Job", "irby");
});
  • Primary Key: JobId (uniqueidentifier)
  • New Columns:
    • JdeNumber — string up to 450 chars
    • FieldOfficeId — nullable GUID FK to FieldOffice
  • Indexes:
    • One on FieldOfficeId
    • Unique index on JdeNumber

FieldOffice Entity

modelBuilder.Entity("IrbyImaging.DomainClasses.FieldOffice", b => {
    b.Property<Guid>("Id")
     .ValueGeneratedOnAdd()
     .HasColumnType("uniqueidentifier");

    b.Property<string>("JdeNumber")
     .HasColumnType("nvarchar(450)");      // ← New ERP number

    // … other properties …

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

    b.ToTable("FieldOffice", "irby");
});
  • Primary Key: Id
  • New Column: JdeNumber — unique ERP identifier
  • Unique Index on JdeNumber (non-null entries)

🔗 Relationships & Indexes

  • Job → FieldOffice
    • ForeignKey: Job.FieldOfficeId
    • Navigation:
      b.HasOne("IrbyImaging.DomainClasses.FieldOffice", "FieldOffice")
       .WithMany("Jobs")
       .HasForeignKey("FieldOfficeId");
      
  • Indexing Strategy:
    • JdeNumber in both entities to accelerate lookups by ERP number
    • FieldOfficeId on Job to optimize JOINs

🚀 Usage & Applying the Migration

  1. Add Migration (already done):
    dotnet ef migrations add AddColumnsToJobAndFieldOfficeTables
    
  2. Update Database:
    dotnet ef database update 20240124150448_AddColumnsToJobAndFieldOfficeTables
    
  3. Verify
    • Check Job & FieldOffice tables for new columns
    • Ensure indexes exist (via SSMS or CLI)

🖼️ UML Diagram

@startuml
class FieldOffice {
  +Guid Id
  +string JdeNumber
  -- (other properties)
}

class Job {
  +Guid JobId
  +string JdeNumber
  +Guid? FieldOfficeId
  +string Name
}

FieldOffice "1" -- "0..*" Job : Jobs
Job --> FieldOffice : FieldOfficeId
@enduml

Diagram shows the 1‑to‑many relationship between FieldOffice and Job, with ERP‑specific columns.


🎯 Conclusion

This migration designer file is central to EF Core’s snapshot of how your Job and FieldOffice tables should look after introducing ERP integration fields. By adding:

  • JdeNumber (unique, indexed strings for ERP IDs)
  • FieldOfficeId (an FK from Job → FieldOffice)

you enable seamless data linking between your internal domain and external JD Edwards systems.

Keep this file under version control, but remember not to modify it by hand—EF Core will regenerate it if you change your domain classes or add further migrations.