CODEWITHSUNDEEP
dynamics-crmdynamics-crm-onlinedynamics-crm-2015
Daryl
Daryl

Reputation: 18895

Performing two Left Outer Joins in a Single LINQ to CRM query

I'm attempting to perform two left outer joins in my CRM online 2015 Update 1 instance, but I am getting an error. This is what I have currently:

var query_join8 = from a in crmContext.AccountSet
                  join c in crmContext.ContactSet
                  on a.PrimaryContactId.Id equals c.ContactId
                  into gr
                  from c_joined in gr.DefaultIfEmpty()
                  join c in crmContext.ContactSet
                  on a.Name equals c.FullName
                  into gr2
                  from c2_joined in gr2.DefaultIfEmpty()
                  select new
                  {
                      contact_name = c_joined.FullName,
                      account_name = a.Name,
                      other_name = c2_joined.FullName
                  };

When I attempt to execute it, I get this error:

An exception of type 'System.NotSupportedException' occurred in Microsoft.Xrm.Sdk.dll but was not handled in user code

Additional information: The method 'GroupJoin' cannot follow the method 'SelectMany' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.

If I comment out the second Join, it works fine:

var query_join8 = from a in crmContext.AccountSet
                  join c in crmContext.ContactSet
                  on a.PrimaryContactId.Id equals c.ContactId
                  into gr
                  from c_joined in gr.DefaultIfEmpty()
                  //join c in crmContext.ContactSet
                  //on a.Name equals c.FullName
                  //into gr2
                  //from c2_joined in gr2.DefaultIfEmpty()
                  select new
                  {
                      contact_name = c_joined.FullName,
                      account_name = a.Name,
                      //other_name = c2_joined.FullName
                  };

Microsoft Documentation:

Defining how to perform a Left Join: http://msdn.microsoft.com/en-us/library/gg509017.aspx#LeftJoin Blog Describing that it is supported: http://blogs.msdn.com/b/crminthefield/archive/2013/01/14/crm-2011-sdk-query-limitations-by-api.aspx

Upvotes: 4

Views: 3350

Answers (1)

Henk van Boeijen
Henk van Boeijen

Reputation: 7918

The documentation on CRM 2015 still states outer joins are not supported. (MSDN: Use LINQ to construct a query) I know there is also an example on MSDN showing a left join (Sample: Complex LINQ queries), but I doubt if under the hood this is actually transformed into a single QueryExpression. So, I guess you are hitting the limits of the capabilities of Ling for CRM.

Upvotes: 1

Related Questions