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.

Does this answer your question? [loading a full hierarchy from a self referencing table with EntityFramework.Core](https://stackoverflow.com/questions/41827918/loading-a-full-hierarchy-from-a-self-referencing-table-with-entityframework-core) — Johnathan Barclay, Jun 08 '20 at 12:37
@JohnathanBarclay No, I'm trying to load a specific parent and its children and grandchildren to the levels they are present. — John, Jun 08 '20 at 14:45
Found a way to get the items recursively at: [Stackoverflow answer](https://stackoverflow.com/a/42680957/362261) — John, Jun 09 '20 at 12:25

Guru Stron · Accepted Answer · 2020-06-09T13:04:55.643

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());

Found a way to get the items recursively at: https://stackoverflow.com/a/42680957/362261 — John, Jun 09 '20 at 12:26
@John yes, it basically does what i written here in recursive manner, you can do that with simple while loop also. But it still makes one request per level. — Guru Stron, Jun 09 '20 at 12:35

Selecting a specific Task and its ChildTasks recursively from EF core

1 Answers1