title: C# Linq join table, where left table might be null
body: <p>Please, see my example below. Here, for the question sake, I have 3 tables - <strong>mainTable</strong>, <strong>tableA</strong> and <strong>tableB</strong>. In tables, PK fields mean primary keys, FK fields mean foreign fields. </p>
  
  <p>I have trouble joining 3 tables. In mainTable, there are field "nullableFK" which references to tableA. It might be null. So to join tableA, i use Left Join using DefaultIfEmpty(). Problem starts, when i need to join third table on fields which match in tableA and tableB. example below throws, <code>InvalidOperationException</code> with following message :</p>
  
  <pre><code>"The cast to value type 'System.Boolean' failed because the materialized value is null. Either the result type's generic parameter or the query must use a nullable type."
  </code></pre>
  
  <p>I tried joining tableB without DefaultIfEmpty - as far as i know, it would be inner join. But then Sequence contains no elements.</p>
  
  <p><strong>Goal is</strong> - </p>
  
  <p>to return values of <strong>mainTable</strong>, <strong>tableA</strong> and <strong>tableB</strong>.</p>
  
  <p>If in <strong>mainTable</strong>, nullableFK is null, return <strong>mainTable</strong>, and <strong>tableA</strong> and <strong>tableB</strong> values as null, </p>
  
  <pre><code>            var query = (from mainTable in db.mainTables
                          where mainTable.PK == id
  
                          join tableA_ in db.tablesA on mainTable.nullableFK equals tableA_.FK into A
                          from tableA in A.DefaultIfEmpty()
  
                          join tableB_ in db.tablesB on tableA.PK equals tableB_.PK into B
                          from tableB in B.DefaultIfEmpty()
  
                          select new {
                              val1 = mainTable.fieldA,
                              val2 = tableA.fieldB,
                              val3 = tableB.fieldC
                          }).First();
  </code></pre>
  
  <p><strong>Answer</strong> - Check for <code>null</code> values in <code>select new {}</code>.</p>