1

Given the following:

public class Person
{
  public int  PersonId { get; set; }
  public int? ParentId { get; set; }
}

Suppose I have the following tree structure (PersonID - ParentID):

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

How can I get all the parents of PersonId 3, or 2,1 using a LINQ to SQL query?

Note: A null ParentId denotes a top-level Person

Brian David Berman
  • 7,514
  • 26
  • 77
  • 144
  • Sounds like recursion; you'd do that kind of thing with a CTE in Sql land, but I think that is too complex for Linq2Sql. Might want to make a proc. – Tejs Apr 28 '11 at 20:21
  • possible duplicate of [Hierarchy Problem -> Replace Recursion with Linq Join??](http://stackoverflow.com/questions/1435229/hierarchy-problem-replace-recursion-with-linq-join) – ChrisF Apr 28 '11 at 20:33
  • @Chris: That's a different kind of problem. That searches for descendants of a hierarchy while this one searches for the ancestors. – Jeff Mercado Apr 28 '11 at 20:37
  • 1
    @Jeff - but the reason LINQ won't work is the same for both problems. – ChrisF Apr 28 '11 at 20:38
  • http://stackoverflow.com/questions/535436/linq-to-sql-recursively-get-children & http://stackoverflow.com/questions/1976325/fill-a-recursive-data-structure-from-a-self-referential-database-table & http://stackoverflow.com/questions/202912/hierarchical-data-in-linq-options-and-performance – CD.. May 02 '11 at 16:19
  • You are trying to create a hierarchical query. The first four results should help you further: http://www.google.com/search?hl=en&q=hierarchical%20query%20linq – Daniel Hilgarth May 02 '11 at 16:19
  • I think a reqursive CTE in SQL is the best way to go. Linq can't do it. – Magnus May 02 '11 at 18:26

1 Answers1

2

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

var personId = 3 as int?;
var result = new List<Person>();
do
{
    var person = context.Persons.Single(p => p.PersonId == personId.Value);
    result.Add(person);
    personId = person.ParentId;
} while (personId != null);
// all ancestors are in `result`
Jeff Mercado
  • 129,526
  • 32
  • 251
  • 272
  • Would you know how to do it, the other way around? http://stackoverflow.com/questions/5859520/get-unknown-amount-of-childen-using-linq-to-sql – Brian David Berman May 02 '11 at 17:58
  • @Brian: The concept is the same. Instead of taking the starting person and going to the parent, you need to go to the child. In this case, you need to find the people who's parent is the target person. Then for each of these people, repeat. – Jeff Mercado May 02 '11 at 19:21