Dynamics CRM 2011 - Filtering LINQ query with outer joins

2
votes

I have a requirement to query for records in CRM that don't 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.

For example:

var query = from c in orgContext.CreateQuery<Contact>()
            join aj in orgContext.CreateQuery<Account>()
                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,
                   };

This should return me any Contats that are not the Primary Contact of an account. However, this query ends up returning all contacts...! When you look at the SQL that gets generated in SQL Profiler it comes out like this:

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

So, I get the Left Join OK, but the filter is on the Join clause, and not in a WHERE clause.and not as it should, like this:

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

Clearly, the results from this query are very different! Is there a way to get the query on CRM to generate the correct SQL?

Is this a limitation of the underlying FetchXML request?

3
linqdynamics-crmdynamics-crm-2011
which table does "Name" column belong to? – Hasan Fahim
@Hassan, Name is a field on the Account entity. I just chose a field that I thought should always have data for this example. In reality my query is not between built in entities. – Mark

3 Answers

7
votes

Unfortunately, this is a limitation of CRM's LINQ and FetchXML implementations. This page from the SDK states outer joins are not supported:

http://technet.microsoft.com/en-us/library/gg328328.aspx

And while I can't find an official document, there are a lot of results out there for people mentioning FetchXML does not support left outer joins, for example:

http://gtcrm.wordpress.com/2011/03/24/fetch-xml-reports-for-crm-2011-online/

1
votes

Try this:

var query = from c in orgContext.CreateQuery<Contact>()
            where orgContext.CreateQuery<Account>().All(aj => c.ContactId != aj.PrimaryContactId.Id)                
            select new Contact
            {
                   FirstName = c.FirstName,
                   LastName = c.LastName,
            };
0
votes

If you don't need to update the entity (e.g. to process all the corresponding validation rules and workflow steps), you can write less-ugly and more efficient queries by hitting the SQL Server directly.

Per CRM's pattern, the views take care of most of the common joins for you. For instance, the dbo.ContactBase and dbo.ContactExtensionBase tables are already joined for you in the view dbo.Contact. The AccountName is already there (called AccountIdName for some bizarre reason, but at least it's there).