π AddTimesheetNumber Migration Documentation
File:
IrbyImaging.DbContext/Migrations/20250303151248_AddTimesheetNumber.cs
ποΈ Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Class Structure
- Up Method: Schema Changes
- 4.1. Altering
CrewIdColumn - 4.2. Adding
TimesheetNumberColumn - 4.3. Adding
FieldTimesheetTimesheetIdColumn - 4.4. Creating Index & Foreign Key
- 4.1. Altering
- Down Method: Rollback
- Data Model Impact
- UML Diagram πΊοΈ
- Usage & Examples π
- Error Handling Considerations β οΈ
- 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
CrewandFieldTimesheetviaFieldTimesheetTimesheetId. - Converts the existing
CrewIdcolumn onFieldTimesheetfrom aGuidto astringtype (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:
CrewIdwasuniqueidentifier NOT NULL. - After:
nvarchar(max) NULLto support external/text-based IDs or optional linking.
4.2. Adding TimesheetNumber Column
- Type:
int - Identity: Starts at
1and increments by1 - 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
CrewandFieldTimesheet.
- From:
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
CrewIdfromGuidβstringmight truncate or fail if legacy GUIDs have special formatting. - Test with sample data.
- Converting
- Foreign Key Violations:
- Ensure no orphaned
Crew.FieldTimesheetTimesheetIdvalues exist before reversion.
- Ensure no orphaned
- 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
Crewback to its associatedFieldTimesheet.
- Added a human-readable
- Next Steps:
- Update your entity configurations (if using Fluent API) to match column changes.
- Adjust any DTOs/ViewModels consuming
CrewIdorTimesheetNumber. - Test thoroughly in dev and staging to catch data conversion issues.
Happy Coding! β¨