Eager loading seems to not work with recursive CTEs?

[Evengard]

Is it possible to use .Include/.ThenInclude, aka eager loading, for recursive CTEs? It seems that applying them on the query does absolutely nothing. Is it intended?

[sdanyliv]

Yes, it does nothing in current implementation. I have to find time for refactoring.

[sdanyliv]

Anyway better to show query.

[Evengard]

I will simplify it a bit. I have an hierarchical structure of objects, such as

public class Division
{
public Guid DivisionId {get; set;}
[ForeignKey("Parent")]
public Guid? ParentId {get; set;}
public virtual Division Parent {get; set;}
[InverseProperty("Parent")]
public virtual ICollection<Division> Children { get; set; }
[InverseProperty("Division")]
public virtual ICollection<Employee> Employees {get; set;}
}

public class Employee
{
public Guid EmployeeId {get; set;}
[ForeignKey("Division")]
public Guid DivisionId {get; set;}
public virtual Division Division {get; set;}
}

Now I need to get recursively all children for a given division, with all their employees. I use a CTE:

var result = await _context.GetCte<Division>(d => 
(from div in _context.Divisions
where div.DivisionId == divId
select div)
.Union(from cdiv in _context.Divisions
from pdiv in d
where cdiv.ParentId == pdiv.DivisionId))
.Include(d => d.Employees)
.Include(d => d.Parent) // This may not be needed, as we get the info from recursive query anyway?
.Include(d => d.Children) // This may not be needed, as we get the info from recursive query anyway?
.ToArrayAsyncLinqToDB();

I expect to have an array of divisions which all of them have populated Employees, Parent and Children navigational properties.

[Evengard]

Is it even possible at all with Linq2db to do eager loading (LoadWith) with recursive CTEs?

[sdanyliv]

I have corrected your query and it should work. Currently recursive CTE cannot return whole object but only projection.

class DivisionCte
{
    public int DivisionId;
    public int ParentId;
}

var divisionCte = _context.GetCte<DivisionCte>(d => 
    (
        from div in _context.Divisions
        where div.DivisionId == divId
        select new DivisionCte
        { 
            DivisionId = div.DivisionId
            ParentId = div.ParentId
        }
    )
    .Concat
    (
        from cdiv in _context.Divisions
        join pdiv in d on cdiv.ParentId equals pdiv.DivisionId
        select new DivisionCte
        {
            DivisionId = cdiv.DivisionId,
            ParentId = cdiv.ParentId
        }
    ));

var query = 
    from d in _context.Divisions.Include(d => d.Employees)
    join cte in divisionCte on d.DivisionId equals cte.DivisionId
    select d;

var result = await query.ToArrayAsyncLinqToDB();

[Evengard]

That's interesting, I never thought about joining a recursive CTE for some reason. Thanks, will try.

[sdanyliv]

Why not ;) If you start thinking from SQL query but not from object collections perspective - it is natural way. And surprise, you will get almost the same SQL as a LINQ query.

[Evengard]

Thanks, that actually works on my much complex query as well! I really should think more out of the box with SQL... xD