πŸ“„ AddTimesheetNumber Migration Documentation

File: IrbyImaging.DbContext/Migrations/20250303151248_AddTimesheetNumber.cs


πŸ—‚οΈ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Class Structure
  4. Up Method: Schema Changes
    • 4.1. Altering CrewId Column
    • 4.2. Adding TimesheetNumber Column
    • 4.3. Adding FieldTimesheetTimesheetId Column
    • 4.4. Creating Index & Foreign Key
  5. Down Method: Rollback
  6. Data Model Impact
  7. UML Diagram πŸ—ΊοΈ
  8. Usage & Examples πŸš€
  9. Error Handling Considerations ⚠️
  10. Conclusion & Next Steps βœ…

1. Overview & Purpose

This EF Core migration introduces a new TimesheetNumber identity column to the FieldTimesheet table and establishes a…

  • One-to-many/optional relationship between Crew and FieldTimesheet via FieldTimesheetTimesheetId.
  • Converts the existing CrewId column on FieldTimesheet from a Guid to a string type (nullable).

Goals:

  • Provide an incremental, auto-generated integer (TimesheetNumber) per timesheet for easier human-readable reference.
  • Link a crew record back to a specific timesheet.

2. Dependencies & Technologies

Technology Version / Detail
.NET / EF Core Microsoft.EntityFrameworkCore
Database SQL Server (nvarchar(max), uniqueidentifier, int)
Migrations API MigrationBuilder, AlterColumn, AddColumn, CreateIndex, AddForeignKey

3. Migration Class Structure

public partial class AddTimesheetNumber : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) { /* apply changes */ }
    protected override void Down(MigrationBuilder migrationBuilder) { /* revert changes */ }
}
  • Up: Applies schema modifications when migrating forward.
  • Down: Reverts those modifications when rolling back the migration.

4. Up Method: Schema Changes

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Alter CrewId from Guid β†’ string (nullable)
    migrationBuilder.AlterColumn<string>(
        name: "CrewId",
        schema: "irby",
        table: "FieldTimesheet",
        type: "nvarchar(max)",
        nullable: true,
        oldClrType: typeof(Guid),
        oldType: "uniqueidentifier");

    // 2️⃣ Add TimesheetNumber with Identity(1,1)
    migrationBuilder.AddColumn<int>(
        name: "TimesheetNumber",
        schema: "irby",
        table: "FieldTimesheet",
        type: "int",
        nullable: false,
        defaultValue: 0)
        .Annotation("SqlServer:Identity", "1, 1");

    // 3️⃣ Add nullable FK field to Crew
    migrationBuilder.AddColumn<Guid>(
        name: "FieldTimesheetTimesheetId",
        schema: "irby",
        table: "Crew",
        type: "uniqueidentifier",
        nullable: true);

    // 4️⃣ Create index + foreign key
    migrationBuilder.CreateIndex(
        name: "IX_Crew_FieldTimesheetTimesheetId",
        schema: "irby",
        table: "Crew",
        column: "FieldTimesheetTimesheetId");

    migrationBuilder.AddForeignKey(
        name: "FK_Crew_FieldTimesheet_FieldTimesheetTimesheetId",
        schema: "irby",
        table: "Crew",
        column: "FieldTimesheetTimesheetId",
        principalSchema: "irby",
        principalTable: "FieldTimesheet",
        principalColumn: "TimesheetId");
}

4.1. Altering CrewId Column

  • Before: CrewId was uniqueidentifier NOT NULL.
  • After: nvarchar(max) NULL to support external/text-based IDs or optional linking.

4.2. Adding TimesheetNumber Column

  • Type: int
  • Identity: Starts at 1 and increments by 1
  • Purpose: Provide a user-friendly, sequential number per timesheet.

4.3. Adding FieldTimesheetTimesheetId Column

  • Type: uniqueidentifier (nullable)
  • On Table: Crew
  • Purpose: Holds an optional link back to a FieldTimesheet.

4.4. Creating Index & Foreign Key

  • Index: Speeds up lookups on Crew.FieldTimesheetTimesheetId.
  • Foreign Key Constraint:
    • From: Crew.FieldTimesheetTimesheetId
    • To: FieldTimesheet.TimesheetId
    • Enforces referential integrity between Crew and FieldTimesheet.

5. Down Method: Rollback

Reverts every change in reverse order.

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropForeignKey(
        name: "FK_Crew_FieldTimesheet_FieldTimesheetTimesheetId", ...);

    migrationBuilder.DropIndex(
        name: "IX_Crew_FieldTimesheetTimesheetId", ...);

    migrationBuilder.DropColumn(
        name: "TimesheetNumber", ...);

    migrationBuilder.DropColumn(
        name: "FieldTimesheetTimesheetId", ...);

    migrationBuilder.AlterColumn<Guid>(
        name: "CrewId",
        schema: "irby",
        table: "FieldTimesheet",
        type: "uniqueidentifier",
        nullable: false,
        defaultValue: Guid.Empty,
        oldClrType: typeof(string),
        oldType: "nvarchar(max)",
        oldNullable: true);
}

6. Data Model Impact

Entity Before After
FieldTimesheet CrewId : Guid NOT NULL CrewId : string NULL TimesheetNumber : int
Crew (no column) FieldTimesheetTimesheetId : Guid NULL
  • New Relationship:
    Crew β†’ (0..1) FieldTimesheet

7. UML Diagram πŸ—ΊοΈ

classDiagram
    %% Entities
    class FieldTimesheet {
      +Guid TimesheetId {PK}
      +string? CrewId
      +int TimesheetNumber
    }
    class Crew {
      +Guid CrewId {PK}
      +Guid? FieldTimesheetTimesheetId
    }

    %% Relationship
    Crew --> FieldTimesheet : FieldTimesheetTimesheetId

8. Usage & Examples πŸš€

8.1. Applying the Migration

# 1. Add migration
dotnet ef migrations add AddTimesheetNumber \
  --context IrbyImagingDbContext \
  --output-dir Migrations

# 2. Update database
dotnet ef database update --context IrbyImagingDbContext

8.2. Rolling Back

# Roll back one migration
dotnet ef database update LastGoodMigrationName

9. Error Handling Considerations ⚠️

  • Existing Data:
    • Converting CrewId from Guid β†’ string might truncate or fail if legacy GUIDs have special formatting.
    • Test with sample data.
  • Foreign Key Violations:
    • Ensure no orphaned Crew.FieldTimesheetTimesheetId values exist before reversion.
  • Identity Seed:
    • If you reseed or have manual inserts, be aware of identity gaps.

10. Conclusion & Next Steps βœ…

  • Purpose Achieved:
    • Added a human-readable TimesheetNumber.
    • Established an optional link from Crew back to its associated FieldTimesheet.
  • Next Steps:
    1. Update your entity configurations (if using Fluent API) to match column changes.
    2. Adjust any DTOs/ViewModels consuming CrewId or TimesheetNumber.
    3. Test thoroughly in dev and staging to catch data conversion issues.

Happy Coding! ✨