CODEWITHSUNDEEP
linqlinq-to-sqllinq-to-entitiesparent-child
Brian David Berman
Brian David Berman

Reputation: 7674

Get unknown amount of parents using LINQ to SQL

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

Upvotes: 1

Views: 1194

Answers (1)

Jeff Mercado
Jeff Mercado

Reputation: 134801

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`

Upvotes: 2

Related Questions