Qfyilyi

I am trying to model something similar to a graph representation of a object where it's children are dependencies on which the parent depends.

Example here is the table structure.

Initative

Id  |  Name

 1  |  Init1

 2  |  Init2

 3  |  Init3

 4  |  Init4



InitiativeChildren

Id  |InitiativeId   InitiativeChildrenId

1   |    1         |         2

2   |    1         |         3

3   |    2         |         4



Categories

Id  | InitiativeId  | Name

1   |     1         | Init1Category1

2   |     1         | Init1Category2

3   |     1         | Init1Category3

4   |     2         | Init2Category1

5   |     2         | Init2Category2

6   |     2         | Init2Category3

7   |     3         | Init3Category1

8   |     3         | Init3Category2

Initiative Id = 1 depends on Initiative Id = 2, 3 where InitiativeId = 2 further depends on Initiative Id = 4

I want to write a query to pull a object like this for Initiative Id = 1

Initiative

{

Id : 1

Children = [ Initiative { 

          Id = 2, 

          Children = [ Initiative 

          {

            Id = 4,

            Children = 

          }]

          },

          Initiative { 

          Id = 3, 

          Children = 

          }]

}

i.e. I want the child initiative to be Initiative Objects which will again have child initiatives and so on.
I want the structure to be like this so that I can use depth first search to resolve the children objects before acting on that object.
The database structure I designed is

And the Entity framework query I have written is

var result = context.Initiatives

                    .Include(m => m.InitiativeChildrens)

                    .Include(m => m.Categories)

                    .Where(m => m.Id == 1)

                    .ToList();

What I get currently is that the the ChildrenInitiative has two children as it should but the Initiative to that child initiative is again the same Initiative with Id = 1 as opposed to a Initiative Object with ChildInitiative.ChildInitiativeId

Lets assume that this representation doesn't lead to modelling a cyclic graph for now