📑 IrbyImaging.DbContext.Migrations.UpdateCrewTables Migration Documentation
📋 Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Data Models Affected
- Detailed Migration Analysis
- Database Impact & Best Practices
- Usage Examples
- UML Diagram
- Error Handling & Return Types
- Third‑Party Integrations & Utilities
- Conclusion
1. Overview & Purpose
✨ UpdateCrewTables is an Entity Framework Core migration that evolves the Crew and CrewEmployee tables in the "irby" schema:
- Standardizes the
CreatedDatein CrewEmployee fromstringtoDateTime. - 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) { /* … */ }
}
}
Upmethod: Defines schema changes to apply.Downmethod: 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
CreatedDatefromstring (nvarchar(max))toDateTime (datetime2) - Ensures non-nullable with a default of
0001-01-01T00:00:00.
- Converts
-
AddColumn
CreatedBy(string?): nullable audit field.CreatedDate(DateTime): non-nullable with default.IsDeleted(bool): non-nullable, defaulting tofalse(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: RestoresCrewEmployee.CreatedDateback to its originalstringtype.
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/Downoperations 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) fordatetime2andbitsupport. - 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! 🎉