πŸ“‘ Migration Documentation: 20231219153038_ApprovalRequired


πŸ“‹ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Schema Changes
  5. Data Models & UML Diagram
  6. Migration Methods
  7. Entity Configuration & Usage Examples
  8. EF Core Commands
  9. Conclusion

1. Overview & Purpose

πŸš€ Migration Name: ApprovalRequired
πŸ•’ Timestamp: 2023-12-19 15:30:38
πŸ“¦ Namespace: IrbyImaging.DbContext.Migrations

This Entity Framework Core migration introduces:

  • A new boolean flag (ApprovalRequired) to the existing irby.RequestOption table
  • Three new tables for capturing lease‐related cost data:
    • DirectorLeaseInfo
    • LeasePropertyDetails
    • StatePropertyCosts

Intended Use

  • ApprovalRequired: Enforces an approval workflow on request options.
  • Lease Cost Tables: Structure monthly cost details for directors, leases and states, enabling richer reporting and analytics within the Irby Imaging application.

2. Dependencies & Technologies

Technology Version / Type Purpose
.NET / C# 6.x / 7.x Application runtime & language
Entity Framework Core 6.x / 7.x ORM & Migrations
SQL Server T‑SQL (bit, decimal) Relational database engine
dotnet CLI Latest EF Core tooling (migrations, update)
Visual Studio / VS Code β€” Development environment

3. Key Functional Areas

  • Up(MigrationBuilder)
    Defines forward schema changes (add column & create tables).

  • Down(MigrationBuilder)
    Defines rollback operations (drop tables & remove column).

  • Schema
    All changes target the irby schema (except new tables which default to the default schema if none is specified).


4. Schema Changes

4.1 Add Column: ApprovalRequired

Table Schema Column Type Nullable Default
RequestOption irby ApprovalRequired bit No false

Why?
This flag enables the application to mark whether a specific request option needs managerial or automated approval before proceeding.


4.2 New Tables Overview

Table Columns
DirectorLeaseInfo Director (nvarchar(max)), NumberOfProperties (int?), MonthlyAVTax (decimal(18,2)?), MonthlyBaseRent (decimal(18,2)?),MonthlyInsurancePayment (decimal(18,2)?), MonthlySalesTax (decimal(18,2)?), MonthlyTotalCost (decimal(18,2)?)
LeasePropertyDetails LeasePurpose (nvarchar(max)), NumberOfProperties (int?), MonthlyAVTax (decimal(18,2)?), MonthlyBaseRent (decimal(18,2)?),MonthlyInsurancePayment (decimal(18,2)?), MonthlySalesTax (decimal(18,2)?), MonthlyTotalCost (decimal(18,2)?)
StatePropertyCosts State (nvarchar(max)), MonthlyTotal (decimal(18,2))

Note:
All three tables are created without primary keys, suggesting they may be used as keyless entity types (e.g., views, reporting tables).


5. Data Models & UML Diagram

Below is a recommended C# model mapping along with a PlantUML diagram.

5.1 C# Entity Models

using Microsoft.EntityFrameworkCore;

[Keyless]
public class DirectorLeaseInfo
{
    public string? Director { get; set; }
    public int? NumberOfProperties { get; set; }
    public decimal? MonthlyAVTax { get; set; }
    public decimal? MonthlyBaseRent { get; set; }
    public decimal? MonthlyInsurancePayment { get; set; }
    public decimal? MonthlySalesTax { get; set; }
    public decimal? MonthlyTotalCost { get; set; }
}

[Keyless]
public class LeasePropertyDetails
{
    public string? LeasePurpose { get; set; }
    public int? NumberOfProperties { get; set; }
    public decimal? MonthlyAVTax { get; set; }
    public decimal? MonthlyBaseRent { get; set; }
    public decimal? MonthlyInsurancePayment { get; set; }
    public decimal? MonthlySalesTax { get; set; }
    public decimal? MonthlyTotalCost { get; set; }
}

[Keyless]
public class StatePropertyCosts
{
    public string? State { get; set; }
    public decimal MonthlyTotal { get; set; }
}

5.2 UML Diagram (PlantUML)

@startuml
package "irby" {
  entity RequestOption {
    + bool ApprovalRequired
    -- existing columns --
  }

  entity DirectorLeaseInfo <<Keyless>> {
    + string Director
    + int? NumberOfProperties
    + decimal? MonthlyAVTax
    + decimal? MonthlyBaseRent
    + decimal? MonthlyInsurancePayment
    + decimal? MonthlySalesTax
    + decimal? MonthlyTotalCost
  }

  entity LeasePropertyDetails <<Keyless>> {
    + string LeasePurpose
    + int? NumberOfProperties
    + decimal? MonthlyAVTax
    + decimal? MonthlyBaseRent
    + decimal? MonthlyInsurancePayment
    + decimal? MonthlySalesTax
    + decimal? MonthlyTotalCost
  }

  entity StatePropertyCosts <<Keyless>> {
    + string State
    + decimal MonthlyTotal
  }
}
@enduml

