Support loading recursive relationships to any depth

[ghost]

I have table like this

ModelA

ID	ParentID
1	NULL
2	1
3	2
4	3
5	4

public partial class Model A
{
    public int ID {get;set;}
    public ParentID? {get;set}
    [ForeignKey("ID")]
    public ModelA Parent {get;set;}
}

I can get Parent navigation property populated by doing

var obj = _context.ModelA.Include(x=>x.Parent).Single(x=>x.ID == 5);

but the obj.Parent.Parent will be null unless I do

var obj = _context.ModelA.Include(x=>x.Parent).ThenInclude(x=>x.Parent))Single(x=>x.ID == 5);

the issue is for any entity, there could be X amount of nested navigation property until the actual ParentID is null. I cannot hard code the ThenInclude for derived type.

var obj = _context.ModelA.Include(x=>x.Parent).ThenInclude(x=>x.Parent))...(N amount of ThenInclude)...Single(x=>x.ID == N);

Also I do not wish to use lazy loading to solve this issue.

[smitpatel]

Can you write a SQL to load all data in single query?

[ghost]

Yes I can, but in my case i need to figure out, when given an ID, all its ancestors and navigate through the nav properties

[smitpatel]

@lfwinster - I am asking you to provide a SQL. If it cannot be expression in SQL then it cannot be done in LINQ either. The issue in linq/SQL both you face is, you don't know how many times you have to do join. If you have a clever way to do it in SQL, we can think about how to support it in linq.

[sdanyliv]

@smitpatel, it is easily doeable by recursive common table expressions (CTE), which are supported by many database engines.

with hierarchy_up (id, parent_id)
as
(
    select id, parent_id from model_a
    where id = 5
    union all
    select parent.id, parent.parent_id 
    from hierarchy_up h
    inner join model_a parent on parent.id = h.parent_id
)
select * from hierarchy_up

[ghost]

Thanks @sdanyliv !
your recursive CTE does the job. I assume for hierarchy questions, there will always be recursion/loop involved.

does that mean Lazy loading is not translating the LINQ into the recursive CTE that @sdanyliv mentioned but rather into multiple sql queries based on how the navigation properties are being consumed in the code?

for example, if i am only accessing the parent vs im accessing all the way to the original ancestor, LINQ will translate into different SQL

[ajcvickers]

Putting this on the backlog to consider implementing in the future.

@lfwinster Two workarounds for now are:

• Load all entities of type ModelA into the context; all parent/children navigation properties will be fixed up.
• Decide an acceptable depth to go to and add Include/ThenInclude/ThenInclude/etc. up to that depth.

[ghost]

Putting this on the backlog to consider implementing in the future.

@lfwinster Two workarounds for now are:

• Load all entities of type ModelA into the context; all parent/children navigation properties will be fixed up.
• Decide an acceptable depth to go to and add Include/ThenInclude/ThenInclude/etc. up to that depth.

Thanks @ajcvickers , I have considered taking the first work around until this is implemented.

[tnlthanzeel]

@ajcvickers , when will this be available?

[ajcvickers]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 6.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Please vote (👍) for this issue if it is important to you.

[tnlthanzeel]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 6.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Please vote (👍) for this issue if it is important to you.

noted! thanks for the reply

[AndriySvyryd]

Related to #365

[ajcvickers]

Note from triage: we do not plan to add direct support for this.