πŸ“– FieldLogTimesheet Migration Documentation


πŸ—‚οΈ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Migration Workflow πŸ”„
  4. Data Model Changes πŸ“
  5. Entity Relationship Diagram (ERD) πŸ“Š
  6. Key Functional Areas
  7. Sample Code Snippets 🧩
  8. Usage & Applying Migration πŸš€
  9. Rollback (Down Method) πŸ”™
  10. Conclusion & Best Practices πŸ“Œ

1. Overview & Purpose

Migration Name: 20250115153413_FieldLogTimesheet
Namespace: IrbyImaging.DbContext.Migrations
Purpose:
This Entity Framework Core migration replaces the old FieldLogEmployees table with a new timesheet data model. It introduces:

  • FieldTimesheet – the main timesheet header
  • EmployeeTimesheet – association of employees to a timesheet
  • EquipmentTimesheet – association of equipment to a timesheet
  • WorkEntry – daily work records per employee
  • EquipmentEntry – time & usage records per equipment

This redesign provides a normalized schema, better scalability, and clearer separation of concerns.


2. Dependencies & Technologies

Dependency Version/Type
.NET / .NET Core 6.0+ (.NET 7, .NET 8)
Entity Framework Core 6.0+
SQL Server (or provider) datetime2, uniqueidentifier
C# 10.0+

πŸ“Œ Note: Make sure your project references the correct EF Core package (Microsoft.EntityFrameworkCore.SqlServer, Microsoft.EntityFrameworkCore.Tools, etc.) and your DbContext is configured to point at the irby schema.


3. Migration Workflow πŸ”„

  1. Design New Model in your code-first classes (not shown here).
  2. Run
    dotnet ef migrations add FieldLogTimesheet
    
  3. Inspect the generated code (Up / Down methods).
  4. Apply with
    dotnet ef database update
    
  5. Verify new tables & relationships in your database.

4. Data Model Changes πŸ“

4.1 Dropped Table

Table: irby.FieldLogEmployees

This legacy table stored a flat timesheet per employee. The migration’s first step removes it:

migrationBuilder.DropTable(
    name: "FieldLogEmployees",
    schema: "irby");

4.2 Created Tables

Below is a summary of the new tables and their key columns:

Table Name Description
FieldTimesheet Timesheet header (crew, foreman sign, date)
EmployeeTimesheet Link table: Timesheet ↔ Employee
EquipmentTimesheet Link table: Timesheet ↔ Equipment
WorkEntry Individual daily work logs for each employee
EquipmentEntry Individual daily usage logs for equipment

4.2.1 FieldTimesheet

Column Name Type Nullable Notes
TimesheetId uniqueidentifier No PK
CrewId nvarchar(max) No Identifier for the field crew
ForemanSignature nvarchar(max) Yes Digital signature (optional)
CreatedDate datetime2 No Timestamp when created
migrationBuilder.CreateTable(
    name: "FieldTimesheet", schema: "irby",
    columns: table => new
    {
        TimesheetId      = table.Column<Guid>(nullable: false),
        CrewId           = table.Column<string>(nullable: false),
        ForemanSignature = table.Column<string>(nullable: true),
        CreatedDate      = table.Column<DateTime>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_FieldTimesheet", x => x.TimesheetId);
    });

4.2.2 EmployeeTimesheet

Column Name Type Nullable Notes
EmployeeTimesheetId uniqueidentifier No PK
TimesheetId uniqueidentifier No FK β†’ FieldTimesheet.TimesheetId
EmployeeId uniqueidentifier No FK β†’ Employee.EmployeeId

🧩 Indexes on EmployeeId & TimesheetId for fast lookups.

