π FieldLogTimesheet Migration Documentation
ποΈ Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Migration Workflow π
- Data Model Changes π
- 4.1 Dropped Table
- 4.2 Created Tables
- Entity Relationship Diagram (ERD) π
- Key Functional Areas
- Sample Code Snippets π§©
- Usage & Applying Migration π
- Rollback (Down Method) π
- 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 yourDbContextis configured to point at theirbyschema.
3. Migration Workflow π
- Design New Model in your code-first classes (not shown here).
- Run
dotnet ef migrations add FieldLogTimesheet - Inspect the generated code (
Up/Downmethods). - Apply with
dotnet ef database update - 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&TimesheetIdfor 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 flatFieldLogEmployeesinto 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 π
- Ensure your
DbContextis configured with theirbyschema. - Run
dotnet ef database updateto migrate. - Verify new tables exist and old
FieldLogEmployeesis gone. - 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:
- Drop
EquipmentEntry,WorkEntry,EquipmentTimesheet,EmployeeTimesheet,FieldTimesheet. - Re-create the legacy
FieldLogEmployeestable 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
FieldLogEmployeesinto 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! π