π¦ IrbyImaging.DbContext Migration
File:
Migrations/20220207182630_Migrations.cs
Author: kvamshi
Created: 02β09β2022
Last Modified: 02β09β2022
π Table of Contents
- Overview & Purpose
- Key Functional Areas
- Dependencies & Technologies
- Data Models & Schemas
- Migration Methods
- Constraints & Indexes
- Entity Relationship Diagram (ERD)
- Usage Examples
- 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
irbyschema - 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
irbyschema 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
- Add a new migration (if you modify the model later):
dotnet ef migrations add 20220207182630_Migrations - 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!