πŸ“˜ IrbyImaging.DbContext.Migrations – 20240917133401_AddColumnsTimesheet.cs


πŸ—‚ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Changes Introduced
    4.1. MechanicTimesheet Table
    4.2. Employee Table
  5. Migration Class Structure
  6. Up Method Details
  7. Down Method Details
  8. Database Impact & UML Diagram
  9. Usage Examples & CLI Commands
  10. Rollback & Revert
  11. 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 CrewId foreign-key for crew-assignment.

🎯 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 MigrationBuilder from EF Core and runs under the irby schema.


πŸ”‘ 3. Key Functional Areas

  1. Schema Evolution

    • Manage structural database changes via code-based migrations.
  2. Data Auditing

    • ApprovedBy & ReviewedBy fields record user actions.
  3. Soft Delete

    • IsDeleted flag prevents hard deletes, enabling data recovery.
  4. Entity Relationship

    • New CrewId GUID links to a (presumed) Crew table or entity.

✨ 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: AddColumnsTimesheet inherits from Migration
  • Methods:
    • Up β€” apply the changes
    • Down β€” 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.
  • schema and table specify 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
  1. MechanicTimesheet
    • Gains auditing and soft-delete flags.
  2. Employee
    • Now references a Crew through CrewId.

πŸš€ 8. Usage Examples & CLI Commands

  1. Add Migration

    dotnet ef migrations add AddColumnsTimesheet \
        --context IrbyDbContext \
        --output-dir IrbyImaging.DbContext/Migrations
    
  2. Update Database

    dotnet ef database update \
        --context IrbyDbContext
    
  3. 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 Down method 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.