title: Get Rows with Different Values or Missing from One Table
body: <p>I have two tables which have similar structures.  Each table has an <code>Id</code> and a <code>FirstValue</code> and <code>SecondValue</code> field.  I want to use Linq-to-SQL to get each row that's in <code>TableOne</code> and not in <code>TableTwo</code> (based on <code>Id</code>) and also get each row that's in both tables, but has different values of <code>FirstValue</code> or <code>SecondValue</code>.  The SQL should be something like</p>
  
  <pre><code>SELECT TableOne.*
  FROM TableOne
       LEFT OUTER JOIN TableTwo
         ON TableOne.Id = TableTwo.Id
  WHERE TableTwo.Id IS NULL 
     OR (TableTwo.Id IS NOT NULL AND 
          (TableOne.FirstValue &lt;&gt; TableTwo.FirstValue OR
           TableOne.SecondValue &lt;&gt; TableTwo.SecondValue
          ) 
        )
  </code></pre>
  
  <p>I've tried </p>
  
  <pre><code>(from a in context.TableOne
   from b in context.TableTwo.Where(b =&gt;
            a.Id == b.Id &amp;&amp;
           (a.FirstValue != b.FirstValue ||
            a.SecondValue != b.SecondValue)).DefaultIfEmpty()
   select a).ToList();
  </code></pre>
  
  <p>The problem in that query is that if <code>TableOne</code> and <code>TableTwo</code> have matching values, the row still gets returned, but all of the <code>TableTwo</code> values are null.  If a row has the same values in each table, I want it not to be returned at all.</p>