📚 Documentation: 20240306145546_AddColumnsToEquipment.cs

Part of the IrbyImaging.DbContext.Migrations namespace, this Entity Framework Core migration adds lifecycle-tracking columns to the Equipment table.


📑 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Changes
  5. Migration Breakdown
  6. Usage Examples
  7. UML Diagram
  8. Best Practices & Recommendations
  9. Conclusion

1. Overview & Purpose

This migration AddColumnsToEquipment introduces three new columns to the irby.Equipment table to better track the lifecycle and status of each equipment entry:

  • BeginDate : (DateTime?) — when the equipment became active/entered service
  • EndDate  : (DateTime?) — when the equipment was decommissioned or left service
  • CurrentStatus: (string) — a descriptor (e.g., “Active”, “Maintenance”, “Retired”)

By adding these fields, the system can:

  • Record service windows for equipment
  • Filter/query equipment by current status
  • Provide historical reporting on usage periods

2. Dependencies & Technologies

Technology / Package Purpose
Microsoft.EntityFrameworkCore Core ORM for .NET, powers migrations & DbContext
Microsoft.EntityFrameworkCore.Migrations Enables code-based migrations (Up/Down methods)
.NET 6+ (or compatible runtime) Target framework for the DbContext

Note: Ensure your project references the same EF Core version across all layers (DbContext, Tools, Design, etc.).


3. Key Functional Areas

  • Schema Evolution — Adds or removes columns without manual SQL
  • Up/Down Reversibility — Allows rolling forward/backward safely
  • Code-First Workflow — Defines DB changes alongside C# models

4. Data Model Changes

Before Migration

Column Name Type Nullable
Id int No
Name nvarchar Yes
SerialNumber nvarchar Yes
... ... ...

After Migration

Column Name Type Nullable Description
Id int No Primary Key
Name nvarchar Yes Equipment display name
SerialNumber nvarchar Yes Factory serial number
BeginDate datetime2 Yes When equipment entered service
EndDate datetime2 Yes When equipment left service (if any)
CurrentStatus nvarchar(max) Yes Status label (Active / Maintenance / etc.)

5. Migration Breakdown

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    /// <inheritdoc />
    public partial class AddColumnsToEquipment : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            // Add new columns in the 'irby' schema to 'Equipment'
            migrationBuilder.AddColumn<DateTime>(
                name: "BeginDate",
                schema: "irby",
                table: "Equipment",
                type: "datetime2",
                nullable: true);

            migrationBuilder.AddColumn<string>(
                name: "CurrentStatus",
                schema: "irby",
                table: "Equipment",
                type: "nvarchar(max)",
                nullable: true);

            migrationBuilder.AddColumn<DateTime>(
                name: "EndDate",
                schema: "irby",
                table: "Equipment",
                type: "datetime2",
                nullable: true);
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            // Rollback: remove the added columns
            migrationBuilder.DropColumn(
                name: "BeginDate",
                schema: "irby",
                table: "Equipment");

            migrationBuilder.DropColumn(
                name: "CurrentStatus",
                schema: "irby",
                table: "Equipment");

            migrationBuilder.DropColumn(
                name: "EndDate",
                schema: "irby",
                table: "Equipment");
        }
    }
}

↑ Up Method

  • AddColumn("BeginDate")
  • AddColumn("CurrentStatus")
  • AddColumn("EndDate")

Each call specifies:

  • schema: "irby"
  • table : "Equipment"
  • type : SQL type (datetime2, nvarchar(max))
  • nullable: allows nulls for existing records

↓ Down Method

  • DropColumn for each of the three fields in reverse order, ensuring safe rollback.

6. Usage Examples

  1. Add Migration

    dotnet ef migrations add AddColumnsToEquipment \
      --project IrbyImaging.DbContext \
      --startup-project IrbyImaging.WebApi
    
  2. Update Database

    dotnet ef database update \
      --project IrbyImaging.DbContext \
      --startup-project IrbyImaging.WebApi
    
  3. Query Examples

    // Fetch active equipment
    var activeEquipment = context.Equipment
        .Where(e => e.CurrentStatus == "Active")
        .ToList();
    
  4. Seeding Data

    modelBuilder.Entity<Equipment>()
        .HasData(new Equipment {
            Id = 1,
            Name = "MRI Scanner X1",
            BeginDate = DateTime.UtcNow,
            CurrentStatus = "Active"
        });
    

7. UML Diagram

+----------------------------------+
|        Migration (abstract)      |
+----------------------------------+
| + Up(MigrationBuilder)          |
| + Down(MigrationBuilder)        |
+----------------------------------+
           ▲
           |
+----------------------------------+
| AddColumnsToEquipment : Migration|
+----------------------------------+
|                                  |
+----------------------------------+
| + Up(...) : override             |
| + Down(...) : override           |
+----------------------------------+

8. Best Practices & Recommendations

  • 🌱 Nullability: New nullable columns avoid breaking existing data.
  • 🔄 Reversible Migrations: Always implement Down for safe rollbacks.
  • 🛠 Testing: Apply migrations on staging DB before production.
  • 📊 Data Backfill: If you need non-null defaults, consider a separate data migration step.
  • 🧹 Cleanup: Remove unused migrations periodically.

9. Conclusion

The AddColumnsToEquipment migration enriches the Equipment table with essential lifecycle-tracking fields. By following EF Core’s code-first approach—complete with Up/Down methods—you ensure that schema changes remain versioned, reversible, and in sync with your application code. This pattern not only simplifies deployments but also enforces a disciplined way to evolve your data model over time. 🚀


Generated documentation for: IrbyImaging.DbContext/Migrations/20240306145546_AddColumnsToEquipment.cs