Dynamics CRM 2011 Linq Left Outer Join

Question

I am trying to get all records from an entity that do not join to another entity.
This is what I am trying to do in SQL:

SELECT * from table1 
LEFT join table2
ON table1.code = table2.code
WHERE table2.code IS NULL

It results in all table1 rows that did not join to table2.

I have it working with Linq when joining on one field, but I have contact records to join on firstname, dob, and number.

I have a "staging" entity that is imported to; a workflow processes the staging records and creates contacts if they are new.
The staging entity is pretty much a copy of the real entity.

var queryable = from staging in linq.mdo_staging_contactSet
                join contact in linq.ContactSet
                on staging.mdo_code equals contact.mdo_code
                into contactGroup
                from contact in contactGroup.DefaultIfEmpty()
                // all staging records are selected, even if I put a where clause here
                select new Contact 
                {
                    // import sequence number is set to null if the staging contact joined to the default contact, which has in id of null
                    ImportSequenceNumber = (contactContactId == null) ? new int?(subImportNo) : null,
                    /* other fields get populated */
                };
return queryable // This is all staging Contacts, the below expressions product only the new Contacts   
    .AsEnumerable() // Cannot use the below query on IQuerable
    .Where(contact => contact.ImportSequenceNumber != null); // ImportSequenceNumber is null for existing Contacts, and not null for new Contacts

Can I do the same thing using method syntax?
Can I do the above and join on multiple fields?
The alternatives I found were worse and involved using newRecords.Except(existingRecords), but with IEnumerables; is there a better way?

I was just looking into this myself. This answer seems to indicate that it isn't possible with CRMs implementation of LINQ. See the limitations section of the linked MSDN doco saying You cannot perform outer joins — Mr Moose

Greg Jones Greg Jones · Accepted Answer · 2013-10-10T18:55:41

You can do the same thing with method calls, but some tend to find it harder to read since there are some LAMBDA expressions in the middle. Here is an example that shows how the two are basically the same.

I've seen others ask this same questions and it boils down to choice by the developer. I personally like the LINQ approach since I also write a bunch of SQL and I can read the code easier.

Dynamics CRM 2011 Linq Left Outer Join

1 Answers