📑 Documentation: 20230201043118_LytxTemplateNewColumnsAndVehicleTable.cs

Part of the IrbyImaging.DbContext.Migrations namespace
Adds two new tables (LytxTemplate & Vehicle) under the irby schema.


🗂️ Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Key Functional Areas
  4. Data Models / Schema Definition
  5. Migration Methods
  6. UML / ER Diagram
  7. Usage Examples
  8. Best Practices & Notes
  9. Conclusion

🔍 Overview & Purpose

This Entity Framework Core migration introduces two new tables under the irby schema:

  • LytxTemplate: Captures event or shift data for drivers and vehicles.
  • Vehicle: A simple lookup table for vehicles.

Why This Migration?

  • 🛠️ Report Generation: Store raw data needed for Lytx safety/event templates.
  • 🚗 Vehicle Management: Provide a master list of vehicles for reporting and joins.
  • 🔄 Schema Evolution: Maintain versioned changes to the database without manual scripts.

🧰 Dependencies & Technologies

Technology Role
.NET / C# Language & runtime
Entity Framework Core ORM to manage database schema & migrations
Microsoft SQL Server Target RDBMS (via datetime2, uniqueidentifier, nvarchar)
Migrations API MigrationBuilder for schema manipulation

🎯 Key Functional Areas

  1. Schema Creation

    • Adds LytxTemplate with driver/vehicle/shift columns.
    • Adds Vehicle as a reference table.
  2. Schema Rollback

    • Drops both tables if the migration is reverted.

🗄️ Data Models / Schema Definition

1. Table: irby.LytxTemplate

Column Name Data Type Nullable Description
Id uniqueidentifier No Primary Key
VehicleId nvarchar(max) Yes External vehicle code
DriverId nvarchar(max) Yes External driver ID
FirstName nvarchar(max) Yes Driver first name
LastName nvarchar(max) Yes Driver last name
ShiftStart datetime2 No Shift start timestamp
ShiftEnd datetime2 No Shift end timestamp
ProcessingDate datetime2 No Date when record was processed/loaded

🔑 Primary Key: Id


2. Table: irby.Vehicle

Column Name Data Type Nullable Description
Id uniqueidentifier No Primary Key
VehicleId nvarchar(max) Yes Vehicle external code/key

🔑 Primary Key: Id


🏗️ Migration Methods

Below are the overridden methods in the Migration class.

Up(MigrationBuilder)

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.CreateTable(
        name: "LytxTemplate",
        schema: "irby",
        columns: table => new
        {
            Id             = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            VehicleId      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            DriverId       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            FirstName      = table.Column<string>(type: "nvarchar(max)", nullable: true),
            LastName       = table.Column<string>(type: "nvarchar(max)", nullable: true),
            ShiftStart     = table.Column<DateTime>(type: "datetime2", nullable: false),
            ShiftEnd       = table.Column<DateTime>(type: "datetime2", nullable: false),
            ProcessingDate = table.Column<DateTime>(type: "datetime2", nullable: false)
        },
        constraints: table => {
            table.PrimaryKey("PK_LytxTemplate", x => x.Id);
        });

    migrationBuilder.CreateTable(
        name: "Vehicle",
        schema: "irby",
        columns: table => new
        {
            Id        = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
            VehicleId = table.Column<string>(type: "nvarchar(max)", nullable: true)
        },
        constraints: table => {
            table.PrimaryKey("PK_Vehicle", x => x.Id);
        });
}

Down(MigrationBuilder)

protected override void Down(MigrationBuilder migrationBuilder)
{
    migrationBuilder.DropTable(
        name: "LytxTemplate",
        schema: "irby");

    migrationBuilder.DropTable(
        name: "Vehicle",
        schema: "irby");
}

📊 UML / ER Diagram

classDiagram
    class LytxTemplate {
        +Guid Id
        +string VehicleId
        +string DriverId
        +string FirstName
        +string LastName
        +DateTime ShiftStart
        +DateTime ShiftEnd
        +DateTime ProcessingDate
    }
    class Vehicle {
        +Guid Id
        +string VehicleId
    }
    LytxTemplate --> Vehicle : references? (no FK defined)

💡 Note: No foreign key constraint is enforced in this migration. You may add one later for referential integrity.


💻 Usage Examples

1. Applying the Migration

# From your project root:
dotnet ef migrations add LytxTemplateNewColumnsAndVehicleTable
dotnet ef database update

2. Querying with Your DbContext

public class IrbyContext : DbContext
{
    public DbSet<LytxTemplate> LytxTemplates { get; set; }
    public DbSet<Vehicle> Vehicles { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.HasDefaultSchema("irby");
        modelBuilder.Entity<LytxTemplate>().ToTable("LytxTemplate");
        modelBuilder.Entity<Vehicle>().ToTable("Vehicle");
    }
}

// Example usage:
using(var ctx = new IrbyContext())
{
    // Add a Vehicle
    ctx.Vehicles.Add(new Vehicle {
        Id        = Guid.NewGuid(),
        VehicleId = "TRK-1234"
    });
    ctx.SaveChanges();

    // Add a LytxTemplate record
    ctx.LytxTemplates.Add(new LytxTemplate {
        Id             = Guid.NewGuid(),
        VehicleId      = "TRK-1234",
        DriverId       = "DRV-5678",
        FirstName      = "Alice",
        LastName       = "Smith",
        ShiftStart     = DateTime.UtcNow.AddHours(-8),
        ShiftEnd       = DateTime.UtcNow,
        ProcessingDate = DateTime.UtcNow
    });
    ctx.SaveChanges();
}

⚙️ Best Practices & Notes

  • Foreign Keys: Consider adding an explicit FK constraint between LytxTemplate.VehicleId and Vehicle.VehicleId (or better: referencing the primary key Id).
  • Indexing: If you query by VehicleId or DriverId often, add non-clustered indexes for performance.
  • Validation: Ensure ShiftStart < ShiftEnd at the application level or add a CHECK constraint.

🏁 Conclusion

This migration is a foundational step toward integrating Lytx data templates into your imaging/reporting platform. By creating dedicated tables for both templates and vehicles, you establish a clear, extensible schema that can grow with new reporting requirements. Apply the migration, seed your initial data, and you're ready to start capturing shift-based telemetry in your .NET application! 🎉


Document generated on 2023-02-01 by the IrbyImaging migration tooling.