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

Question

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

score 1 · Answer 1 · answered Sep 30 '15 at 01:53

1

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.

answered Sep 30 '15 at 01:53

Henk van Boeijen

7,357
6
32
42

I'm going straight off of the "Sample: Complex LINQ queries" for my example, and it works just fine for one, but it doesn't seem to work for two. – Daryl Sep 30 '15 at 13:10
Yes, I guessed you did, but somehow left joins seem to be not fully supported. I am curious how CRM converts the Linq query to a QueryExpression though. – Henk van Boeijen Sep 30 '15 at 13:15
You could run a plugin on the RequestMultiple that spits out the Query Expression, and therefore be able to see how the conversion is being performed, but this would only work for valid LINQ queries, since the query is converted to a Query expression on the client. – Daryl Sep 30 '15 at 17:25
As a workaround, will be acceptable for you to retrieve the target of the second outer join and then perform a pure linq operation? – Mauro De Biasio Sep 30 '15 at 23:54
@Draiden that would work, but I would not describe it as being an acceptable solution. – Daryl Oct 02 '15 at 13:08

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

Microsoft Documentation:

1 Answers1