πŸ“š IrbyImaging.DbContext.Migrations – 20241115170848_FieldLogEmployees.cs


πŸ—‚ Table of Contents

  1. Overview & Purpose
  2. Migration Workflow
    2.1 Up() Method
    2.2 Down() Method
  3. FieldLogEmployees Table Schema
  4. Usage & Examples
    4.1 Applying the Migration
    4.2 Querying the New Table
  5. Dependencies & Technologies
  6. Entity Class Representation
  7. UML Diagram
  8. Conclusion

1. Overview & Purpose

πŸ”Ž What is this file?
This C# file is an Entity Framework Core Migration. It modifies the database schema in the irby schema:

  • Drops several obsolete columns from the Ticket table.
  • Creates a new table named FieldLogEmployees to track field labor entries.

Why?

  • The old ticket-related columns were either unused or moved to a different domain.
  • A dedicated Field Log table provides a clear, audit‑friendly structure for employee field work records (hours, per‑diem, meal provisions, etc.).

2. Migration Workflow

1. Up() Method

protected override void Up(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop obsolete columns from Ticket
    migrationBuilder.DropColumn(name: "AccountType",         schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "AccountTypeNotes",    schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "AdminNotes",          schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "ApproverAuthorization", schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "ExecutorAcknowledgement", schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "ExecutorDate",        schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "RequestDetails",      schema: "irby", table: "Ticket");
    migrationBuilder.DropColumn(name: "ServiceAdminAccess",  schema: "irby", table: "Ticket");

    // 2️⃣ Create new FieldLogEmployees table
    migrationBuilder.CreateTable(
        name: "FieldLogEmployees",
        schema: "irby",
        columns: table => new
        {
            FieldLogId       = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            WorkDate         = table.Column<DateTime>(type: "datetime2",       nullable: false),
            WorkCostCode     = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            ForemanSignature = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            PerDiemRate      = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            WorkOrderNumber  = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            CrewId           = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            JobNumber        = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            EmployeeId       = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            EmployeeName     = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            Class            = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            UnionCode        = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            Hours            = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            CreatedBy        = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            CreatedOn        = table.Column<DateTime>(type: "datetime2",       nullable: false),
            UpdatedBy        = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            UpdatedOn        = table.Column<DateTime>(type: "datetime2",       nullable: false),
            ReviewedBy       = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            ReviewedOn       = table.Column<DateTime>(type: "datetime2",       nullable: false),
            ApprovedBy       = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            ApprovedOn       = table.Column<DateTime>(type: "datetime2",       nullable: false),
            StartTime        = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            StopTime         = table.Column<string>(type: "nvarchar(max)",    nullable: true),
            BreakfastProvided= table.Column<bool>(type: "bit",              nullable: false),
            LunchProvided    = table.Column<bool>(type: "bit",              nullable: false),
            DinnerProvided   = table.Column<bool>(type: "bit",              nullable: false),
            MotelProvided    = table.Column<bool>(type: "bit",              nullable: false)
        },
        constraints: table =>
        {
            table.PrimaryKey("PK_FieldLogEmployees", x => x.FieldLogId);
        }
    );
}
  • Step 1: Remove 8 columns from irby.Ticket that are no longer needed.
  • Step 2: Create the irby.FieldLogEmployees table with 25 columns to capture detailed labor logs.

2. Down() Method

