Selecting multiple one to many relationships in LINQ to SQL using outer joins

I'm using .NET 4 and VS 2010 and have the same issue in .NET 3.5/VS 2008

The structure:

Table 1: Call

Table 2: AddressChangeRequest

Table 3: CallNotes

A single Call can have many AddressChangeRequests and many CallNotes. A Customer (customerKey) can have many Calls.

The LINQ code:

return db.Calls.Where(c => c.CustomerKey == '...').Select( c => new Call(c.CustomerKey, c.StartTime, c.AddressChangeRequests, c.CallNotes));

Call is a domain object that expects a list of AddressChangeRequests and CallNotes. The Code returns a list of Calls as expected however the SQL is not optimal.

The code above generates SQL as a one SELECT from the Call table with a left outer join on the AddressChangeRequest table followed by separate SELECT queries from the CallNote table (one for each associated Call)

Sel开发者_C百科ect ... from Call as c
LEFT OUTER JOIN AddressChangeRequest as acr ON c.id = acr.callId

Select ... from CallNote Where callId = 123
Select ... from CallNote Where callId = 456
Select ... from CallNote Where callId = 789

If I remove AddressChangeRequests from code, a left outer join is generated for the CallNote table and the individual Select statements are no longer generated.

My question is, using LINQ how can I generate a single SQL Statement as follows:

Select ... from Call as c
LEFT OUTER JOIN AddressChangeRequest as acr ON c.id = acr.callId
LEFT OUTER JOIN CallNote as cn ON c.id = cn.callId

The answer is unfortunately "no". L2S's eager loading support is primitive. You can however create your own version on top of the standard query facilities.