π IrbyImaging.DbContext.Migrations β 20240917133401_AddColumnsTimesheet.cs
π Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Changes Introduced
4.1. MechanicTimesheet Table
4.2. Employee Table - Migration Class Structure
- Up Method Details
- Down Method Details
- Database Impact & UML Diagram
- Usage Examples & CLI Commands
- Rollback & Revert
- Conclusion
π 1. Overview & Purpose
This EF Core migration AddColumnsTimesheet augments two existing tables within the irby schema:
MechanicTimesheet- Adds audit and soft-delete capabilities.
Employee- Introduces a
CrewIdforeign-key for crew-assignment.
- Introduces a
π― Goal:
- Track who approved or reviewed a timesheet.
- Mark timesheets as deleted without physically removing them.
- Associate employees with crews via a new GUID column.
βοΈ 2. Dependencies & Technologies
| Technology | Version / Purpose |
|---|---|
| .NET 6+ / .NET Core | Runtime framework |
| Entity Framework Core | ORM; migrations & schema management |
| SQL Server (nvarchar, bit, uniqueidentifier) | Database engine and column types |
Note: The migration uses
MigrationBuilderfrom EF Core and runs under theirbyschema.
π 3. Key Functional Areas
-
Schema Evolution
- Manage structural database changes via code-based migrations.
-
Data Auditing
ApprovedBy&ReviewedByfields record user actions.
-
Soft Delete
IsDeletedflag prevents hard deletes, enabling data recovery.
-
Entity Relationship
- New
CrewIdGUID links to a (presumed)Crewtable or entity.
- New
β¨ 4. Changes Introduced
4.1. MechanicTimesheet Table
| Column Name | Data Type | Nullable | Default | Description |
|---|---|---|---|---|
| ApprovedBy | nvarchar(max) |
Yes | NULL |
User who approved the timesheet. |
| ReviewedBy | nvarchar(max) |
Yes | NULL |
User who reviewed the timesheet. |
| IsDeleted | bit |
No | false |
Soft-delete marker (0 = Active, 1 = Deleted). |
4.2. Employee Table
| Column Name | Data Type | Nullable | Default | Description |
|---|---|---|---|---|
| CrewId | uniqueidentifier |
Yes | NULL |
FK to the employeeβs assigned crew (GUID). |
π Migration Class Structure
using System;
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace IrbyImaging.DbContext.Migrations
{
/// <inheritdoc />
public partial class AddColumnsTimesheet : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
// Adds columns to MechanicTimesheet & Employee
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
// Rolls back the above additions
}
}
}
- Namespace:
IrbyImaging.DbContext.Migrations - Class:
AddColumnsTimesheetinherits fromMigration - Methods:
Upβ apply the changesDownβ revert the changes
π§ 5. Up Method Details
In the Up method, four new columns are added:
protected override void Up(MigrationBuilder migrationBuilder)
{
// 1. Add ApprovedBy
migrationBuilder.AddColumn<string>(
name: "ApprovedBy",
schema: "irby",
table: "MechanicTimesheet",
type: "nvarchar(max)",
nullable: true);
// 2. Add IsDeleted
migrationBuilder.AddColumn<bool>(
name: "IsDeleted",
schema: "irby",
table: "MechanicTimesheet",
type: "bit",
nullable: false,
defaultValue: false);
// 3. Add ReviewedBy
migrationBuilder.AddColumn<string>(
name: "ReviewedBy",
schema: "irby",
table: "MechanicTimesheet",
type: "nvarchar(max)",
nullable: true);
// 4. Add CrewId to Employee
migrationBuilder.AddColumn<Guid>(
name: "CrewId",
schema: "irby",
table: "Employee",
type: "uniqueidentifier",
nullable: true);
}
AddColumn<T>generic defines the CLR type.schemaandtablespecify exactly where the column lands.- Default values ensure existing rows get a sane initial state.
π 6. Down Method Details
The Down method cleanly removes the four added columns:
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropColumn(
name: "ApprovedBy",
schema: "irby",
table: "MechanicTimesheet");
migrationBuilder.DropColumn(
name: "IsDeleted",
schema: "irby",
table: "MechanicTimesheet");
migrationBuilder.DropColumn(
name: "ReviewedBy",
schema: "irby",
table: "MechanicTimesheet");
migrationBuilder.DropColumn(
name: "CrewId",
schema: "irby",
table: "Employee");
}
- Idempotency: Ensures the migration can be rolled back without side effects.
π 7. Database Impact & UML Diagram
Mermaid class diagram illustrating the schema changes:
classDiagram
class MechanicTimesheet {
+string ApprovedBy
+string ReviewedBy
+bool IsDeleted = false
}
class Employee {
+Guid? CrewId
}
%% Relationships (dashed because Crew table not shown)
Employee ..> Crew : CrewId
- MechanicTimesheet
- Gains auditing and soft-delete flags.
- Employee
- Now references a
CrewthroughCrewId.
- Now references a
π 8. Usage Examples & CLI Commands
-
Add Migration
dotnet ef migrations add AddColumnsTimesheet \ --context IrbyDbContext \ --output-dir IrbyImaging.DbContext/Migrations -
Update Database
dotnet ef database update \ --context IrbyDbContext -
Verify
- Inspect SQL Server Management Studio (or Azure Data Studio)
- Confirm new columns in
irby.MechanicTimesheet&irby.Employee.
βοΈ 9. Rollback & Revert
To undo this migration:
dotnet ef database update LastGoodMigrationName \
--context IrbyDbContext
Or use package manager console:
Update-Database -Context IrbyDbContext LastGoodMigrationName
The
Downmethod will execute, dropping the newly added columns.
π 10. Conclusion
This migration adds crucial auditing, soft-delete functionality, and crew-assignment support to your database schema. By following the Up and Down implementations, you can safely apply or revert these changes, keeping your applicationβs data layer in line with evolving business requirements.
β Happy coding with EF Core Migrations! Should you have any questions, feel free to reach out.