protected override void Down(MigrationBuilder migrationBuilder)
{
    // 1️⃣ Drop the FieldLogEmployees table
    migrationBuilder.DropTable(name: "FieldLogEmployees", schema: "irby");

    // 2️⃣ Re-add previously dropped Ticket columns
    migrationBuilder.AddColumn<string>(name: "AccountType", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "AccountTypeNotes", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "AdminNotes", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "ApproverAuthorization", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<bool>(name: "ExecutorAcknowledgement", schema: "irby", table: "Ticket", type: "bit", nullable: false, defaultValue: false);
    migrationBuilder.AddColumn<DateTime>(name: "ExecutorDate", schema: "irby", table: "Ticket", type: "datetime2", nullable: true);
    migrationBuilder.AddColumn<string>(name: "RequestDetails", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
    migrationBuilder.AddColumn<string>(name: "ServiceAdminAccess", schema: "irby", table: "Ticket", type: "nvarchar(max)", nullable: true);
}
  • Reversal Logic: Drops new table and re‑creates the old set of columns on Ticket, preserving the previous state if a rollback is required.

3. FieldLogEmployees Table Schema

Column Type Nullable Description
FieldLogId uniqueidentifier No Primary Key (GUID)
WorkDate datetime2 No Date of the field work
WorkCostCode nvarchar(max) Yes Cost code or billing code
ForemanSignature nvarchar(max) Yes Digital signature of the foreman
PerDiemRate nvarchar(max) Yes Per diem rate applied
WorkOrderNumber nvarchar(max) Yes Reference Work Order number
CrewId nvarchar(max) Yes Identifier for the field crew
JobNumber nvarchar(max) Yes Project or job number
EmployeeId nvarchar(max) Yes Employee’s unique ID
EmployeeName nvarchar(max) Yes Full name of the employee
Class nvarchar(max) Yes Employee class or role
UnionCode nvarchar(max) Yes Union affiliation code
Hours nvarchar(max) Yes Hours worked (could be HH:MM)
StartTime nvarchar(max) Yes Shift start time
StopTime nvarchar(max) Yes Shift end time
BreakfastProvided bit No Was breakfast provided?
LunchProvided bit No Was lunch provided?
DinnerProvided bit No Was dinner provided?
MotelProvided bit No Was lodging (motel) provided?
CreatedBy nvarchar(max) Yes Creator username
CreatedOn datetime2 No Timestamp when record was created
UpdatedBy nvarchar(max) Yes Last updater username
UpdatedOn datetime2 No Timestamp when record was last updated
ReviewedBy nvarchar(max) Yes Reviewer username
ReviewedOn datetime2 No Timestamp when record was reviewed
ApprovedBy nvarchar(max) Yes Approver username
ApprovedOn datetime2 No Timestamp when record was approved

4. Usage & Examples

4.1 Applying the Migration

  1. Via Package Manager Console

    Update-Database -Migration 20241115170848_FieldLogEmployees
    
  2. Via .NET CLI

    dotnet ef database update 20241115170848_FieldLogEmployees
    

βœ… Once applied, the database will no longer contain the dropped columns in irby.Ticket and will include the new irby.FieldLogEmployees table.


4.2 Querying the New Table

C# example using the generated DbContext:

using (var context = new IrbyImagingContext())
{
    // Fetch all field logs for a given date
    var logs = context.FieldLogEmployees
        .Where(fl => fl.WorkDate == DateTime.Today)
        .OrderBy(fl => fl.EmployeeName)
        .ToList();

    foreach (var log in logs)
    {
        Console.WriteLine($"{log.EmployeeName}: {log.Hours} hours");
    }
}

5. Dependencies & Technologies

  • .NET 6+ / .NET Core
  • Entity Framework Core
  • C# 10
  • SQL Server (assumes datetime2, uniqueidentifier)
  • Visual Studio / Rider for migrations management

6. Entity Class Representation

Although not shown in this migration, EF Core generates or expects an entity class similar to:

public class FieldLogEmployee
{
    public Guid      FieldLogId       { get; set; }
    public DateTime  WorkDate         { get; set; }
    public string?   WorkCostCode     { get; set; }
    public string?   ForemanSignature { get; set; }
    public string?   PerDiemRate      { get; set; }
    public string?   WorkOrderNumber  { get; set; }
    public string?   CrewId           { get; set; }
    public string?   JobNumber        { get; set; }
    public string?   EmployeeId       { get; set; }
    public string?   EmployeeName     { get; set; }
    public string?   Class            { get; set; }
    public string?   UnionCode        { get; set; }
    public string?   Hours            { get; set; }
    public string?   StartTime        { get; set; }
    public string?   StopTime         { get; set; }
    public bool      BreakfastProvided{ get; set; }
    public bool      LunchProvided    { get; set; }
    public bool      DinnerProvided   { get; set; }
    public bool      MotelProvided    { get; set; }
    public string?   CreatedBy        { get; set; }
    public DateTime  CreatedOn        { get; set; }
    public string?   UpdatedBy        { get; set; }
    public DateTime  UpdatedOn        { get; set; }
    public string?   ReviewedBy       { get; set; }
    public DateTime  ReviewedOn       { get; set; }
    public string?   ApprovedBy       { get; set; }
    public DateTime  ApprovedOn       { get; set; }
}

Note: You’ll need to add a DbSet<FieldLogEmployee> in your IrbyImagingContext:

public DbSet<FieldLogEmployee> FieldLogEmployees { get; set; }

7. UML Diagram

β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚       FieldLogEmployee        β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ + FieldLogId : Guid           β”‚
β”‚ + WorkDate : DateTime         β”‚
β”‚ + WorkCostCode : string?      β”‚
β”‚ + ForemanSignature : string?  β”‚
β”‚ + ...                         β”‚
β”‚ + BreakfastProvided : bool    β”‚
β”‚ + LunchProvided : bool        β”‚
β”‚ + DinnerProvided : bool       β”‚
β”‚ + MotelProvided : bool        β”‚
β”‚ + CreatedBy : string?         β”‚
β”‚ + CreatedOn : DateTime        β”‚
β”‚ + UpdatedBy : string?         β”‚
β”‚ + UpdatedOn : DateTime        β”‚
β”‚ + ReviewedBy : string?        β”‚
β”‚ + ReviewedOn : DateTime       β”‚
β”‚ + ApprovedBy : string?        β”‚
β”‚ + ApprovedOn : DateTime       β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

8. Conclusion

✨ 20241115170848_FieldLogEmployees streamlines how IrbyImaging tracks field labor logs by centralizing employee work entries in a dedicated table.

  • Old, scattered ticket fields are retired.
  • The new schema enables robust auditing (Created/Updated/Reviewed/Approved).
  • Info such as start/stop times and meal provisions can be reported easily.

Deploy this migration to keep your database in sync with the evolving business requirements for field labor tracking! πŸš€