Map category parent id self referencing table structure to EF Core entity
EF (and LINQ in general) has issues loading tree like data due to lack of recursive expression/CTE support.
But in case you want to load the whole tree (as opposed to filtered tree branch), there is a simple Include
based solution. All you need is a single Include
and then the EF navigation property fixup will do the work for you. And when you need to get only the root nodes as in your sample, the trick is to apply the filter after the query has been materialized (and navigation properties being fixed) by switching to LINQ to Objects context (using AsEnumerable()
as usual).
So the following should produce the desired result with single SQL query:
public override IEnumerable<Category> GetAll()
{
return Table
.AsEnumerable()
.Where(x => x.ParentId == null)
.ToList();
}