EF Core self joined ICollection query

Question

I have a problem with querying data from a self reference datatable having an ICollection.

Here are my table structures:

public class SoqHeading 
{
        public int Id { get; set; }
        
        public string ItemNo { get; set; } 
        public string Heading { get; set; }
        
        public SoqRevision SoqRevision_NP { get; set; }

        public ICollection<SoqItem> Items { get; set; }

        public int? ParentSoqHeadingId { get; set; }
        public SoqHeading Parent_NP { get; set; }

        public ICollection<SoqHeading> Children_NP { get; set; }
}

public class SoqItem 
{
    public int Id { get; set; }
    public SoqPreliminaryOrContract_enum PreliminaryOrContract { get; set; }
    
     
    public int SoqHeadingId { get; set; }
    
    public SoqHeading SoqHeading_NP { get; set; }
}

I have a self referenced relation in the SoqHeading table which is 1 to many. The SoqHeading table can have zero or many SoqItems.

This is how the data is in my database

what i want is to query all from the root node of the SoqHeading table with its children and the items.

like this

 {
    "ItemNo": "C",
    "Items": [
      {
        "ItemNo": "c-1",
      },
      {
        "ItemNo": "c-2",
      }
    ],
    "Children_NP": [
      {
        "ItemNo": "C.1",
        "Children_NP": [
          {
            "ItemNo": "C.1.1",
            "Items": [
              {
                "ItemNo": "c.1.1-1",
              },
              {
                "ItemNo": "c.1.1-2",
              }
            ],
            "Children_NP": [],
          },
          {
            "ItemNo": "C.1.2",
            "Items": [
              {
                "ItemNo": "c.1.2-1",
              },
              {
                "ItemNo": "c.1.2-2",
              }
            ],
            "Children_NP": [],
          }
        ],
      },
      {
        "ItemNo": "C.2",
        "Children_NP": [],
      }
    ]
  }

But currently i get data from the query like this

[
  {
    "ItemNo": "C"
  },
  {
    "ItemNo": "C.1.2",
  }
]

where C.1.2 has to be inside C, but it not. C and C.1.2 are in the same level which i dont wont to have produced by the following query

var entity = await _context.SoqHeadings
                            .Include(i=>i.Items)
                            .Include(i => i.Children_NP)
                            .Where(w => w.SoqRevision_NP.ProjectId == 10)
                            .ToListAsync();

please help

Answer 1

so it seems to be EFcore has no support to included the children on the first child node https://github.com/dotnet/efcore/issues/14144

so the work around is something like this here https://khalidabuhakmeh.com/recursive-data-with-entity-framework-core-and-sql-server