6. Migration Methods

6.1 Up Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    // βž• Add ApprovalRequired flag
    migrationBuilder.AddColumn<bool>(
        name: "ApprovalRequired",
        schema: "irby",
        table: "RequestOption",
        type: "bit",
        nullable: false,
        defaultValue: false);

    // βž• Create DirectorLeaseInfo
    migrationBuilder.CreateTable(
        name: "DirectorLeaseInfo",
        columns: table => new
        {
            Director                 = table.Column<string>(type: "nvarchar(max)", nullable: true),
            NumberOfProperties       = table.Column<int>(type: "int", nullable: true),
            MonthlyAVTax             = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyBaseRent          = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyInsurancePayment  = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlySalesTax          = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyTotalCost         = table.Column<decimal>(type: "decimal(18,2)", nullable: true)
        },
        constraints: table => { });

    // βž• Create LeasePropertyDetails
    migrationBuilder.CreateTable(
        name: "LeasePropertyDetails",
        columns: table => new
        {
            LeasePurpose             = table.Column<string>(type: "nvarchar(max)", nullable: true),
            NumberOfProperties       = table.Column<int>(type: "int", nullable: true),
            MonthlyAVTax             = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyBaseRent          = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyInsurancePayment  = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlySalesTax          = table.Column<decimal>(type: "decimal(18,2)", nullable: true),
            MonthlyTotalCost         = table.Column<decimal>(type: "decimal(18,2)", nullable: true)
        },
        constraints: table => { });

    // βž• Create StatePropertyCosts
    migrationBuilder.CreateTable(
        name: "StatePropertyCosts",
        columns: table => new
        {
            State        = table.Column<string>(type: "nvarchar(max)", nullable: true),
            MonthlyTotal = table.Column<decimal>(type: "decimal(18,2)", nullable: false)
        },
        constraints: table => { });
}

6.2 Down Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    // βž– Drop created tables
    migrationBuilder.DropTable(name: "DirectorLeaseInfo");
    migrationBuilder.DropTable(name: "LeasePropertyDetails");
    migrationBuilder.DropTable(name: "StatePropertyCosts");

    // βž– Remove column ApprovalRequired
    migrationBuilder.DropColumn(
        name: "ApprovalRequired",
        schema: "irby",
        table: "RequestOption");
}

7. Entity Configuration & Usage Examples

7.1 Configuring Keyless Entities in DbContext

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Map to tables
    modelBuilder.Entity<DirectorLeaseInfo>()
        .HasNoKey()
        .ToTable("DirectorLeaseInfo");

    modelBuilder.Entity<LeasePropertyDetails>()
        .HasNoKey()
        .ToTable("LeasePropertyDetails");

    modelBuilder.Entity<StatePropertyCosts>()
        .HasNoKey()
        .ToTable("StatePropertyCosts");

    // Optionally configure the new column on RequestOption
    modelBuilder.Entity<RequestOption>()
        .Property(r => r.ApprovalRequired)
        .HasDefaultValue(false)
        .IsRequired();
}

7.2 Sample Queries

// 1️⃣ Check if a RequestOption needs approval
var option = await context.RequestOptions.FindAsync(id);
if (option.ApprovalRequired)
{
    // trigger approval workflow...
}

// 2️⃣ Retrieve all director lease infos
var directorInfos = await context.Set<DirectorLeaseInfo>().ToListAsync();

// 3️⃣ Compute total monthly cost by state
var stateCosts = await context.Set<StatePropertyCosts>()
    .Where(s => s.MonthlyTotal > 10000)
    .ToListAsync();

8. EF Core Commands

Command Description
dotnet ef migrations add Name Scaffold a new migration
dotnet ef migrations remove Remove the last migration
dotnet ef database update Apply pending migrations to the database
dotnet ef migrations script Generate SQL script for migrations
Update-Database (PMC) Apply migrations in Visual Studio PM Console

9. Conclusion

πŸŽ‰ The 20231219153038_ApprovalRequired migration equips your database with:

  • Approval workflows on request options
  • Comprehensive lease‑cost tracking via three new reporting tables

Next Steps

  1. Apply the migration (dotnet ef database update).
  2. Implement business logic in your service layer to leverage ApprovalRequired.
  3. Populate the new tables and build dashboards or reports as needed.

Enjoy stronger data governance and richer reporting in your Irby Imaging solution! πŸš€


Document generated for IrbyImaging.DbContext/Migrations/20231219153038_ApprovalRequired.cs – Detailed EF Core migration guide.