πŸ“ 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

  1. πŸ“Œ Overview & Purpose
  2. πŸš€ Dependencies & Technologies
  3. πŸ”‘ Key Functional Areas
  4. πŸ“ Detailed Schema Changes
  5. πŸ“Š Entity Relationship Diagram (UML)
  6. πŸ”§ Applying & Rolling Back Migrations
  7. πŸ› οΈ Sample Entity Classes & DbContext Integration
  8. πŸ” Sample Queries & Usage Examples
  9. βš™οΈ Helper Methods & Utilities
  10. 🚫 Error Handling & Return Types
  11. 🌐 API Endpoints Overview
  12. πŸ“¦ Third-party Integrations
  13. 🏁 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

  1. Table Creation
    • TimeEntryType lookup table.
    • TimeSheetEntry transactional table.
  2. Primary Keys & Constraints
    • GUID primary keys for both tables.
    • Foreign keys to Employee and TimeEntryType.
  3. Audit Fields & Soft Delete
    • IsDeleted, AddedBy, AddedDate, ModifiedBy, ModifiedDate.
  4. 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 reference x => x.Id instead of EmployeeId and TimeEntryTypeId. 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 via MigrationBuilder calls.


🚫 Error Handling & Return Types

  • EF Core migrations throw exceptions if the schema already exists or if constraints fail.
  • Ensure idempotency: Down() reverses Up().
  • 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:

  1. Review and correct FK definitions if needed.
  2. Build service layer & DTOs for business logic.
  3. Expose API endpoints for CRUD operations on time entries.
  4. Implement UI or reporting to surface time‐tracking data.

Happy coding! πŸš€