title: Left outer join using LINQ - yields different outputs
body: <p>I am trying to write left outer join using LINQ. The SQL looks like,</p>
  
  <pre><code>SELECT  *                 
  FROM Table1 td1
  LEFT OUTER JOIN Table2
  ON td1.ColumnName = td2.ColumnName
  WHERE td2.ColumnName IS NULL                  
  ORDER BY SomeColumns
  </code></pre>
  
  <p>If I run this query in SQL Query analyzer, it returns say 100 records. And my converted LINQ code returns 105 records.
  I have written LINQ in 2 ways as,</p>
  
  <p>Method 1:</p>
  
  <pre><code>var data= (from td1in Table1
             join td2 in Table2.Where(a =&gt; a.ColumnName == (int?)null)
             on td1.ColumnName equals td2.ColumnName into outer
             from x in outer.DefaultIfEmpty()
             orderby SomeColumns
             select td1);
  </code></pre>
  
  <p>Method 2: This gives an exception as, failed to enumerate results</p>
  
  <pre><code>var data = from td1 in Table1
             join td2 in Table2
             on td1.ColumnName equals td2.ColumnName into outer
             from item in outer.DefaultIfEmpty()
             where item.ColumnName.Value == (int?)null
             orderby somecolumns
             select td1 ;
  </code></pre>
  
  <p><strong>The column used in where clause is nullable int type</strong>.</p>
  
  <p>The result returned in SQL analyzer seems to be correct one.
  Please help me in getting the identical results.</p>
  
  <p>Thanks</p>