title: C# Linq expression for nested left join
body: <p>I'm looking to format a nested left join in a linq expression.  I've downloaded LINQPad4, but it doesn't seem to have the ability to translate SQL into linq.</p>
  
  <p>I don't understand how to get a nested left join.  I've tried manipulating the expression with the 'into' clause to no avail.</p>
  
  <hr>
  
  <p>Linq expression</p>
  
  <pre><code>from cli in Clients
  join er in EntityRelationships on cli.EGUID equals er.ParentID
  join con in Contacts on er.ChildID equals con.EGUID
  
  join erEmail in EntityRelationships on er.EGUID equals erEmail.ParentID into erIfAny
  from r in erIfAny.DefaultIfEmpty()
  join ea in EmailAddresses on r.ChildID equals ea.EGUID
  join eaDefault in EntityDefaultValues on r.ID equals eaDefault.RelationshipID
  
  where cli.ClientID == 6
  select new
  {
      ContactID = con.ContactID,
      ContactLastName = con.ContactLastName,
      ContactFirstName = con.ContactFirstName,
      ContactTitle = con.ContactTitle,
      EmailAddress = ea
  }).Distinct()
  </code></pre>
  
  <p>Produces the following SQL statement</p>
  
  <pre><code>DECLARE @p0 Int = 6
  
  SELECT DISTINCT con.*, ea.*
  FROM Clients AS cli
  INNER JOIN EntityRelationships AS er ON cli.eGUID = er.ParentID
  INNER JOIN Contacts AS con ON er.ChildID = con.eGUID
  
  LEFT OUTER JOIN EntityRelationships AS erEmail ON er.eGUID = erEmail.ParentID
  INNER JOIN EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID
  INNER JOIN EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID
  
  WHERE cli.ClientID = @p0
  </code></pre>
  
  <p>However, I need the two inner joins after the left to be wrapped inside the left like so.</p>
  
  <pre><code>DECLARE @p0 Int = 6
  
  SELECT DISTINCT con.*, ea.*
  FROM Clients AS cli
  INNER JOIN EntityRelationships AS er ON cli.eGUID = er.ParentID
  INNER JOIN Contacts AS con ON er.ChildID = con.eGUID
  
  LEFT OUTER JOIN EntityRelationships AS erEmail
      INNER JOIN EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID
      INNER JOIN EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID
  ON er.eGUID = erEmail.ParentID
  
  WHERE cli.ClientID = @p0
  </code></pre>
  
  <p>What should my linq expression look like?</p>
  
  <hr>
  
  <p>Edit:</p>
  
  <p>Didn't know I could do this, but I ended up just writing inline SQL.</p>
  
  <pre><code>var query = db.Database.SqlQuery&lt;ContactViewModel&gt;(
  "select distinct con.*, ea.Value as EmailAddress " +
  "from Clients AS cli " +
  "join EntityRelationships AS er ON cli.eGUID = er.ParentID " +
  "join Contacts AS con ON er.ChildID = con.eGUID " +
  "left join EntityRelationships AS erEmail " +
      "join EmailAddresses AS ea ON erEmail.ChildID = ea.eGUID " +
      "join EntityDefaultValues AS eaDefault ON erEmail.ID = eaDefault.RelationshipID " +
  "on er.eGUID = erEmail.ParentID " +
  "where cli.ClientID=" + cID.ToString() + " " +
  "order by con.ContactLastName, con.ContactFirstName");
  </code></pre>