πŸ“š Migration Documentation: 20250217161611_UpdateFieldTimesheet


πŸ“– Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
    3.1. Migration Class Definition
    3.2. Up Method
    3.3. Down Method
  4. Data Models Affected
    4.1. WorkEntry Table
    4.2. FieldTimesheet Table
  5. Applying & Reverting the Migration
  6. UML Diagram
  7. Sample Usage
  8. 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 IsDeleted flags
  • 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: UpdateFieldTimesheet inherits from Migration
  • Annotations: Uses #nullable disable to 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
    • IsDeleted columns added to both tables
  • Timestamps & Users
    • SubmittedBy / SubmittedDate
    • ReviewedBy / ReviewedDate
    • ApprovedBy / ApprovedDate
  • Additional Fields
    • Comments (free-form notes)
    • Status (integer enum)
    • StartTime / EndTime / LunchHour on 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

  1. Add Migration

    dotnet ef migrations add UpdateFieldTimesheet
    
  2. Update Database

    dotnet ef database update
    
  3. 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! πŸš€