title: Linq-to-Entities: LEFT OUTER JOIN with WHERE clause and projection
body: <p>I'm having a heckuva time figuring out how to translate a simple SQL LEFT OUTER JOIN with a two condition where clause into a working Linq-to-Entities query.  There are only two tables.  I need values for all rows from Table1, regardless of matches in Table2, but the WHERE clause uses fields from Table2.  In SQL, the two parameters would be Table2WhereColumn1 and Table2WhereColumn2, and the query (which works) looks like this:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT t1.Table1Id,
      t1.FieldDescription, 
      t2.FieldValue
  FROM Table1 t1 WITH (NOLOCK)
  LEFT JOIN Table2 t2 WITH (NOLOCK) ON t1.Table1Id = t2.Table1Id
  WHERE (t2.Table2WhereColumn1 = @someId OR t2.Table2WhereColumn1 IS NULL)
  AND (t2.Table2WhereColumn2 = @someOtherId OR t2.Table2WhereColumn2 IS NULL)
  ORDER BY t1.OrderByColumn
  </code></pre>
  
  <p>I've tried using <code>Group Join</code> with <code>DefaultIfEmpty()</code>, as well as an implicit join (without the actual <code>Join</code> keyword), and I only get rows for items that have values in Table2.  I'm sure this won't help, but here's an example of the Linq I've been trying that doesn't work:</p>
  
  <pre><code>Public Shared Function GetProfilePreferencesForCedent(ByVal dc As EntityContext, _
                                                        ByVal where1 As Int32, _
                                                        ByVal where2 As Int32) _
                                                    As IQueryable(Of ProjectedEntity)
      Return From t1 In dc.Table1
             Group Join t2 In dc.Table2 _
                  On t1.Table1Id Equals t2.Table1Id _
                  Into t2g1 = Group _
             From t2gx In t2g1.DefaultIfEmpty(Nothing)
             Where (t2gx.Table2Where1 = where1 Or t2gx.Table2Where1 = Nothing) _
                  And (t2gx.Table2Where2 = where2 Or t2gx.Table2Where2 = Nothing)
             Order By t1.SortOrder
             Select New ProjectedEntity With {
                 .Table1Id = t1.Table1Id, _
                 .FieldDescription = t1.FieldDescription, _
                 .FieldValue = If(t2gx Is Nothing, String.Empty, t2gx.FieldValue) _
             }
  End Function
  </code></pre>