LINQ: Chain ID's from one row to another

I have a table which has sort of a child->child->parent set up inside it. (It's a patchup im using on an existing old database so it's a little dodgy).

The class for the table:

public class Foo
{
    int ID {get;set;}
    int ParentID  {get;set;}
    int BaseParentID {get;set;}
}

Lets say i have a few records in there

ID: 10, ParentID: 5, BaseParentID: 5
ID: 05, ParentID: 1, BaseParentID: 5
ID: 01, ParentID: 1, BaseParentID: 0

What I want to do, is get each of the ParentID's until the the baseparentid is 0. So in a way, it's stepping through the table from one record to another and retrieving it into a list of ID's.

The end result should be a list: { 10, 5, 1 }

This is what I'm doing now (there is a limit of 4 at the moment, but i'd prefer it if there was no limit):

var list = new List<int?>();
var id = 10; // The first ID is given when this method is started.
list.Add(id);
int? pid = db.Foo.Where(w => w.ID == id).Single().BaseParentID; // i have this as a compiled query function
if (pid != 0) {
    list.Add(pid);
    pid = db.Foo.Where(w => w.ID == pid).Single().BaseParentID; //  for the sake of this example i'm just using the query here
    if (pid != null) {
         list.Add(pid);
         // And so on
    }
开发者_Python百科}

As you can see, it's a bit of a crappy way to do this. But i'm not sure if there's a way to do this in a fancy linq query.

ps. The point of this is sort of a pseudo folder structure.

---

This is a good example of where you would write a separate iterator function:

 IEnumerable<Foo> TraverseParents(Foo foo, IEnumerable<Foo> all)
 {
      while(foo != null) 
      {
          yield return foo;
          foo = (foo.pid == 0) ? null : all.FirstOrDefault(f => f.ID == foo.pid);
      }
 }

 // In the calling code
 var id = 10;
 Foo root = db.Foo.FirstOrDefault(f => f.ID == id);
 List<int> list = TraverseParents(root, db.Foo)
                   .Select(f => f.ID)
                   .ToList();

---

You can use the following method:

List<int> GetParentHierarchy(int startingId)
{
  List<int> hierarchy = new List<int> { startingId };
  using(Connection db = new Connection()) //change to your context
  {      
      int parentId = startingId;
      while(true)
      {
         var foo = db.Foo(x => x.Id == parentId).SingleOrDefault(); 
         if(foo == null)
           break;
         parentId = foo.ParentId;
         hierarchy.Add(foo.Id);
         if(foo.BaseParentID == 0)
           break;
      }
  }

  return hierarchy;

}