Recursive linq to get infinite children

Question

I'm new to linq and am trying to find a way to return the parent and a List (children) and all those children, for a given parent. I have a Locations table with the fields LocationID, ParentLocationID, LocationName. A sample of the data could look like this:

ABC
 --ABC1
 --ABC2
 ----DEF1
 ----DEF2
 ----DEF3
 --ABC3
 ----DEF4
 ------GHI1
 ------GHI2
 ----DEF5
 --ABC4
 ...

Given that data, if the selected parent is 'ABC', I want to return all the rows because all children are under it. However, if I selected the parent 'ABC3', it would return DEF4, GHI1, GHI2, DEF5.

I've looked into these SO questions but am still confused on how to create this statement(s):

Find all descendants in self-referencing (parent-child) hierarchical tree

LINQ to SQL - Self Join Child to Parent (Same Table)

Here's what I've tried but am getting an error:

public ActionResult Index()
{
    var loc = GetChild(346);
    return View(loc);
}

public IEnumerable<Location> GetChild(int id)
{
    DBEntities db = new DBEntities();
            
    var locations =  db.Locations.Where(x => x.ParentLocationID == id || x.LocationID == id).Union(
                        db.Locations.Where(x => x.ParentLocationID == id).SelectMany(y => GetChild(y.LocationID)));
    return locations;
}

And the Location class is:

public class Location
{
    public Location();

    public virtual ICollection<SimSystem> SimSystems { get; set; }
    public virtual ICollection<LocationAddress> LocationAddresses { get; set; }
    public virtual LocationType LocationType { get; set; }
    public virtual ICollection<CustomerUser> CustomerUsers { get; set; }
    public virtual ICollection<AppUserLocation> AppUserLocations { get; set; }
    public int LocationTypeID { get; set; }
    public DateTime? InstallDate { get; set; }
    public string AltPhone { get; set; }
    public string OfficePhone { get; set; }
    public int? PrimaryAddressID { get; set; }
    public int? ParentLocationID { get; set; }
    public string LocationName { get; set; }
    public string LocationName2 { get; set; }
    public int LocationID { get; set; }
    public virtual Address Address { get; set; }
}

The error is:

LINQ to Entities does not recognize the method and this method cannot be translated into a store expression.

Answer 1

So while trying to figure out this myself and failing I did some research. Most of the articles I came across said this just isn't possible in a single query with Linq.

One thing you can do is gather up all the IDs of the objects that will be in your hierarchy. Then grab only the Objects you will need from the and then use that to build a tree in memory with the objects. Grabbing the IDs should be very quick, especially with indexes.

e.g. List<int> locationIDs = db.Locations.Where(x => x.ParentLocationID == id || x.LocationID == id).Select(x => x.LocationID).ToList();

then you can

var locations = db.Locations.Where(x => locationIDs.Contains(x.LocationID);

And then use a normal recursive method to build the tree. However I would also like to mention an extension method I found that supposedly does this for you. The description looks very detailed with infographics and such. http://www.scip.be/index.php?Page=ArticlesNET23

Answer 2

can you try this..

 public IEnumerable<Location> GetChild(int id)
        {
            DBEntities db = new DBEntities();
        var locations = db.Locations.Where(x => x.ParentLocationID == id || x.LocationID == id).ToList();

    var child = locations.AsEnumerable().Union(
                                db.Locations.AsEnumerable().Where(x => x.ParentLocationID == id).SelectMany(y => GetChild(y.LocationId))).ToList();
            return child;
        }

Answer 3

First of all you would want to let the DBEntities out of the recursive method. You will end up making too many connections on the Database and you will end up with memory leaks.
As for the exception. It states that the call to GetChild(int); cannot be translated to linq to sql expression. If you have the sql that you want to generate i could help you translate it to linq.

EDIT:
So i was able to recreate your code. This is not optimal as it will call to many times the database with sql queries.

public class Recursive
{
    BlogContext db = new BlogContext();
    public int Counter { get; set; } = 0;
    public Recursive()
    {
        db.Database.Log += (str) => //this will log all the calls to the database.
        {
            System.Diagnostics.Debug.WriteLine(str, "Sql Query: ");
        };
    }

    public List<Location> StartRecursive()
    {
        return GetChild(50).ToList();
    }
    public IEnumerable<Location> GetChild(int id)
    {
        var locations = db.Locations
        .Where(x => x.ParentLocationID == id || x.LocationID == id).ToList();
        if (locations.Count == 1) return locations;
        var locationSubset = locations.Where(tt=>tt.LocationID!=id)
        .SelectMany(tt => GetChild(tt.LocationID)).ToList();
        Counter++;
        return locations.Union(locationSubset);
    }