Retrieve entities with children per one sql call. ADO.NET Entity framework

Question

Hello everybody,

I have two tables: A & B

B { B1: Field1, B2: Field2, ... }

A { Children: List of B, A1: Field1, A2: Field2, }

I want to retrieve "A" entities with related "B" entities like this:

DataContext.A.Select( a => new MySubset( A1 = a.A1, Children = a.Children.Select(b => b.B1).ToList());

But EF can't translate ToList into SQL, so i have to call ToList() per each instance in query producing additional network call.

How can I avoid this?

Thank you in advance.

Answer 1

I would use IEnumerable instead of List in the constructor of MySubset.

Or: Create the List not by .ToList(), but by new List<T>(a.Children.Select(b => b.B1))

Answer 2

You can use an .Include statement to do eager loading:

http://msdn.microsoft.com/en-us/library/bb896272.aspx

Explanation

Assuming you are on VS2010 and Lazy Loading is enabled:

When you load A initially there's a collection of B's apparently waiting for you off A, but it's not really, it's not a List<>, its an object that knows how to provide the B's when you ask for them. Only when you access this collection will EF do the round trip to the database to fetch it.

But if you use .Include() you can request that it fetches B at the same time as it fetches A.