Entity Framework - Eager Load Related Entities' Related Entity

Question

I'm writing a simple database application in C# (.NET 4.5) with Entity Framework 5. I have a situation where I may or may not need to load a single entity's related entities. If it happens that I need to load the entity's related entities, I would like to eager load the related entities' related entity. Basically, I am trying to avoid the "SELECT N+1" problem. Hopefully the following code will make what I'm trying to do clear:

using (var ctx = new DbContext())
{
    // Find a single instance of a person.  I don't want to eagerly load
    // the person's friends at this point because I may not need this
    // information.
    var person = ctx.Persons.Single(x => x.PersonID == 12);

    // Many lines of code...
    // Many lines of code...
    // Many lines of code...

    // Okay, if we have reached this point in the code, the person wants to 
    // send all his friends a postcard, so we need the person's friends and
    // their addresses.

    // I want to eagerly load the person's friends' addresses, but the Include
    // method is not allowed.  The property "Friends" is just an ObservableCollection.
    var friends = person.Friends.Include("Address").ToList();

    // So, I must do the following:
    var friends = person.Friends.ToList();

    // Now I will output the address of each friend. This is where I have the 
    // SELECT N+1 problem.
    foreach(var friend in friends)
    {
        // Every time this line is executed a query (SELECT statement) is sent
        // to the database.
        Console.WriteLine(friend.Address.Street);

    }

}

Any ideas as to what I should do?

score 1 · Accepted Answer · answered May 10 '13 at 21:37

That's a good situation for explicit loading - the third option to load related entities with Entity Framework besides eager and lazy loading:

using (var ctx = new DbContext())
{
    var person = ctx.Persons.Single(x => x.PersonID == 12);

    // ...

    // the following issues one single DB query
    person.Friends = ctx.Entry(person).Collection(p => p.Friends).Query()
        .Include(f => f.Address) // = .Include("Address")
        .ToList();

    foreach(var friend in person.Friends)
    {
        // No DB query because all friends including addresses
        // have already been loaded
        Console.WriteLine(friend.Address.Street);
    }
}

The key here is .Query() which returns an queryable for the Friends collection and that allows you to add arbitrary additional query logic for the friends collection - like filtering, ordering, joining additional related data (=Include), aggregation (Count the friends for example), etc.

@Slauma...I finally got it working...I needed to add "using System.Data.Entity;" to make it work. Thanks again for all your help...I really appreciate it! =) — HydroPowerDeveloper, May 11 '13 at 03:16

Entity Framework - Eager Load Related Entities' Related Entity

1 Answers1