π¦ Migration Documentation: 20250319224535_AddColumnsToAssetTable
File Path:
IrbyImaging.DbContext/Migrations/20250319224535_AddColumnsToAssetTable.cs
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Model Impact
- Migration Class Structure
- 5.1 UML Class Diagram
- 5.2 Code Snippet
- Up Method: Implementing Schema Changes
- Down Method: Rolling Back Changes
- Usage & Examples
- Best Practices & Tips
- 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
csprojincludes:<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:datetime2nullable:falsedefaultValue: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
- Add Migration
dotnet ef migrations add AddColumnsToAssetTable --project IrbyImaging.DbContext - Apply to Database
dotnet ef database update --project IrbyImaging.DbContext - 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
UpandDownpaths.
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