EF code-first: How to load related data (parent-child-grandchild)?

Question

I have this entity:

public class DynamicPage {

    public int PageId { get; set; }

    public int Order { get; set; }

    public string MenuText { get; set; }

    public string MenuHover { get; set; }

    public int? ParentId { get; set; }

    public virtual DynamicPage Parent { get; set; }

    public virtual ICollection<DynamicPage> Children { get; set; }
}

This entity may have 3 level: Parent -> Child -> Grandchild. How can I load the Parent (level 1) whit all associated children (level 2) and for each child, associated grandchild (level 3) if any? Thanks to help.

score 12 · Accepted Answer · answered Sep 22 '11 at 17:39

12

EF 4.1 feature and syntax:

var entity = context.Parents
    .Include(p => p.Children.Select(c => c.GrandChildren))
    .FirstOrDefault(p => p.Id == 1); // or whatever condition

answered Sep 22 '11 at 17:39

Slauma

175,098
59
401
420

There is not a property named `GrandChildren`. Do you mean this code: `Include(p => p.Children.Select(c => c.Children))` ? – amiry jd Sep 22 '11 at 23:01
@Javad_Amiry: Yes. Actually, what I wrote as `GrandChildren` can be any navigation property - collection or reference. The pattern `Include(x => x.SomeCollection.Select(c => c.SomeNavigationProperty))` just causes an `Include` for the next level. You can repeat that ad infinitum: `Select` on collections, simple dotted paths on references. – Slauma Sep 22 '11 at 23:06
OK, this works correctly :D thanks a lot. In continues I need to order children (and so grand children) when fetching them. I'll edit your answer and put my first code, and final code created by your guidance to explain my issue. Please help me to complete the solution. Special thanks to pay attention to question. Regards – amiry jd Sep 22 '11 at 23:21
@Javad_Amiry: Better make a new question about your sorting problem because 1) more people will see a new question than an edit in my answer, and 2) I'm offline now :) – Slauma Sep 22 '11 at 23:28
OK, before I see your comment, I post my edit. So sorry. I'll put it to a new question just now. Please rollback my edit, SOF don't allow me to do it by myself. Thanks again. – amiry jd Sep 22 '11 at 23:31
Has this changed in EF4.3? The only version of .Include() I can see in 4.3 takes a string instead of a typesafe navigator like x => x.Children - a step backwards. This is reflected in the documentation here: http://www.asp.net/mvc/tutorials/mvc-music-store/mvc-music-store-part-4 Seems strange to go backwards like that in a newer version of the framework... – Chris Moschini Mar 03 '12 at 04:41
1

@ChrisMoschini: No, it hasn't changed. The expression overload of `Include` is still there. Make sure that you have included `using System.Data.Entity;` in your source file. – Slauma Mar 03 '12 at 12:46

devuxer · Answer 2 · 2011-09-22T16:59:01.050

0

If you want to make life easy on yourself, follow the EF Code First conventions of naming your table IDs simply Id (or, alternatively, name of table + Id, e.g., DyanmicPageId).

This should leave you with something like this:

public class DynamicPage
{
    public int Id { get; set; }
    public int Order { get; set; }
    public string MenuText { get; set; }
    public string MenuHover { get; set; }
    public int? ParentId { get; set; }

    public virtual DynamicPage Parent { get; set; }
    public virtual ICollection<DynamicPage> Children { get; set; }
}

Then you need to set up the relationship between parents and children explicitly in an OnModelCreating method in your DbContext class.

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<DynamicPage>()
        .HasMany(page => page.Children)
        .WithRequired(child => child.Parent)
        .HasForeignKey(child => child.ParentId);
}

You can then select children or grandchildren as needed:

var parent = dbContext.DynamicPages.Where(page => page.ParentId == null);
var children = parent.Children;
var grandchildren = parent.SelectMany(page => page.Children);
var allRelatedPages = parent.Union(children).Union(grandchildren);

edited Sep 22 '11 at 16:59

answered Sep 22 '11 at 00:51

devuxer

41,681
47
180
292

What are u talking about? Plz read the Q again: **How can I load the Parent (level 1) whit all associated children (level 2) and for each child, associated grandchild (level 3) if any?** I know how to map entities, and know how to load them in 2-level relationship *Parent-Child*. But I can't load a 3-level association. Just this! – amiry jd Sep 22 '11 at 01:00
What do you mean by "load"? Do you mean select? – devuxer Sep 22 '11 at 01:04
Load from database, fetch from db, select. Like this: `entity.DynamicPages.Where(d => d.ParentId == null).Include(d => d.Children).ToList();` I know this, but I want to load `d.Children.Children` that I can't ): – amiry jd Sep 22 '11 at 01:08
Updated my answer. Hopefully this is what you were looking for. – devuxer Sep 22 '11 at 01:12
Thanks, I will test it and put the result here. Regards – amiry jd Sep 22 '11 at 01:16
Well in that case, you just need to union them together. I updated my answer again. – devuxer Sep 22 '11 at 16:58
Thank you to pay attention on my question, but my answer is not this, and because of I can't speak English good, I can't explain my purpose. Regards – amiry jd Sep 22 '11 at 23:47
1

Now that I see Slauma's answer, I get what you were looking for. And I also learned something, so no problem. – devuxer Sep 23 '11 at 01:06
I moved the part 2 of question (ordering children and grandchildren) here: http://stackoverflow.com/questions/7522784/ef-4-1-code-first-how-to-order-navigation-properties-when-using-include-and-or-s I'll be thankful if see the new question and suggest me if have any idea. Good lock and best regards. – amiry jd Sep 23 '11 at 01:29

EF code-first: How to load related data (parent-child-grandchild)?

2 Answers2

Linked