📑 IrbyImaging.DbContext.Migrations.UpdateCrewTables Migration Documentation


📋 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Data Models Affected
  4. Detailed Migration Analysis
  5. Database Impact & Best Practices
  6. Usage Examples
  7. UML Diagram
  8. Error Handling & Return Types
  9. Third‑Party Integrations & Utilities
  10. Conclusion

1. Overview & Purpose

✨ UpdateCrewTables is an Entity Framework Core migration that evolves the Crew and CrewEmployee tables in the "irby" schema:

  • Standardizes the CreatedDate in CrewEmployee from string to DateTime.
  • Introduces audit and soft-delete columns (CreatedBy, CreatedDate, IsDeleted) into the Crew table.

Why?

  • Ensure strongly‑typed dates instead of text-based storage.
  • Support audit trails (who created a record, when).
  • Enable soft deletion of crews without physical data loss.

2. Dependencies & Technologies

Technology Version / Notes
.NET / C# .NET 6+ / C# 10+
Microsoft.EntityFrameworkCore EF Core Migrations API
Database SQL Server (datetime2, bit)
Tooling dotnet ef CLI, Visual Studio

3. Data Models Affected

Below are the Entity classes (simplified) that correspond to the modified tables:

namespace IrbyImaging.Domain.Models
{
    public class Crew
    {
        public int Id { get; set; }
        public string Name { get; set; }
        // New columns added by this migration:
        public string? CreatedBy { get; set; }
        public DateTime CreatedDate { get; set; }
        public bool IsDeleted { get; set; }
    }

    public class CrewEmployee
    {
        public int Id { get; set; }
        public int CrewId { get; set; }
        // Altered column type:
        public DateTime CreatedDate { get; set; }
    }
}

4. Detailed Migration Analysis

4.1 Migration Class Structure

namespace IrbyImaging.DbContext.Migrations
{
    public partial class UpdateCrewTables : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder) { /* … */ }
        protected override void Down(MigrationBuilder migrationBuilder) { /* … */ }
    }
}
  • Up method: Defines schema changes to apply.
  • Down method: Reverts those changes in case of rollback.

4.2 Up Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Alter existing column type on CrewEmployee
    migrationBuilder.AlterColumn<DateTime>(
        name: "CreatedDate",
        schema: "irby",
        table: "CrewEmployee",
        type: "datetime2",
        nullable: false,
        defaultValue: new DateTime(1,1,1),
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true);

    // 2️⃣ Add audit fields to Crew
    migrationBuilder.AddColumn<string>(
        name: "CreatedBy",
        schema: "irby",
        table: "Crew",
        type: "nvarchar(max)",
        nullable: true);

    migrationBuilder.AddColumn<DateTime>(
        name: "CreatedDate",
        schema: "irby",
        table: "Crew",
        type: "datetime2",
        nullable: false,
        defaultValue: new DateTime(1,1,1));

    // 3️⃣ Add soft-delete field to Crew
    migrationBuilder.AddColumn<bool>(
        name: "IsDeleted",
        schema: "irby",
        table: "Crew",
        type: "bit",
        nullable: false,
        defaultValue: false);
}
  • AlterColumn

    • Converts CreatedDate from string (nvarchar(max)) to DateTime (datetime2)
    • Ensures non-nullable with a default of 0001-01-01T00:00:00.
  • AddColumn

    • CreatedBy (string?): nullable audit field.
    • CreatedDate (DateTime): non-nullable with default.
    • IsDeleted (bool): non-nullable, defaulting to false (soft-delete flag).

4.3 Down Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    // Remove added columns
    migrationBuilder.DropColumn(name: "CreatedBy", schema: "irby", table: "Crew");
    migrationBuilder.DropColumn(name: "CreatedDate", schema: "irby", table: "Crew");
    migrationBuilder.DropColumn(name: "IsDeleted", schema: "irby", table: "Crew");

    // Revert column type on CrewEmployee
    migrationBuilder.AlterColumn<string>(
        name: "CreatedDate",
        schema: "irby",
        table: "CrewEmployee",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(DateTime),
        oldType: "datetime2");
}
  • DropColumn: Cleanly removes newly added fields.
  • AlterColumn: Restores CrewEmployee.CreatedDate back to its original string type.

4.4 Summary of Changes

Operation Table Column From Type To Type Default Value
AlterColumn CrewEmployee CreatedDate nvarchar(max)? datetime2 ! 0001-01-01T00:00:00
AddColumn Crew CreatedBy — nvarchar(max)? null
AddColumn Crew CreatedDate — datetime2 ! 0001-01-01T00:00:00
AddColumn Crew IsDeleted — bit ! false

Legend:
– \? means nullable
– ! means non-nullable


5. Database Impact & Best Practices

  • Atomicity: EF Core wraps Up/Down operations in a single transaction by default.
  • Downtime: Usually minimal for simple schema changes; verify no long-running locks.
  • Backups: Always backup production DB before applying migrations.
  • Data Migration: Converting a string to DateTime can fail if existing data is invalid—ensure data cleanup or a custom migration step if needed.

6. Usage Examples

6.1 Adding the Migration

# From the project root that contains the DbContext
dotnet ef migrations add UpdateCrewTables \
  --project IrbyImaging.DbContext \
  --output-dir Migrations

6.2 Applying the Migration

dotnet ef database update \
  --project IrbyImaging.DbContext \
  --context IrbyDbContext

6.3 Rolling Back the Migration

# Revert one step (to the previous migration)
dotnet ef database update <PreviousMigrationName> \
  --project IrbyImaging.DbContext

7. UML Diagram

classDiagram
    class Migration {
        <<abstract>>
        +void Up(MigrationBuilder)
        +void Down(MigrationBuilder)
    }
    class UpdateCrewTables {
        +void Up(MigrationBuilder)
        +void Down(MigrationBuilder)
    }
    Migration <|-- UpdateCrewTables

    class MigrationBuilder {
        +AlterColumn<T>(...)
        +AddColumn<T>(...)
        +DropColumn(...)
        // … other fluent methods
    }
    UpdateCrewTables ..> MigrationBuilder : uses

8. Error Handling & Return Types

  • Migration methods (Up/Down) return void.
  • Any failure during script execution will throw an exception and roll back the transaction automatically.

9. Third‑Party Integrations & Utilities

  • Microsoft.EntityFrameworkCore: Core migration APIs.
  • SQL Server driver (Microsoft.Data.SqlClient) for datetime2 and bit support.
  • No additional helper utilities in this migration file.

10. Conclusion

🚀 The UpdateCrewTables migration brings improved type safety, auditability, and soft‑delete capabilities to your Crew domain. By standardizing date-time storage and tracking creation metadata, you enable richer business logic and robust data integrity for your IrbyImaging application. Ensure to test in staging, back up your database, and then roll out to production seamlessly!


Happy Coding! 🎉