StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSelecting multiple one to many relationships in LINQ to SQL using outer joins
text
Body
copied!<p>I'm using .NET 4 and VS 2010 and have the same issue in .NET 3.5/VS 2008</p> <p>The structure:<br> Table 1: Call<br> Table 2: AddressChangeRequest<br> Table 3: CallNotes </p> <p>A single Call can have many AddressChangeRequests and many CallNotes. A Customer (customerKey) can have many Calls. </p> <p>The LINQ code:<br> return db.Calls.Where(c => c.CustomerKey == '...').Select( c => new Call(c.CustomerKey, c.StartTime, c.AddressChangeRequests, c.CallNotes)); </p> <p>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. </p> <p>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) </p> <pre><code>Select ... 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 </code></pre> <p>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.</p> <p>My question is, using LINQ how can I generate a single SQL Statement as follows:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload