Entity Framework Self Join

Question

I am using Entity Framework 6 with First code, and I have a table with the following structure:

public class Item { [Key] public int ItemId { get; set; } [Required] [MaxLength(255)] public string Name { get; set; }} public Item Parent { get; set; } public virtual List<Item> Children { get; set; } } 

I would like to know if it is possible to get one query / trip to the database, all Items through my tree to the root by supplying the itemId argument.

For example, give me all parents from itemId 55 until a parent is found.

Answer 1

You cannot force all parents to use any reasonable code in one trip.

However, you can do something like this: fooobar.com/questions/174990 / ...

By changing your essence a bit, you can add 2 derived collections:

 public class Item { [Key] public int ItemId { get; set; } [Required] [MaxLength(255)] public string Name { get; set; }} public virtual Item Parent { get; set; } // be sure to make this virtual public virtual List<Item> Children { get; set; } public virtual ICollection<ItemNode> Ancestors { get; set; } public virtual ICollection<ItemNode> Offspring { get; set; } } 

You need to introduce a new object to do this job, which looks like this:

 public class ItemNode { public int AncestorId { get; set; } public virtual Item Ancestor { get; set; } public int OffspringId { get; set; } public virtual Item Offspring { get; set; } public int Separation { get; set; } // optional } 

Now if you want

all parents from ItemId 55 until no parents are found

... you can do something like this:

 IEnumerable<Item> allParentsFrom55 = dbContext.Set<Item>() .Find(55).Ancestors.Select(x => x.Ancestor);