📑 Documentation: 20230201043118_LytxTemplateNewColumnsAndVehicleTable.cs
Part of the
IrbyImaging.DbContext.Migrationsnamespace
Adds two new tables (LytxTemplate&Vehicle) under theirbyschema.
🗂️ Table of Contents
- Overview & Purpose
- Dependencies & Technologies
- Key Functional Areas
- Data Models / Schema Definition
- Migration Methods
- UML / ER Diagram
- Usage Examples
- Best Practices & Notes
- 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
-
Schema Creation
- Adds
LytxTemplatewith driver/vehicle/shift columns. - Adds
Vehicleas a reference table.
- Adds
-
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.VehicleIdandVehicle.VehicleId(or better: referencing the primary keyId). - Indexing: If you query by
VehicleIdorDriverIdoften, add non-clustered indexes for performance. - Validation: Ensure
ShiftStart < ShiftEndat 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.