migrationBuilder.CreateTable(
    name: "EmployeeTimesheet", schema: "irby",
    columns: table => new
    {
        EmployeeTimesheetId = table.Column<Guid>(nullable: false),
        TimesheetId         = table.Column<Guid>(nullable: false),
        EmployeeId          = table.Column<Guid>(nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_EmployeeTimesheet", x => x.EmployeeTimesheetId);
        table.ForeignKey(
            name: "FK_EmployeeTimesheet_FieldTimesheet_TimesheetId",
            column: x => x.TimesheetId,
            principalSchema: "irby",
            principalTable: "FieldTimesheet",
            principalColumn: "TimesheetId",
            onDelete: ReferentialAction.Cascade);
        table.ForeignKey(
            name: "FK_EmployeeTimesheet_Employee_EmployeeId",
            column: x => x.EmployeeId,
            principalSchema: "irby",
            principalTable: "Employee",
            principalColumn: "EmployeeId",
            onDelete: ReferentialAction.Cascade);
    });

4.2.3 EquipmentTimesheet

Column Name Type Nullable Notes
EquipmentTimesheetId uniqueidentifier No PK
TimesheetId uniqueidentifier No FK β†’ FieldTimesheet.TimesheetId
EquipmentId uniqueidentifier No FK β†’ Equipment.EquipmentId

πŸ” Indexed by TimesheetId & EquipmentId.


4.2.4 WorkEntry

Column Name Type Nullable Notes
WorkEntryId uniqueidentifier No PK
WorkDate datetime2 No Date of work
JobNo nvarchar(max) Yes Job number
CostCode nvarchar(max) Yes Cost code
WorkOrder nvarchar(max) Yes Work order number
Class nvarchar(max) Yes Employee classification
UnionCode nvarchar(max) Yes Union code
PayCode nvarchar(max) Yes Pay code
RegularHours float No Regular hours worked
OvertimeHours float No Overtime hours
DoubletimeHours float No Double-time hours
PerDiemRate float No Per diem rate
JobTransfer nvarchar(max) Yes Transfer code
Breakfast, Lunch, DinnerMotel bit No Meal & lodging flags
EmployeeTimesheetId uniqueidentifier No FK β†’ EmployeeTimesheet.EmployeeTimesheetId

4.2.5 EquipmentEntry

Column Name Type Nullable Notes
EquipmentEntryId uniqueidentifier No PK
OdometerReading float No Equipment odometer at entry time
CostCode, JobNo nvarchar(max) Yes Job & cost codes
WorkedHours, IdleHours float No Hours used & idle
EquipmentTimesheetId uniqueidentifier No FK β†’ EquipmentTimesheet.EquipmentTimesheetId

5. Entity Relationship Diagram (ERD) πŸ“Š

erDiagram
    FieldTimesheet ||--o{ EmployeeTimesheet : has
    FieldTimesheet ||--o{ EquipmentTimesheet : has

    EmployeeTimesheet ||--o{ WorkEntry : records
    EquipmentTimesheet ||--o{ EquipmentEntry : records

    EmployeeTimesheet }o--|| Employee : belongs_to
    EquipmentTimesheet }o--|| Equipment : belongs_to

6. Key Functional Areas

  • Data Normalization
    Splitting a flat FieldLogEmployees into header and detail tables.

  • Referential Integrity
    Cascading deletes ensure no orphaned child records.

  • Query Performance
    Indexes on FK columns (e.g., EmployeeId, TimesheetId, EquipmentId).

  • Extensibility
    Easy to add additional entry types (e.g., TravelEntry, MaterialEntry).


7. Sample Code Snippets 🧩

a) Applying the Migration

# Add migration (if not auto-generated)
dotnet ef migrations add FieldLogTimesheet

# Apply to database
dotnet ef database update

b) In Your DbContext

public class IrbyContext : DbContext
{
    public DbSet<FieldTimesheet> FieldTimesheets { get; set; }
    public DbSet<EmployeeTimesheet> EmployeeTimesheets { get; set; }
    public DbSet<EquipmentTimesheet> EquipmentTimesheets { get; set; }
    public DbSet<WorkEntry> WorkEntries { get; set; }
    public DbSet<EquipmentEntry> EquipmentEntries { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("irby");

        modelBuilder.Entity<FieldTimesheet>()
            .HasKey(f => f.TimesheetId);

        // Configure relationships & indexes...
    }
}

c) Creating a Timesheet in Code

var timesheet = new FieldTimesheet
{
    TimesheetId = Guid.NewGuid(),
    CrewId = "Crew-Alpha",
    ForemanSignature = "DigitalSig123",
    CreatedDate = DateTime.UtcNow
};

var empTs = new EmployeeTimesheet
{
    EmployeeTimesheetId = Guid.NewGuid(),
    EmployeeId = employeeId,
    TimesheetId = timesheet.TimesheetId
};

var workEntry = new WorkEntry
{
    WorkEntryId = Guid.NewGuid(),
    WorkDate = DateTime.Today,
    RegularHours = 8,
    EmployeeTimesheetId = empTs.EmployeeTimesheetId
};

dbContext.FieldTimesheets.Add(timesheet);
dbContext.EmployeeTimesheets.Add(empTs);
dbContext.WorkEntries.Add(workEntry);
await dbContext.SaveChangesAsync();

8. Usage & Applying Migration πŸš€

  1. Ensure your DbContext is configured with the irby schema.
  2. Run dotnet ef database update to migrate.
  3. Verify new tables exist and old FieldLogEmployees is gone.
  4. Update any repository or service layer to use the new entities.

9. Rollback (Down Method) πŸ”™

If you ever need to revert:

dotnet ef database update <previous-migration-name>

The Down method will:

  1. Drop EquipmentEntry, WorkEntry, EquipmentTimesheet, EmployeeTimesheet, FieldTimesheet.
  2. Re-create the legacy FieldLogEmployees table with its original columns.

10. Conclusion & Best Practices πŸ“Œ

  • Validation: Always back up production data before dropping tables.
  • Data Migration: If needed, write a one-time script to migrate old data from FieldLogEmployees into the new schema.
  • Testing: Use integration tests to verify referential integrity & cascade rules.
  • Version Control: Commit the generated migration file for team consistency.

πŸŽ‰ With this migration in place, your system now has a flexible, scalable, and normalized timesheet data model!


Happy coding! πŸš€