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

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;
        }