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

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

CodePudding user response：

Better approach would be to add property nullable ParentTaskId, which is nothing but the task id of the parent task. I’m considering that task and sub task have same properties.

Otherwise create a separate class for sub task and add nullable parentTaskId to it as foreign key.

CodePudding user response：

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.