📚 Documentation: AddWorkDateToEquipmentEntry Migration


Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Migration Class Structure
  5. How to Apply & Roll Back the Migration
  6. Integration with Entity Framework Core
  7. UML Diagram
  8. Sample Usage & Code Snippets
  9. Best Practices & Considerations
  10. Conclusion

1. Overview & Purpose

🚀 Purpose
The 20250310024708_AddWorkDateToEquipmentEntry migration extends the existing EquipmentEntry table by adding a new non-nullable column:

  • WorkDate (datetime2): Represents the date (and optionally time) when a piece of equipment was worked on.

📝 Why?

  • Auditing & Reporting: Track when maintenance or inspections occurred.
  • Business Logic: Enables features like “List all equipment serviced within a given date range.”
  • Data Integrity: A non-nullable WorkDate ensures every entry has an associated date.

2. Dependencies & Technologies

Technology / Package Version (Example) Purpose
Microsoft.EntityFrameworkCore 7.0.0+ Core ORM for .NET, provides MigrationBuilder.
.NET 6 / .NET 7 6.0 / 7.0 Runtime for executing migrations and application.
SQL Server (or any provider) — Database engine where datetime2 type is supported.

3. Key Functional Areas

  1. Schema Evolution
    • Adds a new column to an existing table without data loss.
  2. Default Values
    • Sets default to DateTime.MinValue (0001-01-01) for existing rows.
  3. Reversibility
    • Provides a Down() method to cleanly remove the column if needed.

4. Migration Class Structure

using System;
using Microsoft.EntityFrameworkCore.Migrations;

#nullable disable

namespace IrbyImaging.DbContext.Migrations
{
    /// <inheritdoc />
    public partial class AddWorkDateToEquipmentEntry : Migration
    {
        /// <inheritdoc />
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.AddColumn<DateTime>(
                name: "WorkDate",
                schema: "irby",
                table: "EquipmentEntry",
                type: "datetime2",
                nullable: false,
                defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
        }

        /// <inheritdoc />
        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropColumn(
                name: "WorkDate",
                schema: "irby",
                table: "EquipmentEntry");
        }
    }
}

4.1 Up() Method

Parameter Description
migrationBuilder Fluent builder to define schema changes.
AddColumn<DateTime> Adds a new column of CLR type DateTime.
schema: "irby" Namespacing all tables under the irby schema.
nullable: false Column cannot contain NULL; enforces data integrity.
defaultValue Existing rows get 0001-01-01T00:00:00 (CLR’s DateTime.MinValue).

What It Does

  • Alters the EquipmentEntry table to include a WorkDate column.
  • Uses SQL type datetime2 to capture both date & time at high precision.
  • Ensures no row is left without a date by providing a default.

4.2 Down() Method

Parameter Description
migrationBuilder Fluent builder to undo schema changes.
DropColumn(...) Removes the previously added WorkDate.

What It Does

  • Safely rolls back the schema change by dropping the WorkDate column.
  • Ensures migrations remain reversible, aiding in CI/CD pipelines and testing.

5. How to Apply & Roll Back the Migration

Applying the Migration

# Generate SQL script
dotnet ef migrations script 20250310024708_AddWorkDateToEquipmentEntry

# Apply directly to the database
dotnet ef database update AddWorkDateToEquipmentEntry

Rolling Back (Downgrading)

# Roll back one migration
dotnet ef database update LastGoodMigrationName

6. Integration with Entity Framework Core

  1. Registering DbContext
    Ensure your Startup.cs or Program.cs includes:

    services.AddDbContext<IrbyDbContext>(options =>
        options.UseSqlServer(configuration.GetConnectionString("IrbyDatabase")));
    
  2. Applying Migrations on Startup

    using (var scope = app.Services.CreateScope())
    {
        var db = scope.ServiceProvider.GetRequiredService<IrbyDbContext>();
        db.Database.Migrate(); // Applies pending migrations
    }
    
  3. Entity Class Update
    Add public DateTime WorkDate { get; set; } to your EquipmentEntry POCO:

    public class EquipmentEntry
    {
        public int Id { get; set; }
        public string SerialNumber { get; set; }
        // ... other props
        public DateTime WorkDate { get; set; }  // ➕ Newly added
    }
    

7. UML Diagram

classDiagram
    class EquipmentEntry {
        +int Id
        +string SerialNumber
        +DateTime CreatedOn
        +DateTime WorkDate    <<added in 20250310024708>>
        // ... other properties
    }

    class Migration {
        <<abstract>>
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }

    class AddWorkDateToEquipmentEntry {
        +Up(MigrationBuilder)
        +Down(MigrationBuilder)
    }

    Migration <|-- AddWorkDateToEquipmentEntry

8. Sample Usage & Code Snippets

Querying by WorkDate

// Fetch all equipment entries worked on in the last 7 days
var oneWeekAgo = DateTime.UtcNow.AddDays(-7);
var recentWork = dbContext.EquipmentEntries
    .Where(e => e.WorkDate >= oneWeekAgo)
    .ToList();

Seeding Default Entries

modelBuilder.Entity<EquipmentEntry>().HasData(
    new EquipmentEntry
    {
        Id = 1,
        SerialNumber = "EQ123",
        WorkDate = new DateTime(2025, 3, 10)
    }
);

9. Best Practices & Considerations

  • Default Values: If DateTime.MinValue is semantically invalid, consider migrating existing rows to a more meaningful default via a one‑time SQL update before enforcing NOT NULL.
  • Time Zones: Store all dates in UTC or use DateTimeOffset if local time is critical.
  • Backfilling Data: After adding the column, run a data migration to backfill real work dates for historical records.
  • Testing: Always confirm migrations on a staging database before production rollout.

10. Conclusion

✅ Key Takeaways

  • The AddWorkDateToEquipmentEntry migration cleanly adds a crucial WorkDate column.
  • It follows EF Core best practices: reversible, schema‑scoped, and non‑disruptive.
  • Integrate the change by updating your EquipmentEntry model and running dotnet ef database update.

With these steps, you’ll be able to track and query equipment maintenance dates reliably, enhancing reporting and operational oversight. 🎉