Entity Framework Self Join

Question

I am using Entity Framework 6 with Code First, 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's possible to get on a single query/trip to database, all Items across my tree until the root, supplying a itemId as argument.

e.g Give me all parents from ItemId 55 until no parent is found.

I don't think it is possible. Probably have to map to a recursive CTE with stored proc. — GraemeMiller, Dec 15 '14 at 20:32
Echoing @GraemeMiller here: I had to do something similar recently, and that's how I ended up doing it. — Corey Adler, Dec 15 '14 at 20:37

score 0 · Accepted Answer · edited May 23 '17 at 10:26

You cannot get all parents in one trip using any sane code.

However you can do something like this: https://stackoverflow.com/a/11565855/304832

By modifying your entity 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 do need to introduce a new entity to make this work though, 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 parent is found

...you can do something like this:

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

Entity Framework Self Join

1 Answers1

Linked