Left Outer join LINQ query with four entities in CRM 2011 and with multiple where clauses

Question

I have a situation in Microsoft Dynamics CRM 2011 Here I have a set of Accounts which has Permits and permits ae related to permitCounty and permitCounty is related to County

I would like to get get an output like this query.

But it is not proper

strSearch="Some text";

var lstItems = (from county in x.De_countySet
                join permitcounty in x.de_permitcountySet on county.De_countyId equals permitcounty.de_CountyId.Id
                join permit in x.de_permitSet on permitcounty.de_PermitId.Id equals permit.de_permitId
                join account in x.AccountSet on permit.de_AccountId.Id equals account.AccountId into outer

                where permit.de_Type.Equals("Type1") && permit.statuscode.Equals("Active")                // Permit Type as Type1 and Status as Active
                where account.De_LegalName.Contains(strSearch) || account.Name.Contains(strSearch)        // Search in Legal Name and Account full name
                where county.de_CountyName.Contains(strSearch)                                            // Search in County Name

                orderby county.de_CountyName
                from account in outer.DefaultIfEmpty()
                select new clsTemp
                {
                    Name = account.Name,
                    De_LegalName = account.De_LegalName,
                    Address1_Line1 = account.Address1_Line1,
                    Address1_City = account.Address1_City,
                    Address1_PostalCode = account.Address1_PostalCode,

                    de_CountyName = county.de_CountyName              
                 }).ToList();

Any help is appreciated.

Vinu

What makes it "not proper"? Is the result set incorrect? Is there an exception? — Aducci, Oct 19 '12 at 17:31
"The method 'GroupJoin' cannot follow the method 'Join' 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 is the error message I am getting I have tried to update the query with "ToList" like x.De_countySet, but no use — Vinu, Oct 19 '12 at 17:38
What are the exact types of `x.De_countySet`, `x.de_permitcountySet`, `x.de_permitSet`, `x.AccountSet`? It sounds like at least one of them does not support LINQ. — goric, Oct 19 '12 at 18:11

Risky Martin · Answer 1 · 2012-10-22T23:21:14.333

Here is an alternate way to write the group join. Note that the line containing into was the only line that was replaced:

strSearch="Some text";

var lstItems = (from county in x.De_countySet
                join permitcounty in x.de_permitcountySet on county.De_countyId equals permitcounty.de_CountyId.Id
                join permit in x.de_permitSet on permitcounty.de_PermitId.Id equals permit.de_permitId
                let outer = x.AccountSet.Where(account => permit.de_AccountId.Id == account.AccountId)

                where permit.de_Type.Equals("Type1") && permit.statuscode.Equals("Active")                // Permit Type as Type1 and Status as Active
                // "account" is not in scope and should cause a design-time error
                where account.De_LegalName.Contains(strSearch) || account.Name.Contains(strSearch)        // Search in Legal Name and Account full name
                where county.de_CountyName.Contains(strSearch)                                            // Search in County Name

                orderby county.de_CountyName
                from account in outer.DefaultIfEmpty()
                select new clsTemp
                {
                    Name = account.Name,
                    De_LegalName = account.De_LegalName,
                    Address1_Line1 = account.Address1_Line1,
                    Address1_City = account.Address1_City,
                    Address1_PostalCode = account.Address1_PostalCode,

                    de_CountyName = county.de_CountyName              
                 }).ToList();

When it's written this way, an obvious error comes to light (account not being in scope). Perhaps there are other issues too. A county without any permits won't show up, while a county with multiple permits that don't have any accounts will have multiple rows with blank accounts. Is this the functionality that you want?

Edit: Second Try:

strSearch="Some text";

var lstItems = (from county in x.De_countySet
                join permitcounty in x.de_permitcountySet on county.De_countyId equals permitcounty.de_CountyId.Id
                join permit in x.de_permitSet on permitcounty.de_PermitId.Id equals permit.de_permitId


                where permit.de_Type.Equals("Type1") && permit.statuscode.Equals("Active")                // Permit Type as Type1 and Status as Active

                where county.de_CountyName.Contains(strSearch)                                            // Search in County Name

                orderby county.de_CountyName
                from account in x.AccountSet.Where(account => 
                    permit.de_AccountId.Id == account.AccountId &&
                    (account.De_LegalName.Contains(strSearch) || 
                     account.Name.Contains(strSearch))).DefaultIfEmpty()   // Search in Legal Name and Account full name
                select new clsTemp
                {
                    Name = account.Name,
                    De_LegalName = account.De_LegalName,
                    Address1_Line1 = account.Address1_Line1,
                    Address1_City = account.Address1_City,
                    Address1_PostalCode = account.Address1_PostalCode,

                    de_CountyName = county.de_CountyName              
                 }).ToList();

Hi Risky Martin, Thank you very much for the suggestion. When I tried to implemented the change and when I was trying to access the web page, I got the error like this "System.NotSupportedException: The method 'Where' cannot follow the method 'Select' or is not supported. Try writing the query in terms of supported methods or call the 'AsEnumerable' or 'ToList' method before calling unsupported methods.". I have modified the selection to handle the null values by "Name = account["Name"] == null ? null : account["Name"].ToString()," resulting same error I expect same out put as you explained — Vinu, Oct 22 '12 at 13:43
@Vinu It looks like we're going to have to keep trying different methods until we find one it likes. — Risky Martin, Oct 23 '12 at 00:33
@RiskyMartin - you seem fairly knowledgeable on this could you take a look please at my question to see if you can shed any light: http://stackoverflow.com/questions/16307738/linq-where-clause-throwing-error-with-2nd-where — Phill Healey, May 01 '13 at 15:34

Left Outer join LINQ query with four entities in CRM 2011 and with multiple where clauses

1 Answers1