📜 HelpDeskCreate Migration Documentation


Table of Contents

  1. Overview & Purpose
  2. Dependencies & Technologies
  3. Data Models & Schema
  4. Migration Structure
  5. Key Functional Areas
  6. UML Entity Diagram
  7. Usage & Execution
  8. Sample Code Blocks
  9. Error Handling & Return Types
  10. Conclusion

1. Overview & Purpose

File: 20220427163827_HelpDeskCreate.cs
Assembly: IrbyImaging.DbContext
Author: kvamshi
Created: 05-17-2022
Last Modified: 05-17-2022

This EF Core migration adds the initial “HelpDesk” schema to the database, including tables for ✉️ tickets, 🛠️ updates, ❓ issues, and 🗂️ request options. It defines columns, data types, primary keys, and a foreign-key relationship.


2. Dependencies & Technologies

Technology Version / Notes
.NET Core / .NET 5+ EF Core Migrations
Microsoft.EntityFrameworkCore.Migrations Migration, MigrationBuilder
Database SQL Server (via datetime2, uniqueidentifier)

No external third‑party integrations beyond EF Core.


3. Data Models & Schema

This migration defines four tables under schema irby:

Table Name Purpose
Issue Stores individual help‑desk issues (questions/bugs).
RequestOption Defines selectable request items and their cost/type.
Ticket Main help‑desk ticket record capturing customer input.
Update Tracks status/comments linked to a Ticket.

3.1. Issue

Column Type Nullable Description
Id uniqueidentifier No Primary key GUID
Description nvarchar(max) Yes Free‑text issue description

3.2. RequestOption

Column Type Nullable Description
Id uniqueidentifier No Primary key GUID
Type int No Enum representing option category
Item nvarchar(max) Yes Label/text of the option
Cost nvarchar(max) Yes Cost or pricing string

3.3. Ticket

Column Type Nullable Description
Id uniqueidentifier No Primary key GUID
CustomerName nvarchar(max) Yes Full name
CustomerUsername nvarchar(max) Yes Login/username
ResponseEmail nvarchar(max) Yes Email for replies
CallBackNumber nvarchar(max) Yes Phone number
CreatedOn datetime2 No Ticket creation timestamp
CreatedBy nvarchar(max) Yes Internal user who created the ticket
Description nvarchar(max) Yes Customer’s initial description
RequestedJson nvarchar(max) Yes JSON payload of requested options/details
EmployeeAndJobDetails nvarchar(max) Yes Internal notes or job assignment details
ShippingAddress nvarchar(max) Yes Postal address if equipment shipping needed
Status int No Enum for ticket lifecycle status
Type int No Enum for ticket category/type

3.4. Update

Column Type Nullable Description
Id uniqueidentifier No Primary key GUID
Timestamp datetime2 No When the update was applied
Description nvarchar(max) Yes Update comments or description
RequestedStatusJson nvarchar(max) Yes JSON representing status changes
UpdatedBy nvarchar(max) Yes Internal user who performed the update
TicketId uniqueidentifier No Foreign key → Ticket(Id)

4. Migration Structure

public partial class HelpDeskCreate : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        // Create tables Issue, RequestOption, Ticket, Update
        migrationBuilder.CreateTable(...);
        // Add foreign key for Update → Ticket
        migrationBuilder.CreateIndex(...);
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        // Drop in reverse order: Issue, RequestOption, Update, Ticket
        migrationBuilder.DropTable(...);
    }
}
  • Up(): Applies schema changes (CREATE TABLE, FKs, INDEXES).
  • Down(): Rolls back (DROP TABLE).

5. Key Functional Areas

  1. Table Creation
    • migrationBuilder.CreateTable(...) for each entity.
  2. Primary Keys
    • Defined via table.PrimaryKey("PK_<Table>", x => x.Id).
  3. Foreign Key Relationship
    • Update.TicketId → Ticket.Id with Cascade delete.
  4. Indexing
    • Index on Update.TicketId for query performance.

6. UML Entity Diagram

erDiagram
    TICKET {
        GUID Id PK
        DateTime CreatedOn
        int Status
        int Type
        string CustomerName
        string CustomerUsername
        string ResponseEmail
        string CallBackNumber
        string Description
        string RequestedJson
        string EmployeeAndJobDetails
        string ShippingAddress
    }
    UPDATE {
        GUID Id PK
        DateTime Timestamp
        string Description
        string RequestedStatusJson
        string UpdatedBy
        GUID TicketId FK
    }
    ISSUE {
        GUID Id PK
        string Description
    }
    REQUESTOPTION {
        GUID Id PK
        int Type
        string Item
        string Cost
    }
    TICKET ||--o{ UPDATE : "has"

7. Usage & Execution

  1. Add Migration
    dotnet ef migrations add HelpDeskCreate --project IrbyImaging.DbContext
    
  2. Apply to Database
    dotnet ef database update --project IrbyImaging.DbContext
    
  3. Verify
    • Check irby.Issue, irby.RequestOption, irby.Ticket, irby.Update in your SQL Server.

8. Sample Code Blocks

8.1. Up Method Snippet

migrationBuilder.CreateTable(
    name: "Ticket",
    schema: "irby",
    columns: table => new
    {
        Id = table.Column<Guid>(type: "uniqueidentifier", nullable: false),
        CustomerName = table.Column<string>(type: "nvarchar(max)", nullable: true),
        // … other columns …
        Status = table.Column<int>(type: "int", nullable: false),
        Type = table.Column<int>(type: "int", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Ticket", x => x.Id);
    });

8.2. Down Method Snippet

migrationBuilder.DropTable(name: "Update", schema: "irby");
migrationBuilder.DropTable(name: "Ticket", schema: "irby");
migrationBuilder.DropTable(name: "Issue", schema: "irby");
migrationBuilder.DropTable(name: "RequestOption", schema: "irby");

9. Error Handling & Return Types

  • Error Handling: EF Core migrations throw exceptions if SQL fails (e.g., naming conflicts, missing schema).
  • Return Types: Both Up() and Down() are void. All work done via MigrationBuilder operations.

10. Conclusion

🎉 This migration bootstraps a HelpDesk module in your database.

  • Next Steps:
    • Create corresponding EF Core entities and DbSet<T> properties.
    • Scaffold or hand-write repository/repo-pattern to interact with these tables.
    • Build service and API layers for ticket creation, status updates, and reporting.

With this foundation, you have a robust schema to manage customer support tickets, track their life cycle, and configure request options. Happy coding! 🚀