How to implement recursive self join entity framework?

Question

I have Menu table in database which have self referencing foreign key i.e. ParentID. Below is my Menu class (DB First Approach)

public partial class Menu
{
    public Menu()
    {
        this.Menu1 = new HashSet<Menu>();
        this.Products = new HashSet<Product>();
    }

    public int MenuID { get; set; }
    public string Name { get; set; }
    public Nullable<int> ParentID { get; set; }
    public virtual ICollection<Menu> Menu1 { get; set; }
    public virtual Menu Menu2 { get; set; }
    public virtual ICollection<Product> Products { get; set; }
}

I want to implement following things,

I want entire hierarchy using menu id e.g "if I pass 7 then result should be all the child and sub-child of menu id 7"
If I pass 7 then I want all parent and super parent of menu id 7.

I found several article on StackOverflow before posting this question but they were asking to implement Code First Approach. Here are the questions posted on Stackoverflow before Entity Framework Self Join, Most efficient method of self referencing tree using Entity Framework

Possible duolicate: http://stackoverflow.com/questions/1308158/how-does-entity-framework-work-with-recursive-hierarchies-include-seems-not-t — Evaldas Buinauskas, May 18 '15 at 07:52
A possible solution is through a TVF (Table Valued Function) that uses a TSQL Recursive Query and returns the rows. — xanatos, May 18 '15 at 07:56
@xanatos : I can not interact with database. I have Static list of Menu class object. That's why i need to do it with EF. — Kalpesh Bhadra, May 18 '15 at 08:27

Parth Shah · Accepted Answer · 2015-05-18T08:41:31.077

5

I am not sure if you realize this but Menu1 is your parent Menu and Menu2 are your children menus. (I would recommending renaming both Menu1 and Menu2 properties to parent and children).

I believe all of the solutions you have linked have a solution you can use to solve your problem.

Code Sample:

void GetParents(Menu current) {
    dbContext.Entry(current).Reference(m => m.Menu2).Load();
    while (current.Menu2 != null) {
        current = current.Menu2; 
        dbContext.Entry(current).Reference(m => m.Menu2).Load();
    }
}

void GetChildren(Menu current) {
    if (current == null) {
        return;
    } else {
        dbContext.Entry(current).Collection(m => m.Menu1).Load();
        foreach (var menu in m.Menu1) {
            GetChildren(menu);
        }
    }
}

Something like this should help you get all parents and all children of a Menu instance called current. Note the efficiency is terrible. But that is a different problem. I don't optimize code until my performance tests indicate the bottlenecks in my application.

Fun quote: "Premature optimization is the root of all evil." - Donald Knuth

edited May 18 '15 at 08:41

answered May 18 '15 at 08:18

Parth Shah

2,060
1
23
34

Parth i can reach parent and child upto one level by following code 'List childList = db.Menus.Where(m => m.ParentID == menuID).ToList();' . There may be N-Level. How can i do it? – Kalpesh Bhadra May 18 '15 at 08:24
Added a code sample. It is untested so you will need to change stuff here and there. But that is the general idea. I re-read your class definition and am a bit confused on why you call Nullable ParentId? I would assume they would be your children? However if your model says you can have multiple parents and just one child, then you will have to rename my functions. – Parth Shah May 18 '15 at 08:44
Your answer helped me solve my problem. I want to vote up but my reputation is 1 so i cant do it and i cant find accept answer or mark correct answer button to accept your answer. – Kalpesh Bhadra May 19 '15 at 13:43
You might see a tick below the vote up/down buttons. If you click on the tick, then it will mark this answer as correct. – Parth Shah May 20 '15 at 03:19

How to implement recursive self join entity framework?

1 Answers1