Fixed – The method ‘GroupJoin’ cannot follow the method ‘SelectMany’ or is not supported while using LINQ query (Dataverse / Dynamics 365)

We might get the below error while using LINQ to query Dataverse –

System.NotSupportedException: ‘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.’

This error occurs because the GroupJoin method, which is essentially what happens during the into … syntax in LINQ, is not supported in the Dataverse/LINQ provider for Dynamics CRM. The issue is related to how the LINQ provider translates queries into FetchXML or SQL that the Dataverse understands. Specifically, nested joins or SelectMany (used by DefaultIfEmpty() in left joins) are not fully supported by the LINQ provider for Dataverse.

The GroupJoin and DefaultIfEmpty methods (used for left joins) result in queries that cannot be translated directly into FetchXML. The LINQ provider for Dataverse does not support all LINQ-to-Entities features, such as nested joins or advanced grouping logic. When you perform nested joins with DefaultIfEmpty() for left joins, the LINQ provider struggles to translate it into the underlying Dataverse query format, which is why the exception is thrown.

To fix it we can break the query into multiple steps as shown below.

However, here as we are fetching partial data into memory and combining it, it increases transfer and processing overhead and can take a long time to process based on the number of records.

The better alternative from a performance perspective would be to use FetchXML or QueryExpression here.

Also check writing complex LINQ queries

Hope it helps..