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

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

Your classes would link up like this…

public class Task
{        
 public int Id { get; set; }        
 public string Name { get; set; }        
 public string Description { get; set; }
 public virtual ICollection<SubTask> SubTask { get; set; }
}

public class SubTask
{        
 public int Id { get; set; }        
 public string Name { get; set; }        
 public string Description { get; set; }
 public int? TaskId { get; set; }
 public Task Task { get; set; }
}

You can see that Task has a collection of SubTask and SubTask has a reference back to Task with a Task property and a TaskId.

Update: Since you’re not using EF code-first, as I initially thought.

Your DTO could just have a list of SubTask, something like this:

public class TaskDto
    {        
     public int Id { get; set; }        
     public string Name { get; set; }        
     public string Description { get; set; }
     public virtual List<SubTask> SubTask { get; set; }
    }

Then, you could do something like:

var tasks = (from t in _db.Tasks
                        join st in _db.Sub Tasks on st.Id equals t.SubTaskId
                        select new TaskDto
                        {
                            Id = t.Id,
                            Name = t.Name,
                            Description = t.Description,
                             SubTask = st
                        });

This is all hand written, but it’ll be along these lines.