π Table of Contents
- Overview & Purpose
- Migration Class: AddMechanicTimesheet
- 2.1 Up Method
- 2.2 Down Method
- Database Schema Changes
- UML Class Diagram
- Dependencies & Technologies
- How to Apply / Rollback Migration
- Usage Examples
- Sample DbContext Entity
- Conclusion
π Overview & Purpose
This migration, AddMechanicTimesheet, adds a new table MechanicTimesheet to the irby schema in the database.
It is part of the Entity Framework Core (EF Core) migrations pipeline and is designed to:
- Store timesheet entries for mechanics.
- Track working hours, job details, cost types, and approval status.
- Provide a historical log of mechanic work sessions for payroll and reporting.
π Migration Class: AddMechanicTimesheet
namespace IrbyImaging.DbContext.Migrations
{
public partial class AddMechanicTimesheet : Migration
{
protected override void Up(MigrationBuilder migrationBuilder)
{
// Create table...
}
protected override void Down(MigrationBuilder migrationBuilder)
{
// Drop table...
}
}
}
2.1 Up Method
The Up method applies the migration: it creates the MechanicTimesheet table with all specified columns and primary key.
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "MechanicTimesheet",
schema: "irby",
columns: table => new
{
Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
EmployeeId = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
SubmittedBy = table.Column<string>(type: "nvarchar(max)", nullable: true),
StartTime = table.Column<string>(type: "nvarchar(max)", nullable: true),
EndTime = table.Column<string>(type: "nvarchar(max)", nullable: true),
StartDate = table.Column<DateTime>(type: "datetime2", nullable: false),
Job = table.Column<string>(type: "nvarchar(max)", nullable: true),
PayCode = table.Column<string>(type: "nvarchar(max)", nullable: true),
CostType = table.Column<string>(type: "nvarchar(max)", nullable: true),
UnitNumber = table.Column<string>(type: "nvarchar(max)", nullable: true),
WorkOrder = table.Column<string>(type: "nvarchar(max)", nullable: true),
Description = table.Column<string>(type: "nvarchar(max)", nullable: true),
RegularTime = table.Column<string>(type: "nvarchar(max)", nullable: true),
OverTime = table.Column<string>(type: "nvarchar(max)", nullable: true),
Status = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_MechanicTimesheet", x => x.Id);
}
);
}
2.2 Down Method
The Down method reverses the migration: it drops the MechanicTimesheet table.
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "MechanicTimesheet",
schema: "irby"
);
}
πΎ Database Schema Changes
3.1 MechanicTimesheet Table Definition
| Table Schema | Table Name |
|---|---|
irby |
MechanicTimesheet |
Purpose: Store mechanicsβ daily time entries, job codes, descriptions, and status for payroll and reporting.
3.2 Columns & Constraints
| Column Name | Data Type | Nullable | Description |
|---|---|---|---|
| Id | uniqueidentifier |
No | Primary Key (GUID) |
| EmployeeId | uniqueidentifier |
No | Reference to the employee (GUID) |
| SubmittedBy | nvarchar(max) |
Yes | User who submitted the entry |
| StartTime | nvarchar(max) |
Yes | Shift start time (e.g., "08:00 AM") |
| EndTime | nvarchar(max) |
Yes | Shift end time (e.g., "05:00 PM") |
| StartDate | datetime2 |
No | Date of the work session |
| Job | nvarchar(max) |
Yes | Job code or title |
| PayCode | nvarchar(max) |
Yes | Payroll code (regular, overtime, etc.) |
| CostType | nvarchar(max) |
Yes | Cost allocation type |
| UnitNumber | nvarchar(max) |
Yes | Equipment or unit number |
| WorkOrder | nvarchar(max) |
Yes | Associated work order number |
| Description | nvarchar(max) |
Yes | Free-text description of tasks performed |
| RegularTime | nvarchar(max) |
Yes | Regular hours worked (e.g., "8:00") |
| OverTime | nvarchar(max) |
Yes | Overtime hours worked (e.g., "2:00") |
| Status | int |
No | Entry status (e.g., Pending = 0, Approved = 1, Rejected) |
- Primary Key:
PK_MechanicTimesheet (Id) - Schema: objects are created under the
irbyschema.
π· UML Class Diagram
classDiagram
class MechanicTimesheet {
+Guid Id
+Guid EmployeeId
+string SubmittedBy
+string StartTime
+string EndTime
+DateTime StartDate
+string Job
+string PayCode
+string CostType
+string UnitNumber
+string WorkOrder
+string Description
+string RegularTime
+string OverTime
+int Status
}
π§ Dependencies & Technologies
- .NET 6+ / .NET Core
- Entity Framework Core
- Microsoft.EntityFrameworkCore.Migrations
- SQL Server (or other relational DB supporting schemas)
- C# 10 (nullable context disabled in this migration)
βοΈ How to Apply / Rollback Migration
Using the CLI:
-
Add Migration
dotnet ef migrations add AddMechanicTimesheet \ --project IrbyImaging.DbContext \ --startup-project IrbyImaging.Api -
Update Database
dotnet ef database update --project IrbyImaging.DbContext -
Rollback (to previous)
dotnet ef database update <PreviousMigrationName>
π Usage Examples
Inserting a Timesheet Entry
using var context = new IrbyDbContext();
var entry = new MechanicTimesheet
{
Id = Guid.NewGuid(),
EmployeeId = someEmployeeId,
SubmittedBy = "jdoe",
StartTime = "08:00 AM",
EndTime = "05:00 PM",
StartDate = DateTime.UtcNow.Date,
Job = "Engine Overhaul",
PayCode = "REG",
CostType = "Labor",
UnitNumber = "U-1234",
WorkOrder = "WO-5678",
Description = "Replaced pistons and sealed leaks.",
RegularTime = "8:00",
OverTime = "0:00",
Status = 0 // Pending
};
context.MechanicTimesheet.Add(entry);
context.SaveChanges();
Querying Entries
var approved = context.MechanicTimesheet
.Where(t => t.Status == 1)
.OrderBy(t => t.StartDate)
.ToList();
π Sample DbContext Entity
Although the migration defines the table, youβll typically have an entity class in your DbContext:
[Table("MechanicTimesheet", Schema = "irby")]
public class MechanicTimesheet
{
public Guid Id { get; set; }
public Guid EmployeeId { get; set; }
public string? SubmittedBy { get; set; }
public string? StartTime { get; set; }
public string? EndTime { get; set; }
public DateTime StartDate { get; set; }
public string? Job { get; set; }
public string? PayCode { get; set; }
public string? CostType { get; set; }
public string? UnitNumber { get; set; }
public string? WorkOrder { get; set; }
public string? Description { get; set; }
public string? RegularTime { get; set; }
public string? OverTime { get; set; }
public int Status { get; set; }
}
And in your DbContext:
public class IrbyDbContext : DbContext
{
public DbSet<MechanicTimesheet> MechanicTimesheet { get; set; }
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.HasDefaultSchema("irby");
modelBuilder.Entity<MechanicTimesheet>()
.HasKey(m => m.Id);
// Additional config...
}
}
π― Conclusion
This migration seamlessly integrates a Mechanic Timesheet feature into the existing database schema. By applying AddMechanicTimesheet, you empower your application to:
- Capture mechanic labor details.
- Support payroll calculations (regular vs. overtime).
- Maintain an audit trail of timesheet submissions.
Make sure to maintain matching entity definitions in your codebase, run migrations as part of your deployment pipeline, and handle the Status codes in your business logic (e.g., enum for better readability).
Happy coding! π οΈπ