πŸ“¦ Migration Documentation: 20250319224535_AddColumnsToAssetTable

File Path: IrbyImaging.DbContext/Migrations/20250319224535_AddColumnsToAssetTable.cs


πŸ“‹ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Model Impact
  5. Migration Class Structure
  6. Up Method: Implementing Schema Changes
  7. Down Method: Rolling Back Changes
  8. Usage & Examples
  9. Best Practices & Tips
  10. Conclusion

1. Overview & Purpose πŸš€

This Entity Framework Core migration adds three new columns to the existing Asset table in the "irby" schema:

  • ComplianceStatus (string): Tracks whether an asset meets organizational compliance rules.
  • LastCheckIn (DateTime): Records the timestamp of the last check-in from the asset/device.
  • PrimaryUPN (string): Stores the User Principal Name of the asset’s primary user.

Why?
Adding these columns enables enhanced tracking and reporting on asset health, compliance state, and user association.


2. Dependencies & Technologies πŸ› οΈ

  • .NET 6+ or .NET Core 3.1+
  • Microsoft.EntityFrameworkCore (EF Core)
  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.Relational

Ensure your csproj includes:

<PackageReference Include="Microsoft.EntityFrameworkCore" Version="6.0.0" />
<PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="6.0.0" />

3. Key Functional Areas πŸ”

  • Schema Evolution: Implements forward (Up) and backward (Down) migration logic.
  • Data Integrity: Sets default values for non-nullable columns to avoid breaking changes.
  • Modularity: Defined under the "irby" schema to keep domain objects organized.

4. Data Model Impact πŸ—ƒοΈ

Once applied, your C# Asset model (e.g., in IrbyImaging.DbContext/Models/Asset.cs) should include:

public class Asset
{
    public int Id { get; set; }
    public string? Name { get; set; }
    // New Properties:
    public string? ComplianceStatus { get; set; }
    public DateTime LastCheckIn { get; set; }
    public string? PrimaryUPN { get; set; }
}

5. Migration Class Structure πŸ—οΈ

5.1 UML Class Diagram

classDiagram
    class AddColumnsToAssetTable {
        <<Migration>>
        +void Up(MigrationBuilder builder)
        +void Down(MigrationBuilder builder)
    }
    class MigrationBuilder {
        +AddColumn<T>(...)
        +DropColumn(...)
    }
    AddColumnsToAssetTable ..> MigrationBuilder : uses

5.2 Code Snippet

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

            migrationBuilder.AddColumn<DateTime>(
                name: "LastCheckIn",
                schema: "irby",
                table: "Asset",
                type: "datetime2",
                nullable: false,
                defaultValue: new DateTime(1,1,1,0,0,0,DateTimeKind.Unspecified));

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

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(name: "ComplianceStatus", schema: "irby", table: "Asset");
            migrationBuilder.DropColumn(name: "LastCheckIn",    schema: "irby", table: "Asset");
            migrationBuilder.DropColumn(name: "PrimaryUPN",     schema: "irby", table: "Asset");
        }
    }
}

6. Up Method: Implementing Schema Changes πŸ”Ό

The Up method adds three columns to the Asset table:

6.1 Added Columns

  • ComplianceStatus:

    • type: nvarchar(max)
    • nullable: true
  • LastCheckIn:

    • type: datetime2
    • nullable: false
    • defaultValue: 1/1/0001 00:00:00 (Unspecified)
  • PrimaryUPN:

    • type: nvarchar(max)
    • nullable: true

6.2 Column Definitions Table

Column Name CLR Type SQL Type Nullable Default Value Purpose
ComplianceStatus string nvarchar(max) Yes NULL Asset compliance state
LastCheckIn DateTime datetime2 No 1/1/0001 00:00:00 Timestamp of last check-in event
PrimaryUPN string nvarchar(max) Yes NULL User Principal Name of primary owner

7. Down Method: Rolling Back Changes πŸ”„

The Down method reverses the schema updates. It drops the three columns added by Up:

migrationBuilder.DropColumn("ComplianceStatus", schema: "irby", table: "Asset");
migrationBuilder.DropColumn("LastCheckIn",    schema: "irby", table: "Asset");
migrationBuilder.DropColumn("PrimaryUPN",     schema: "irby", table: "Asset");

8. Usage & Examples πŸ“˜

8.1 Generating & Applying Migrations

  1. Add Migration
    dotnet ef migrations add AddColumnsToAssetTable --project IrbyImaging.DbContext
    
  2. Apply to Database
    dotnet ef database update --project IrbyImaging.DbContext
    
  3. Verify Schema
    SELECT TOP 5 ComplianceStatus, LastCheckIn, PrimaryUPN
    FROM irby.Asset;
    

8.2 Accessing New Properties in Code

using var context = new IrbyContext();

// Create new Asset
var asset = new Asset
{
    Name = "Printer-42",
    ComplianceStatus = "Pending",
    LastCheckIn = DateTime.UtcNow,
    PrimaryUPN = "[email protected]"
};

context.Assets.Add(asset);
context.SaveChanges();

// Query assets that haven’t checked in for >7 days
var staleAssets = context.Assets
    .Where(a => a.LastCheckIn < DateTime.UtcNow.AddDays(-7))
    .ToList();

9. Best Practices & Tips βœ…

  • Default Values: Always specify a safe default for non-nullable columns to prevent deployment errors on existing data.
  • Schema Versioning: Name migrations clearly (yyyyMMddHHmmss_Description) to track history.
  • Backup First: Before applying to production, snapshot your database.
  • Test Migrations: Use a local/dev environment to validate both Up and Down paths.

10. Conclusion 🎯

This migration extends the Asset table to capture critical compliance and activity data. By following the patterns outlined aboveβ€”clear naming, default values, and robust rollbackβ€”you’ll maintain a healthy schema evolution process. Happy coding! πŸš€


End of Documentation