How do you construct a LINQ to Entities query to load child objects directly, instead of calling a Reference property or Load().

Question

I'm new to using LINQ to Entities (or Entity Framework whatever they're calling it) and I'm writing a lot of code like this:

var item = (from InventoryItem item in db.Inventory
            where item.ID == id
            select item).First<InventoryItem>();

and then calling methods on that object like this:

var type = item.ItemTypeReference;

or

var orders = item.OrderLineItems.Load();

to retrieve child or related objects.

I haven't profiled the DB or dug too deeply but my guess is that when I call a .Load() or a *Reference property I'm actually making another call to the DB. If this is the case, is there any way to get those objects in my initial LINQ expression?

Answer 1

You want to use the .Include(string) method references in this "Shaping query results" article.

var item = from InventoryItem item in
              db.Inventory.Include("ItemTypeReference").Include("OrderLineItems")
           where item.ID == id
           select item;

There is probably a "sql" style syntax for the Includes as well.

Also see this article about moving from LINQ-to-SQL to LINQ-to-Entities.

For others looking for a solution to this problem for Linq to SQL you want to do the following (Substitute DataContext and other types for whatever you have):

using (DataContext db = new DataContext())
{
    DataLoadOptions options = new DataLoadOptions();
    options.LoadWith<InventoryItem>(ii => ii.ItemTypeReference);
    options.LoadWith<InventoryItem>(ii => ii.OrderLineItems);
    db.LoadOptions = options;

    var item = from InventoryItem item in db.Inventory
               where item.ID == id
               select item;
}

This will load the properties specified in LoadWith whenever the parent item (InventoryItem) is loaded, for that particular context.

In response to some further questions from James and Jesper, check out this question

Answer 2

Using that same example, how do I make a where condition on OrderLineItems?

For example, where item.OrderLineItems.orderLineId == 6 does not work intellisense gives me First<>, Equals<> etc...