Get unknown amount of parents using LINQ to SQL -

given following:

public class person {   public int  personid { get; set; }   public int? parentid { get; set; } } 

suppose have following tree structure (personid - parentid):

1 - null   2 - 1     3 - 2   4 - 1 

how can parents of personid 3, or 2,1 using linq sql query?

note: null parentid denotes top-level person

you'll need loop (or other form of recursion).

var personid = 3 int?; var result = new list<person>(); {     var person = context.persons.single(p => p.personid == personid.value);     result.add(person);     personid = person.parentid; } while (personid != null); // ancestors in `result`