How to create and populate a nested ViewModel well

I have a View Model that has some serious nesting. I need to populate it from Entity Framework 4. I tried creating one big linq statement to populate it, but it says it doesn't recognize the .ToList() methods. It compiles fine. Runtime error is

LINQ to Entities does not recognize the method 
'System.Collections.Generic.List`1[ProductDepartment] ToList[ProductDepartment]
(System.Collections.Generic.IEnumerable`1[ProductDepartment])' method, 
and this method cannot be tra开发者_运维知识库nslated into a store expression.

What is a more efficient way to populate something like this without doing several thousand database calls?

List<Product> Products {
    int ID
    string Name
    ...
    List<Department> Departments {
        int ID
        string Name
    }
    List<Image> Images {
        int ID
        string Name
    }
    List<Price> Prices {
        int ID
        string Name
        List<Version> Versions {
            int ID
            string Name
            List<Pages> Pages {
                int ID
                string Name
}   }   }   }

The horrible Linq code looks something like this

var myProducts = (from myProduct in DC.MyProducts
                  where p => p.productGroup == 1
                  select new Product {
                      ID = myProduct.ID,
                      Name = myProduct.Name,
                      Departments = (from myDept in DC.MyDepartments
                                     where q => q.fkey = myProduct.pkey
                                     select new Department {
                                         ID = myDept.ID,
                                         Name = myDept.Name
                                     }).ToList(),
                      ...
                      //Same field assignment with each nesting
                  }).ToList();

Update:

The fix was to remove all the .ToLists(), which worked better anyway.

Now I have to do filtering and sorting on the end product.

---

Well for starters, that is one crazy model, but i'm assuming you already know this.

Do you really need all that info at once?

I'll play devil's advocate here and assume you do, in which case you have a couple of logical choices:

1) As @xandy mentioned - use .Include to eager load your associations in the one call. This is assuming you have setup navigational properties for your entites in your EDMX.

2) Use a View. Put all that crazy joining logic inside the database, making your EF work a very simple select from the view. The downside of this is your queries to the view basically become read only, as i don't believe you can perform updates to an entity which is mapped to a view.

So it's your choice - if this is a readonly collection for displaying data, use a View, otherwise eager-load your associations in the one hit.

Also, be careful when writing your LINQ queries - i see you have multiple .ToList statements, which will cause the query to be executed.

Build up your query, then perform the .ToList once at the end.

---

why do you require all this informataion at one go? You can use lazy loading when a nested property is accessed?