📑 UpdateFieldTimesheetCrew Migration Documentation

Filename:
IrbyImaging.DbContext/Migrations/20250227173147_UpdateFieldTimesheetCrew.cs

Generated On: 2025-02-27 17:31:47 UTC


🗂 Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Anatomy
    3.1. Up Method
    3.2. Down Method
  4. Data Model Changes
    4.1. Crew Entity Definition
    4.2. Relationship to Job
  5. Schema Diagram (UML / ER)
  6. Usage & Application
  7. Sample Commands & Code Snippets
  8. Error Handling & Rollback
  9. Conclusion

1. Overview & Purpose

This Entity Framework Core migration introduces a new table named Crew under the irby schema. It is designed to:

  • 📌 Store Crew details associated with a Job.
  • 📌 Establish a one-to-many relationship: one Job can have multiple Crews.
  • 📌 Ensure referential integrity with cascade delete.

This migration is part of the IrbyImaging.DbContext project and is intended to be applied to the production or development database to support new timesheet and crew-management features.


2. Dependencies & Technologies

Component Version / Type
.NET SDK 6.0+ / 7.0+
Entity Framework Core (EF Core) Microsoft.EntityFrameworkCore.Migrations
Database SQL Server (uniqueidentifier, nvarchar)
Language C# (.NET)

Key NuGet Packages

  • Microsoft.EntityFrameworkCore
  • Microsoft.EntityFrameworkCore.SqlServer
  • Microsoft.EntityFrameworkCore.Tools

3. Migration Anatomy

The migration class UpdateFieldTimesheetCrew inherits from Migration and overrides two methods:

public partial class UpdateFieldTimesheetCrew : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder) { /* ... */ }
    protected override void Down(MigrationBuilder migrationBuilder) { /* ... */ }
}

3.1. Up Method

  • CreateTable: Defines the Crew table.
  • Columns:
    • CrewId (GUID, PK)
    • CrewName (string, nullable)
    • JobId (GUID, FK)
  • Primary Key: PK_Crew on CrewId.
  • Foreign Key:
    • References irby.Job(JobId)
    • Constraint: FK_Crew_Job_JobId
    • On Delete: Cascade
  • Index:
    • IX_Crew_JobId on JobId for quicker lookups.
protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "Crew",
        schema: "irby",
        columns: table => new
        {
            CrewId   = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            CrewName = table.Column<string>(type: "nvarchar(max)", nullable: true),
            JobId    = table.Column<Guid>(type: "uniqueidentifier", nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_Crew", x => x.CrewId);
            table.ForeignKey(
                name: "FK_Crew_Job_JobId",
                column: x => x.JobId,
                principalSchema: "irby",
                principalTable: "Job",
                principalColumn: "JobId",
                onDelete: ReferentialAction.Cascade);
        });

    migrationBuilder.CreateIndex(
        name: "IX_Crew_JobId",
        schema: "irby",
        table: "Crew",
        column: "JobId");
}

3.2. Down Method

Reverts the changes made in Up by dropping the Crew table:

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "Crew",
        schema: "irby");
}

4. Data Model Changes

4.1. Crew Entity Definition

Column Type Nullable Key Notes
CrewId uniqueidentifier (GUID) No Primary Key Auto-generated GUID
CrewName nvarchar(max) Yes – Name or descriptor of the crew
JobId uniqueidentifier (GUID) No Foreign Key Links to irby.Job (cascade delete)

4.2. Relationship to Job

  • One Job can have many Crews.
  • Cascade Delete ensures if a Job is removed, all associated Crews are automatically deleted.

5. Schema Diagram (UML / ER)

@startuml
!theme styled
package "irby" {
  class Job {
    + Guid JobId
    ...
  }

  class Crew {
    + Guid CrewId
    + string CrewName
    + Guid JobId
  }

  Job "1" --> "*" Crew : has
}
@enduml

ASCII ER Diagram

+-----------+     1     +--------+
|   Job     |-----------|  Crew  |
+-----------+  *        +--------+
| PK JobId  |           | PK CrewId
|    ...    |           | CrewName
+-----------+           | FK JobId
                        +--------+

6. Usage & Application

  1. Add Migration
    If you need to recreate or update the migration:

    dotnet ef migrations add UpdateFieldTimesheetCrew \
      --project IrbyImaging.DbContext \
      --output-dir Migrations
    
  2. Apply Migration
    To apply to your database:

    dotnet ef database update UpdateFieldTimesheetCrew \
      --project IrbyImaging.DbContext
    
  3. Rollback
    To revert:

    dotnet ef database update LastGoodMigrationName
    

7. Sample Commands & Code Snippets

Applying via C# (at Startup)

public void Configure(IApplicationBuilder app)
{
    using var scope = app.ApplicationServices.CreateScope();
    var dbContext = scope.ServiceProvider.GetRequiredService<IrbyDbContext>();
    dbContext.Database.Migrate(); // Applies all pending migrations including UpdateFieldTimesheetCrew
}

LINQ Query Example

var crewsForJob = await dbContext.Crews
    .Where(c => c.JobId == someJobId)
    .ToListAsync();

8. Error Handling & Rollback

  • Schema Mismatch: EF Core will throw a SqlException if the table exists or column conflicts occur.
  • Foreign Key Violations: On cascade delete, referential integrity ensures child rows are removed properly.
  • Rollback Strategy: Always have a backup or a point-in-time restore strategy. Use Down() method to roll back.

9. Conclusion

This migration ⚙️ UpdateFieldTimesheetCrew enriches the database schema by introducing the Crew entity and linking it to Job. It lays the groundwork for advanced crew and timesheet management, ensuring data integrity via foreign keys and cascade delete behaviors.

By following the Usage & Application steps, developers can seamlessly integrate this schema change into their CI/CD pipelines and local development environments.


✨ Happy Coding! ✨