In c# app, how to connect foreign key of subtask table in SQL?

Question

I am trying to create a Task Management System using C# Web API & SQL Server.

Here is what I am trying to achieve:

• Users can send POST requests to create a Task (with optional sub-tasks).
• Users can send GET requests to retrieve a Task (displaying all sub-tasks in the response if they exist)

Here are some of my current files:

Task DTO:
public class TaskDto
{
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public DateTime StartDate { get; set; }
public DateTime FinishDate { get; set; }
public string Status { get; set; }
}

Task.cs:
public class Task
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Description { get; set; }
public DateTime StartDate { get; set; }
public DateTime FinishDate { get; set; }
public string Status { get; set; }
}

Subtask.cs:
public class SubTask
{
[Key]
public int Id { get; set; }
public string Name { get; set; }
public string Status { get; set; }
}

With the above code, I'm currently, able to POST & GET a Task, but I am struggling to add the Sub-Task part.

Can someone please advise what I need to do in order to be able to meet this requirement?

Thanks a lot in advance

Answer 1

I'm not sure how familiar with database relationships you are generally, but this article is a good starting point to learn how to set them up in Entity Framework. In this example Blog is equivalent to your Task, and Post is equal to your SubTask, so you can follow the same format as this code snippet:
https://learn.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#manual-configuration

This involves adding the list of sub-tasks to your task, and a reference to your parent task in your sub-task class. Then you'd define the HasOne/HasMany relationships in your DB context's OnModelCreating handler.

You'll want to make a SubTaskDto that matches the structure of your EF model, then add a list of them to your TaskDto. You'll then need to update your create/retrieve methods to map these sub-tasks to & from your EF models.

Answer 2

Create a new class project, use EF Power Tools to reverse engineer (assuming you have relations setup in the database) your database and examine the configuration for each table. Run some unit test to ensure all is working. Note this is a lot easier when using EF Core 5+