π IrbyImaging.DbContext/Migrations/20230706154136_NewTimeEntryTypeTables.cs
Migration: Add new tables for Time Entry Types and Time Sheet Entries
Timestamp: 2023β07β06 15:41:36
π Table of Contents
- π Overview & Purpose
- π Dependencies & Technologies
- π Key Functional Areas
- π Detailed Schema Changes
- π Entity Relationship Diagram (UML)
- π§ Applying & Rolling Back Migrations
- π οΈ Sample Entity Classes & DbContext Integration
- π Sample Queries & Usage Examples
- βοΈ Helper Methods & Utilities
- π« Error Handling & Return Types
- π API Endpoints Overview
- π¦ Third-party Integrations
- π Conclusion & Next Steps
π Overview & Purpose
This EF Core migration adds two new tables under the irby schema:
- TimeEntryType: Lookup table for various types of time entries (e.g., Regular, Overtime, PTO).
- TimeSheetEntry: Tracks daily hours worked by employees, linked to both an employee record and a timeβentry type.
Both tables include audit and soft-delete fields to support historical tracking and logical deletion.
Why?
- Provide structure for categorizing time entries.
- Enable flexible reporting on employee hours by type.
- Maintain data integrity with foreignβkey constraints.
- Support auditing (
AddedBy/ModifiedBy) and soft deletes (IsDeleted).
π Dependencies & Technologies
| Technology | Version / Package |
|---|---|
| .NET SDK | β₯ 6.0 |
| Entity Framework Core | Microsoft.EntityFrameworkCore β₯ 6.0 |
| SQL Server | Uses uniqueidentifier, datetime2, bit, decimal |
| C# | Language for migrations in MigrationBuilder |
Note: Make sure your project has the following NuGet packages installed:
- Microsoft.EntityFrameworkCore
- Microsoft.EntityFrameworkCore.SqlServer
- Microsoft.EntityFrameworkCore.Design
π Key Functional Areas
- Table Creation
TimeEntryTypelookup table.TimeSheetEntrytransactional table.
- Primary Keys & Constraints
- GUID primary keys for both tables.
- Foreign keys to
EmployeeandTimeEntryType.
- Audit Fields & Soft Delete
IsDeleted,AddedBy,AddedDate,ModifiedBy,ModifiedDate.
- Up/Down Methods
Up: Creates tables.Down: Drops tables.
π Detailed Schema Changes
4.1 TimeEntryType Table
| Column | Type | Nullable | Description |
|---|---|---|---|
| Id | uniqueidentifier | No | Primary key β TimeEntryType.Id |
| Name | nvarchar(max) | Yes | Descriptive name of the entry type |
| IsDeleted | bit | No | Soft-delete flag |
| AddedBy | nvarchar(max) | Yes | Username or service who created row |
| AddedDate | datetime2 | No | UTC timestamp of creation |
| ModifiedBy | nvarchar(max) | Yes | Username or service who modified |
| ModifiedDate | datetime2 | No | UTC timestamp of last modification |
migrationBuilder.CreateTable(
name: "TimeEntryType",
schema: "irby",
columns: table => new
{
Id = table.Column<Guid>("uniqueidentifier", nullable: false),
Name = table.Column<string>("nvarchar(max)", nullable: true),
IsDeleted = table.Column<bool>("bit", nullable: false),
AddedBy = table.Column<string>("nvarchar(max)", nullable: true),
AddedDate = table.Column<DateTime>("datetime2", nullable: false),
ModifiedBy = table.Column<string>("nvarchar(max)", nullable: true),
ModifiedDate = table.Column<DateTime>("datetime2", nullable: false)
},
constraints: table => table.PrimaryKey("PK_TimeEntryType", x => x.Id)
);
4.2 TimeSheetEntry Table
| Column | Type | Nullable | Description |
|---|---|---|---|
| Id | uniqueidentifier | No | Primary key β TimeSheetEntry.Id |
| EmployeeId | uniqueidentifier | Yes | FK β Employee.Id (manyβtoβone) |
| TimeEntryTypeId | uniqueidentifier | Yes | FK β TimeEntryType.Id (manyβtoβone) |
| DateWorked | Date | No | Date the work was performed |
| HoursWorked | decimal(18,2) | No | Hours logged (2 decimal precision) |
| IsDeleted | bit | No | Soft-delete flag |
| AddedBy | nvarchar(max) | Yes | Creator |
| AddedDate | datetime2 | No | Creation time |
| ModifiedBy | nvarchar(max) | Yes | Last modifier |
| ModifiedDate | datetime2 | No | Last modification time |
migrationBuilder.CreateTable(
name: "TimeSheetEntry",
schema: "irby",
columns: table => new
{
Id = table.Column<Guid>("uniqueidentifier", nullable: false),
EmployeeId = table.Column<Guid>("uniqueidentifier", nullable: true),
TimeEntryTypeId = table.Column<Guid>("uniqueidentifier", nullable: true),
DateWorked = table.Column<DateTime>("Date", nullable: false),
HoursWorked = table.Column<decimal>("decimal(18,2)", nullable: false),
IsDeleted = table.Column<bool>("bit", nullable: false),
AddedBy = table.Column<string>("nvarchar(max)", nullable: true),
AddedDate = table.Column<DateTime>("datetime2", nullable: false),
ModifiedBy = table.Column<string>("nvarchar(max)", nullable: true),
ModifiedDate = table.Column<DateTime>("datetime2", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_TimeSheetEntry", x => x.Id);
table.ForeignKey(
name: "FK_TimeSheetEntry_Employee_Id",
column: x => x.Id, // β οΈ Should likely reference x => x.EmployeeId
principalSchema: "irby",
principalTable: "Employee",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
table.ForeignKey(
name: "FK_TimeSheetEntry_TimeEntryType_Id",
column: x => x.Id, // β οΈ Should likely reference x => x.TimeEntryTypeId
principalSchema: "irby",
principalTable: "TimeEntryType",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
βοΈ NOTE
The foreign key definitions appear to referencex => x.Idinstead ofEmployeeIdandTimeEntryTypeId. You may wish to correct these to prevent accidental selfβjoins.
π Entity Relationship Diagram (UML)
ββββββββββββββββββ ββββββββββββββββββ
β Employee βββββββββββ TimeSheetEntry βββββββββββ
β (irby.Employee)β β (irby) β β
ββββββββββββββββββ ββββββββββββββββββ β
β² β² βΌ
β β βββββββββββββββββ
β FK EmployeeId β β TimeEntryType β
β βββ β(irby) β
β βββββββββββββββββ
βββββββββββββββββββ
βTimeEntryType β
β(irby) β
βββββββββββββββββββ
- Employee 1 β * TimeSheetEntry
- TimeEntryType 1 β * TimeSheetEntry
π§ Applying & Rolling Back Migrations
Apply Migration
# 1. Add the migration (already created here)
dotnet ef migrations add NewTimeEntryTypeTables \
--project IrbyImaging.DbContext \
--context IrbyDbContext
# 2. Update your database
dotnet ef database update \
--project IrbyImaging.DbContext \
--context IrbyDbContext
Rollback Migration
# Rollback to the previous migration
dotnet ef database update <PreviousMigrationName>
Or programmatically within Down():
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable("TimeSheetEntry", schema: "irby");
migrationBuilder.DropTable("TimeEntryType", schema: "irby");
}
π οΈ Sample Entity Classes & DbContext Integration
namespace IrbyImaging.Models
{
public class TimeEntryType
{
public Guid Id { get; set; }
public string Name { get; set; }
public bool IsDeleted { get; set; }
public string AddedBy { get; set; }
public DateTime AddedDate { get; set; }
public string ModifiedBy { get; set; }
public DateTime ModifiedDate { get; set; }
public ICollection<TimeSheetEntry> TimeSheetEntries { get; set; }
}
public class TimeSheetEntry
{
public Guid Id { get; set; }
public Guid? EmployeeId { get; set; }
public Employee Employee { get; set; }
public Guid? TimeEntryTypeId { get; set; }
public TimeEntryType TimeEntryType { get; set; }
public DateTime DateWorked { get; set; }
public decimal HoursWorked { get; set; }
public bool IsDeleted { get; set; }
public string AddedBy { get; set; }
public DateTime AddedDate { get; set; }
public string ModifiedBy { get; set; }
public DateTime ModifiedDate { get; set; }
}
}
// In DbContext.OnModelCreating:
modelBuilder.HasDefaultSchema("irby");
modelBuilder.Entity<TimeEntryType>()
.ToTable("TimeEntryType")
.HasKey(t => t.Id);
modelBuilder.Entity<TimeSheetEntry>()
.ToTable("TimeSheetEntry")
.HasKey(t => t.Id);
modelBuilder.Entity<TimeSheetEntry>()
.HasOne(e => e.Employee)
.WithMany(emp => emp.TimeSheetEntries)
.HasForeignKey(e => e.EmployeeId);
modelBuilder.Entity<TimeSheetEntry>()
.HasOne(e => e.TimeEntryType)
.WithMany(t => t.TimeSheetEntries)
.HasForeignKey(e => e.TimeEntryTypeId);
π Sample Queries & Usage Examples
1. Add a new Time Entry Type
var regularType = new TimeEntryType
{
Id = Guid.NewGuid(),
Name = "Regular",
AddedBy = "system",
AddedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
};
dbContext.TimeEntryTypes.Add(regularType);
dbContext.SaveChanges();
2. Log a TimeSheet Entry
var entry = new TimeSheetEntry
{
Id = Guid.NewGuid(),
EmployeeId = existingEmployeeId,
TimeEntryTypeId = regularType.Id,
DateWorked = DateTime.Today,
HoursWorked = 8.0m,
AddedBy = "john.doe",
AddedDate = DateTime.UtcNow,
ModifiedDate = DateTime.UtcNow
};
dbContext.TimeSheetEntries.Add(entry);
dbContext.SaveChanges();
3. Query Hours by Employee & Type
var report = dbContext.TimeSheetEntries
.Where(e => !e.IsDeleted &&
e.EmployeeId == employeeId &&
e.DateWorked.Month == 7)
.GroupBy(e => e.TimeEntryType.Name)
.Select(g => new {
Type = g.Key,
TotalHours = g.Sum(x => x.HoursWorked)
})
.ToList();
βοΈ Helper Methods & Utilities
None in this migration.
All schema changes are defined viaMigrationBuildercalls.
π« Error Handling & Return Types
- EF Core migrations throw exceptions if the schema already exists or if constraints fail.
- Ensure idempotency:
Down()reversesUp(). - No custom return types; EF handles SQL command execution.
π API Endpoints Overview
Not applicable
This file solely manages database schema. Actual CRUD endpoints reside in the Web API project.
π¦ Third-party Integrations
- Entity Framework Core: Migration scaffolding & SQL generation.
- SQL Server: Target database engine.
π Conclusion & Next Steps
π Success! You've now added robust tables to manage time-entry types and timesheet logs:
- Audit-ready with
AddedBy/ModifiedBy. - Soft-delete flags via
IsDeleted. - Relational integrity via GUID FKs.
Next Steps:
- Review and correct FK definitions if needed.
- Build service layer & DTOs for business logic.
- Expose API endpoints for CRUD operations on time entries.
- Implement UI or reporting to surface timeβtracking data.
Happy coding! π