title: Linq - left join on multiple (OR) conditions
body: <p>I need to do a left join on multiple conditions where the conditions are <code>OR</code>s rather than <code>AND</code>s.  I've found lots of samples of the latter but am struggling to get the right answer for my scenario.</p>
  
  <pre><code>from a in tablea
  join b in tableb on new { a.col1, a.col2 } equals new { b.col1, b.col2 }
  group a by a into g
  select new () { col1 = a.col1, col2 = a.col2, count = g.Count() }
  </code></pre>
  
  <p>works great for joins where all conditions must match.  I need to get the join to match <code>on a.col1 = b.col1 OR a.col2 = b.col2</code>.</p>
  
  <p>I know it must be easy but I've coming up blank on this!</p>
  
  <p>Edit:</p>
  
  <p>To give a little more info, the purpose of the query is to get a projection containing all of the fields from 'a' plus a count of the matching records in 'b'.  I've amended the sample above to try and illustrate what I'm after.  When I run with the above using the approach Jon Skeet has noted I'm getting a count of all records from a, not the count of the related records in b.</p>
  
  <p>The basic left join works fine:</p>
  
  <pre><code>from a in tablea
  from b in tableb
  .Where( b =&gt; ( a.col1 == b.col1 || a.col2 == b.col2))
  .DefaultIfEmpty()
  select new { col1 = a.col1, col2 = a.col2 }
  </code></pre>
  
  <p>If I revise it to add the grouping as below</p>
  
  <pre><code>from a in tablea
  from b in tableb
  .Where( b =&gt; ( a.col1 == b.col1 || a.col2 == b.col2))
  .DefaultIfEmpty()
  group a by a.col1 into g
  select new { col1 = g.Key, count = g.Count() }
  </code></pre>
  
  <p>I'm getting the count of the records returned from a - not the count of records in matching in b.</p>
  
  <p>Edit:</p>
  
  <p>I'll give the answer to Jon - I've solved my count issue - I hadn't realized I could use a lamda to filter the count <code>(g.Count(x =&gt; x != null))</code>.  Plus I need to group b by a rather than a by a as I had above. This gives the correct result but the SQL is not as efficient as I'd write it by hand as it adds a correlated sub query - if anyone can advise a better way of writing it to simulate the following SQL I'd appreciate it!</p>
  
  <pre><code>select a.col1, count(b.col1)
  from tablea a
  left join tableb b
  on a.col1 = b.col1
  or a.col2 = b.col2
  group by a.col1
  </code></pre>