Selecting a specific Task and its ChildTasks recursively from EF core

Question

I have a TaskItem class that has child items of the same type:

public class TaskItem
{
    public Guid Id { get;set; }

    // Parent Task

    public TaskItem ParentTask { get; set; }

    public Guid? ParentTaskId { get; set; }

   //Child Tasks

    public ICollection<TaskItem > ChildTasks{ get; set; }
}

To get a Task and its subtasks recursively as a Flat list:

var taskList =  taskDBContext.TaskItems.Include(c => c.ChildTasks)
                .Where(x => x.Id == taskId)
                .SelectMany(x => x.ChildTasks)
                .ToList();

The issue is that I always get a single task though the task has many grandchildren at various levels. My scenario is loading a specific parent and its children and grandchildren as a single list.

Also, please let me know if this is a good design or do I have to change it.

Answer 1

EF Core will not fetch data for you recursively you either need to do it yourself for every level:

var secondLevelx = taskDBContext.TaskItems
    .Where(x => x.Id == taskId)
    .SelectMany(x => x.ChildTasks)
    .SelectMany(x => x.ChildTasks)
    .ToList();

Or :

var secondLevelx = taskDBContext.TaskItems
    .Where(x => taskList.Select(t=>t.Id).Contains(x.Id))
    .SelectMany(x => x.ChildTasks)
    .ToList();  

And repeat it while there are results.

Or write recursive sql query if your database supports it (for SQL Server for example you can use CTE's).

UPD

If you are ok with making a request per level (also AFAIK that there is limit how many parameters you can pass to your query) and you don't have cycles you can do something like this:

var taskList = new List<TaskItem>();
var toQuery = new List<Guid> {taskId};

do
{
    var current = taskDBContext.TaskItems
        .Where(x => toQuery.Contains(x.Id))
        .SelectMany(x => x.ChildTasks)
        .ToList();
    taskList.AddRange(current);
    toQuery = current.Select(x => x.Id).ToList();
} while (toQuery.Any());