πŸ“¦ IrbyImaging.DbContext Migration

File: Migrations/20220207182630_Migrations.cs
Author: kvamshi
Created: 02‑09‑2022
Last Modified: 02‑09‑2022


πŸ“‘ Table of Contents

  1. Overview & Purpose
  2. Key Functional Areas
  3. Dependencies & Technologies
  4. Data Models & Schemas
  5. Migration Methods
  6. Constraints & Indexes
  7. Entity Relationship Diagram (ERD)
  8. Usage Examples
  9. Conclusion

1. Overview & Purpose πŸš€

This class is an EntityΒ Framework Core migration that defines the initial database structure for the IrbyImaging application. It:

  • Ensures the presence of the irby schema
  • Creates core tables (Assets, Employees, Jobs, etc.)
  • Defines columns, primary/foreign keys, and indexes
  • Provides an Up method to apply changes
  • Provides a Down method to roll them back

Why migrations?
EF Core migrations let you version-control your database schema alongside your code.


2. Key Functional Areas βš™οΈ

  • Schema Management
  • Table Creation
  • Primary & Foreign Key Constraints
  • Index Definitions
  • Up/Down Rollback Support

3. Dependencies & Technologies πŸ› οΈ

Technology Version / Notes
Microsoft.EntityFrameworkCore Migration API (Migration, MigrationBuilder)
Microsoft.EntityFrameworkCore.SqlServer SQL Server provider
.NET (Core/5/6) Target platform
SQL Server Database engine (schema, types)

4. Data Models & Schemas πŸ—‚οΈ

All tables (except AssetModelDetail) are created under the irby schema.
AssetModelDetail has no schema specified (defaults to dbo).

4.1 Tables Overview

Table Name Schema Description
AssetHistory irby Audit trail for asset status changes
AssetManufacturer irby Lookup of asset manufacturers
AssetModel irby Models produced by manufacturers
AssetModelDetail dbo Extended model metadata
OperatingSystem irby Supported operating systems
Job irby JDE job codes
Employee irby Employee directory
FieldOffice irby Field office locations
Asset irby Central asset registry
CovidTestData irby COVID‑19 test records
DocumentImage irby Scanned document storage
IPMMigration irby Migration staging for IPM
TempDocumentData irby Temporary document staging
ODS_Jobs_Staging irby Staging for ODS Job data
ODS_Employee_Staging irby Staging for ODS Employee data

4.2 Sample Table Definition: Asset

migrationBuilder.CreateTable(
    name: "Asset",
    schema: "irby",
    columns: table => new {
        Id                = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
        Category          = table.Column<string>(type: "nvarchar(max)", nullable: false),
        PurchaseDate      = table.Column<DateTimeOffset>(type: "datetimeoffset", nullable: false),
        Status            = table.Column<string>(type: "nvarchar(max)", nullable: false),
        ModelId           = table.Column<int>(type: "int", nullable: false),
        OwnerId           = table.Column<Guid>(type: "uniqueidentifier", nullable: true),
        FieldOfficeId     = table.Column<Guid>(type: "uniqueidentifier", nullable: true),
        SerialNumber      = table.Column<string>(type: "nvarchar(max)", nullable: true),
        … plus TPH discriminator columns for Phone, MobileDevice, etc.
    },
    constraints: table => {
        table.PrimaryKey("PK_Asset", x => x.Id);
        table.ForeignKey(
            name: "FK_Asset_AssetModel_ModelId",
            column: x => x.ModelId,
            principalSchema: "irby",
            principalTable: "AssetModel",
            principalColumn: "Id",
            onDelete: ReferentialAction.Cascade);
        // … other FKs: Employee (Owner), FieldOffice, OperatingSystem
    });

5. Migration Methods πŸ”„

Up Method

  • EnsureSchema: Creates irby schema if missing
  • CreateTable: Defines each table and its columns
  • PrimaryKey & ForeignKey: Establishes constraints
  • CreateIndex: Improves lookup performance
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.EnsureSchema(name: "irby");
    // … CreateTable calls for AssetHistory, AssetManufacturer, …
    migrationBuilder.CreateTable(
        name: "Asset",
        schema: "irby",
        columns: table => new { … },
        constraints: table => { … });
    // … Index definitions
}

Down Method

Reverses Up by dropping tables in reverse order to avoid FK conflicts.

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(name: "Asset",    schema: "irby");
    migrationBuilder.DropTable(name: "AssetHistory", schema: "irby");
    // … rest of tables
}

6. Constraints & Indexes 🏷️

Constraint / Index Table Type Notes
PK_Asset Asset Primary on Id
FK_Asset_AssetModel Asset β†’ AssetModel Foreign Cascade on delete
IX_Asset_Imei (unique) Asset Unique Filter Imei IS NOT NULL
IX_Employee_JdeNumber (unique) Employee Unique Filter JdeNumber IS NOT NULL
IX_FieldOffice_JdeNumber (unique) FieldOffice Unique Filter JdeNumber IS NOT NULL
IX_Job_JdeNumber (unique) Job Unique Filter JdeNumber IS NOT NULL
FK_Employee_CurrentJobId Employee β†’ Job Foreign Cascade on delete
FK_FieldOffice_ManagerId FieldOffice β†’ Employee Foreign Cascade on delete
… plus many others for AssetModelDetail, ODS staging

7. Entity Relationship Diagram (ERD) πŸ“Š

erDiagram
    ASSET_HISTORY      }o--|| ASSET          : tracks
    ASSET_MANUFACTURER ||--o{ ASSET_MODEL   : produces
    ASSET_MODEL        ||--o{ ASSET         : defines
    ASSET_MODEL        ||--o{ ASSET_MODEL_DETAIL : details
    OPERATING_SYSTEM   ||--o{ ASSET         : supports
    JOB                ||--o{ EMPLOYEE      : assigns
    EMPLOYEE           ||--o{ FIELD_OFFICE  : manages
    FIELD_OFFICE       ||--o{ ASSET         : locates
    ODS_JOBS_STAGING   ||--o{ ODS_EMPLOYEE_STAGING : joins

8. Usage Examples πŸ“‹

Applying the Migration

  1. Add a new migration (if you modify the model later):
    dotnet ef migrations add 20220207182630_Migrations
    
  2. Update database:
    dotnet ef database update
    

Rolling Back

To revert to a previous migration (for example, remove all):

dotnet ef database update 0

9. Conclusion βœ…

This EF Core migration provides a comprehensive starting schema for the IrbyImaging application, covering:

  • Asset management
  • Employee & organizational data
  • Document & test data staging

By versioning your database with migrations, you gain:

  • Safe, repeatable deployments
  • Schema evolution tracking
  • Automatic rollback capability

Feel free to extend this migration step-by-step as your domain model grows!