π IrbyImaging.DbContext.Migrations β 20241115170848_FieldLogEmployees.cs
π Table of Contents
- Overview & Purpose
- Migration Workflow
2.1 Up() Method
2.2 Down() Method FieldLogEmployeesTable Schema- Usage & Examples
4.1 Applying the Migration
4.2 Querying the New Table - Dependencies & Technologies
- Entity Class Representation
- UML Diagram
- 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
Tickettable. - Creates a new table named
FieldLogEmployeesto 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.Ticketthat are no longer needed. - Step 2: Create the
irby.FieldLogEmployeestable 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
-
Via Package Manager Console
Update-Database -Migration 20241115170848_FieldLogEmployees -
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! π