How can I get an IList of entites and their child objects in Entity Framework efficiently?

Question

That is, I want to do this:

var lists = (from list in this.DB.Lists
             select new
             {
                 List = list,
                 Items = list.ListItems.ToList(),
             }).ToList();

But, obviously, the Entity Framework doesn't support the ToList extension method in the select clause.

What should I do? Any other method I've tried ends up sending a ton of queries to the database when iterating.

Answer 1

Have you tried the Include etension method?

var lists = (from list in this.DB.Lists.Include("ListItems")
             select list).ToList(); 

foreach(var item in lists)
{
   // there shouldn't be any DB calls here.
   var items = list.ListItems.ToList();
}

Answer 2

You can't call ToList on the part of the query that is translated to SQL, but you can do it locally, using AsEnumerable:

var lists = (from list in this.DB.Lists.AsEnumerable()
             select new
             {
                 List = list,
                 Items = list.ListItemsItems.ToList()
             }).ToList();