🗂️ Documentation: 20240201183732_UpdateEmployeeTableWithNewColumns2.cs

Location: IrbyImaging.DbContext/Migrations/20240201183732_UpdateEmployeeTableWithNewColumns2.cs
Purpose: EF Core Migration to add new columns to the JobAndFieldOfficeMapping table.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Migration Breakdown
  5. Data Model Impact
  6. Usage & Workflow
  7. Code Snippets & Examples
  8. UML Diagram
  9. Rollback Strategy
  10. FAQs
  11. Conclusion

1. Overview & Purpose

This EF Core Migration updates the schema of the JobAndFieldOfficeMapping table by adding four new string columns:

  • AssetsCount
  • EmployeeCount
  • JobCount
  • Lead

🚀 Why?
The application requires these additional data points on each mapping between jobs and field offices—enabling richer reporting and analytics.


2. Dependencies & Technologies

Technology / Package Version (example) Purpose
Microsoft.EntityFrameworkCore ≥ 6.0.0 Core EF ORM & migrations API
Microsoft.EntityFrameworkCore.Relational ≥ 6.0.0 Relational database features
.NET SDK ≥ 6.0 Target framework for the project

The migration file is auto-generated by EF Core’s CLI/tooling.


3. Key Functional Areas

  • Schema Evolution
    Managing incremental changes to the database without data loss.

  • Up/Down Pattern

    • Up(): Applies changes (adds columns).
    • Down(): Reverts changes (drops columns).
  • Idempotency & Versioning
    Each migration class has a unique timestamp in its filename, ensuring ordered schema updates.


4. Migration Breakdown

using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    public partial class UpdateEmployeeTableWithNewColumns2 : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<string>(
                name: "AssetsCount",
                table: "JobAndFieldOfficeMapping",
                type: "nvarchar(max)",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "EmployeeCount",
                table: "JobAndFieldOfficeMapping",
                type: "nvarchar(max)",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "JobCount",
                table: "JobAndFieldOfficeMapping",
                type: "nvarchar(max)",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "Lead",
                table: "JobAndFieldOfficeMapping",
                type: "nvarchar(max)",
                nullable: true);
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "AssetsCount",
                table: "JobAndFieldOfficeMapping");

            migrationBuilder.DropColumn(
                name: "EmployeeCount",
                table: "JobAndFieldOfficeMapping");

            migrationBuilder.DropColumn(
                name: "JobCount",
                table: "JobAndFieldOfficeMapping");

            migrationBuilder.DropColumn(
                name: "Lead",
                table: "JobAndFieldOfficeMapping");
        }
    }
}

4.1 Up Method

  • Purpose: Introduce new columns to store additional metadata.
  • Columns Added:
    Name Type Nullable Default
    AssetsCount nvarchar(max) Yes NULL
    EmployeeCount nvarchar(max) Yes NULL
    JobCount nvarchar(max) Yes NULL
    Lead nvarchar(max) Yes NULL

4.2 Down Method

  • Purpose: Fully revert the schema changes introduced in Up().
  • Drops Columns: AssetsCount, EmployeeCount, JobCount, Lead.

5. Data Model Impact

After applying this migration, the Entity representing JobAndFieldOfficeMapping should include:

public class JobAndFieldOfficeMapping
{
    public int Id { get; set; }
    // Existing properties...
    public string? AssetsCount { get; set; }
    public string? EmployeeCount { get; set; }
    public string? JobCount { get; set; }
    public string? Lead { get; set; }
}

⚠️ Note: You must update your EF Core DbContext model (if using fluent or attributes) or run dotnet ef dbcontext scaffold to sync.


6. Usage & Workflow

  1. Generate Migration
    dotnet ef migrations add UpdateEmployeeTableWithNewColumns2
    
  2. Apply to Database
    dotnet ef database update
    
  3. Rollback (if needed)
    dotnet ef database update <PreviousMigrationName>
    

7. Code Snippets & Examples

Adding a New Record

var mapping = new JobAndFieldOfficeMapping
{
    // ... existing props
    AssetsCount = "15",
    EmployeeCount = "200",
    JobCount = "50",
    Lead = "John Doe"
};

dbContext.JobAndFieldOfficeMappings.Add(mapping);
await dbContext.SaveChangesAsync();

Querying with New Columns

var reports = await dbContext.JobAndFieldOfficeMappings
    .Where(j => j.EmployeeCount != null)
    .Select(j => new {
        j.Id,
        j.Lead,
        j.EmployeeCount
    })
    .ToListAsync();

8. UML Diagram

classDiagram
    class JobAndFieldOfficeMapping {
        +int Id
        +string? AssetsCount
        +string? EmployeeCount
        +string? JobCount
        +string? Lead
        // ... other properties
    }

    class Migration {
        <<abstract>>
        +void Up(MigrationBuilder)
        +void Down(MigrationBuilder)
    }

    UpdateEmployeeTableWithNewColumns2 ..|> Migration : extends

9. Rollback Strategy 🔄

  • Revert Migration:
    dotnet ef database update 20240201183000_PreviousMigration
  • Revert Code:
    • Remove the properties from your model class.
    • Delete the migration file if it has not been applied elsewhere.

10. FAQs ❓

Q1: What happens to existing data?
A1: All existing rows keep their original data. New columns are initialized to NULL.

Q2: Can I set default values?
A2: Yes. Modify the migration to include .HasDefaultValue("0") or similar in the fluent API.


11. Conclusion 🎯

This migration is a straightforward extension of the JobAndFieldOfficeMapping schema, enabling richer business metrics. By following standard EF Core patterns, we ensure that schema changes are versioned, reversible, and integrated into the CI/CD pipeline.


End of Documentation