Assignment: EF Core Database-First
MVC Application
You have been provided with an existing SQLite database containing a Help Desk system.
Your task is to build an [Link] Core MVC application that works with this existing
database using Entity Framework Core.
Scenario
You have been provided with an existing SQLite database: [Link].
The database already contains the complete Help Desk system, including its tables,
relationships, keys, and data.
Build an [Link] Core MVC application that works with the supplied database using
Entity Framework Core.
The database is the source of truth.
You are responsible for manually translating the database structure into an EF Core
model.
1. Provided Database
You will receive [Link].
The database already contains data.
Use the supplied database as provided.
Do not create another database.
Do not create the tables yourself.
Do not modify the supplied database.
Do not add seed data.
Do not remove existing data.
Do not use migrations to create the database.
2. Create the MVC Application
Create an [Link] Core MVC application using [Link] Core MVC, Entity Framework
Core, SQLite, Dependency Injection, Razor Views, and LINQ.
3. Manually Create the EF Core Model
Do not scaffold the database.
Manually create the EF Core entity classes based on your examination of [Link].
For every relevant database table, create the corresponding C# entity.
Your entity classes must correctly represent the database.
This includes, where applicable: primary keys, foreign keys, nullable fields, navigation
properties, collection navigation properties, composite keys, many-to-many
relationships, and self-referencing relationships.
4. Create the DbContext
Create your own EF Core DbContext.
The context must represent the supplied database.
Configure the model appropriately using EF Core configuration.
Where necessary, use OnModelCreating to represent database relationships that cannot
be adequately represented through conventions.
The DbContext must connect to [Link].
5. Database Investigation
Create [Link].
After inspecting the supplied database and your EF Core model, document the tables
discovered, primary keys, foreign keys, one-to-many relationships, many-to-many
relationships, self-referencing relationships, optional relationships, composite primary
keys, and nullable columns.
The information must come from your own investigation of the supplied database.
6. Manual Model Mapping
Your C# model must match the supplied database.
You must determine how tables, keys, relationships, and nullable values should be
represented in EF Core based on the database.
7. Dependency Injection
Register the EF Core DbContext using [Link] Core's built-in Dependency Injection.
Controllers must receive the context through constructor injection.
Do not manually instantiate the context inside controllers.
8. MVC Application
Create pages that allow the user to browse the Help Desk system.
Departments: display Name, Description, Active status, and Employee count.
Employees: display Full name, Email, Job title, Department, and Active status.
Teams: display Team name, Department, and Team members.
Customers: display Company, Contact name, Email, Phone, and Active status.
Tickets: display Ticket ID, Subject, Customer, Category, Priority, Status, Created date,
and Due date.
9. Ticket Details
Create a detailed Ticket page.
Display Ticket, Customer, Category, Priority, Status, Assignments, Comments, Tags, and
Attachments.
The information must be retrieved from the existing database through EF Core
relationships.
10. Required Queries
Employee Workload: display every active employee with Employee, Department, and
Unresolved Ticket Count. Employees with zero unresolved tickets must still appear.
Department Workload: display Department, Employee Count, and Unresolved Ticket
Count. Departments with zero unresolved tickets must still appear.
Unassigned Tickets: display tickets with no active assignment, including Ticket, Subject,
Customer, Priority, Status, and Created At.
Multiple-Assignee Tickets: display tickets with more than one active assignee, including
Ticket, Subject, Number of Active Assignees, and Assignees.
Primary Assignee: display Ticket, Subject, and Primary Assignee. If there is no primary
assignee, display Unassigned.
Category Hierarchy: display Category and Parent Category. Root categories must also be
displayed.
11. Important Restriction: No Scaffolding
Do not use EF Core database scaffolding.
Do not use scaffolding to generate entity classes, DbContext, relationships, or
configuration.
The purpose of this assignment is to determine whether you can inspect an existing
relational database and manually represent it using EF Core.
12. Important Restriction: No Migrations
Do not use Add-Migration, Update-Database, or equivalent migration commands to
create or modify the database.
The database already exists.
Your application must adapt to the database, not the other way around.
13. GitHub Submission
Submit the complete project through GitHub.
The repository must contain Controllers/, Models/, Views/, Data/, wwwroot/,
[Link], [Link], [Link], [Link], and the project file.
The repository must be runnable after cloning.
14. README
[Link] must contain Project Name, .NET Version, EF Core Version, NuGet
Packages, Database Location, and How to Run.
15. Live Defense
Your repository will be reviewed and you will demonstrate the application.
You may be asked to identify a database table represented by an entity; explain why a
property has a particular type; identify a primary key or foreign key; explain navigation
and collection navigation properties; explain a composite key, many-to-many
relationship, self-referencing relationship, or nullable property; modify an EF Core
query; add a filter; add another related entity to an existing view; explain your
DbContext and OnModelCreating; explain how Dependency Injection provides the
context; or diagnose an incorrect relationship mapping.
You may also be given a small change to implement during the defense.
16. AI / External Assistance
You may use documentation to look up syntax and API usage.
You are responsible for the database model you submit.
The entity classes, relationships, and EF Core configuration must be implemented by
you.
During the live defense, you must be able to explain your implementation and make
changes to it.
If you cannot explain how your manually created entities correspond to the supplied
database, your implementation will be considered incomplete regardless of whether the
application runs.
The Actual Exercise
PROVIDED: [Link]
Inspect the database.
Manually create C# entity classes.
Manually configure EF Core relationships.
Create the DbContext.
Register it through Dependency Injection.
Build MVC controllers and Razor views.
Query the supplied SQLite database through EF Core.