π Migration Documentation: 20250217161611_UpdateFieldTimesheet
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
3.1. Migration Class Definition
3.2.UpMethod
3.3.DownMethod - Data Models Affected
4.1. WorkEntry Table
4.2. FieldTimesheet Table - Applying & Reverting the Migration
- UML Diagram
- Sample Usage
- Conclusion
π 1. Overview & Purpose
This EF Core migration introduces audit and time-tracking fields to the WorkEntry and FieldTimesheet tables within the irby schema. The changes enable:
- Start/End times and lunch durations for work entries
- Soft-delete capability via
IsDeletedflags - Approval, review, and submission metadata (dates, users, comments, status) on field timesheets
These extensions pave the way for richer timesheet workflows, allowing records to be tracked through submission β review β approval lifecycles.
βοΈ 2. Dependencies & Technologies
| Component | Version / Type | Purpose |
|---|---|---|
| .NET / C# | .NET 6+ | Runtime & language |
| Entity Framework Core (EF Core) | 6.x | ORM & migration framework |
| SQL Server (MSSQL) | Any EF Coreβcompatible DB | Underlying relational database |
Microsoft.EntityFrameworkCore.Migrations |
NuGet package | Provides Migration base class and tooling |
ποΈ 3. Key Functional Areas
3.1 Migration Class Definition
namespace IrbyImaging.DbContext.Migrations
{
/// <inheritdoc />
public partial class UpdateFieldTimesheet : Migration
{
...
}
}
- Namespace:
IrbyImaging.DbContext.Migrations - Class:
UpdateFieldTimesheetinherits fromMigration - Annotations: Uses
#nullable disableto maintain EF Core defaults
3.2 Up Method
The Up method applies schema changes forward in the migration history:
protected override void Up(MigrationBuilder migrationBuilder)
{
// WorkEntry table enhancements
migrationBuilder.AddColumn<string>(
name: "StartTime", schema: "irby", table: "WorkEntry",
type: "nvarchar(max)", nullable: true);
migrationBuilder.AddColumn<string>(
name: "EndTime", schema: "irby", table: "WorkEntry",
type: "nvarchar(max)", nullable: true);
migrationBuilder.AddColumn<double>(
name: "LunchHour", schema: "irby", table: "WorkEntry",
type: "float", nullable: false, defaultValue: 0.0);
migrationBuilder.AddColumn<bool>(
name: "IsDeleted", schema: "irby", table: "WorkEntry",
type: "bit", nullable: false, defaultValue: false);
// FieldTimesheet table enhancements
migrationBuilder.AddColumn<string>(
name: "SubmittedBy", schema: "irby", table: "FieldTimesheet",
type: "nvarchar(max)", nullable: true);
migrationBuilder.AddColumn<DateTime>(
name: "SubmittedDate", schema: "irby", table: "FieldTimesheet",
type: "datetime2", nullable: false,
defaultValue: new DateTime(1,1,1,0,0,0,0,DateTimeKind.Unspecified));
migrationBuilder.AddColumn<string>(
name: "ReviewedBy", ...);
migrationBuilder.AddColumn<DateTime>(
name: "ReviewedDate", ...);
migrationBuilder.AddColumn<string>(
name: "ApprovedBy", ...);
migrationBuilder.AddColumn<DateTime>(
name: "ApprovedDate", ...);
migrationBuilder.AddColumn<string>(
name: "Comments", ...);
migrationBuilder.AddColumn<int>(
name: "Status", ... defaultValue: 0);
migrationBuilder.AddColumn<bool>(
name: "IsDeleted", ... defaultValue: false);
}
Highlights
- Soft Delete
IsDeletedcolumns added to both tables
- Timestamps & Users
SubmittedBy/SubmittedDateReviewedBy/ReviewedDateApprovedBy/ApprovedDate
- Additional Fields
Comments(free-form notes)Status(integer enum)StartTime/EndTime/LunchHouron entries
3.3 Down Method
The Down method rolls back these changes:
protected override void Down(MigrationBuilder migrationBuilder)
{
// WorkEntry rollbacks
migrationBuilder.DropColumn(name: "StartTime", ...);
migrationBuilder.DropColumn(name: "EndTime", ...);
migrationBuilder.DropColumn(name: "LunchHour", ...);
migrationBuilder.DropColumn(name: "IsDeleted", ...);
// FieldTimesheet rollbacks
migrationBuilder.DropColumn(name: "SubmittedBy", ...);
migrationBuilder.DropColumn(name: "SubmittedDate", ...);
migrationBuilder.DropColumn(name: "ReviewedBy", ...);
migrationBuilder.DropColumn(name: "ReviewedDate", ...);
migrationBuilder.DropColumn(name: "ApprovedBy", ...);
migrationBuilder.DropColumn(name: "ApprovedDate", ...);
migrationBuilder.DropColumn(name: "Comments", ...);
migrationBuilder.DropColumn(name: "Status", ...);
migrationBuilder.DropColumn(name: "IsDeleted", ...);
}
ποΈ 4. Data Models Affected
4.1 WorkEntry Table
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| StartTime | nvarchar | Yes | β | Clock-in time (e.g., "08:30 AM") |
| EndTime | nvarchar | Yes | β | Clock-out time (e.g., "05:00 PM") |
| LunchHour | float | No | 0.0 | Duration of lunch in hours |
| IsDeleted | bit | No | false | Soft-delete flag |
4.2 FieldTimesheet Table
| Column | Type | Nullable | Default | Description |
|---|---|---|---|---|
| SubmittedBy | nvarchar | Yes | β | User who submitted the timesheet |
| SubmittedDate | datetime2 | No | 0001-01-01T00:00:00 |
Timestamp of submission |
| ReviewedBy | nvarchar | Yes | β | User who reviewed the timesheet |
| ReviewedDate | datetime2 | No | 0001-01-01T00:00:00 |
Timestamp of review |
| ApprovedBy | nvarchar | Yes | β | User who approved the timesheet |
| ApprovedDate | datetime2 | No | 0001-01-01T00:00:00 |
Timestamp of approval |
| Comments | nvarchar | Yes | β | Optional free-text comments |
| Status | int | No | 0 | Enum representing timesheet status |
| IsDeleted | bit | No | false | Soft-delete flag |
π 5. Applying & Reverting the Migration
-
Add Migration
dotnet ef migrations add UpdateFieldTimesheet -
Update Database
dotnet ef database update -
To Rollback (to prior migration):
dotnet ef database update LastGoodMigrationName
π 6. UML Diagram
classDiagram
class UpdateFieldTimesheet {
<<Migration>>
+Up(MigrationBuilder)
+Down(MigrationBuilder)
}
%% Tables & Columns
class WorkEntry {
+string StartTime
+string EndTime
+double LunchHour
+bool IsDeleted
}
class FieldTimesheet {
+string SubmittedBy
+DateTime SubmittedDate
+string ReviewedBy
+DateTime ReviewedDate
+string ApprovedBy
+DateTime ApprovedDate
+string Comments
+int Status
+bool IsDeleted
}
UpdateFieldTimesheet --> WorkEntry : adds/drops
UpdateFieldTimesheet --> FieldTimesheet : adds/drops
π 7. Sample Usage
// In your DbContext-derived class, ensure migrations assembly is set:
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseSqlServer(
connectionString,
x => x.MigrationsAssembly("IrbyImaging.DbContext"));
}
// After adding migration:
await context.Database.MigrateAsync();
Querying soft-deleted records
var allEntries = context.WorkEntries
.IgnoreQueryFilters() // if you later implement global filter for IsDeleted
.Where(w => w.IsDeleted == false)
.ToList();
π 8. Conclusion
The UpdateFieldTimesheet migration enriches the WorkEntry and FieldTimesheet tables to support advanced timesheet lifecycle managementβtracking when records are submitted, reviewed, and approved, and enabling soft deletes. By integrating these columns, the database schema aligns closely with real-world auditing and workflow requirements. Implement this migration to pave the way for robust timesheet features in your application!
Happy coding! π