title: Dynamics CRM 2011 - Filtering LINQ query with outer joins
body: <p>I have a requirement to query for records in CRM that <em>don't</em> have a related entity of a certain type. Normally, I would do this with an Left Outer Join, then filter for all the rows that have NULLs in the right-hand side.</p>
  
  <p>For example:</p>
  
  <pre><code>var query = from c in orgContext.CreateQuery&lt;Contact&gt;()
              join aj in orgContext.CreateQuery&lt;Account&gt;()
                  on c.ContactId equals aj.PrimaryContactId.Id
              into wonk
              from a in wonk.DefaultIfEmpty()
              where a.Name == null
              select new Contact
                     {
                         FirstName = c.FirstName,
                         LastName = c.LastName,
                     };
  </code></pre>
  
  <p>This should return me any Contats that are not the Primary Contact of an account. However, this query ends up returning <strong>all</strong> contacts...! When you look at the SQL that gets generated in SQL Profiler it comes out like this:</p>
  
  <pre><code>SELECT cnt.FirstName, cnt.LastName
  FROM Contact as cnt
      LEFT OUTER JOIN Account AS acct
          ON cnt.ContactId = acct.PrimaryContactId AND acct.Name is NULL
  </code></pre>
  
  <p>So, I get the Left Join OK, but the filter is <em>on</em> the <code>Join</code> clause, and not in a <code>WHERE</code> clause.and not as it should, like this:</p>
  
  <pre><code>SELECT cnt.FirstName, cnt.LastName
  FROM Contact as cnt
      LEFT OUTER JOIN Account AS acct
          ON cnt.ContactId = acct.PrimaryContactId
  WHERE acct.Name is NULL
  </code></pre>
  
  <p>Clearly, the results from this query are very different! Is there a way to get the query on CRM to generate the correct SQL?</p>
  
  <p>Is this a limitation of the underlying FetchXML request?</p>