Subqueries

Oct 1, 2009 at 2:58 PM

I am having some trouble figuring out how to write this query, and determining if LINQtoCRM supports this (not shown in any examples)

Basically, what I want is, to be able to make a query that will return the following data structure:

{
 Contact,
 Account,
 Contracts = {
                     { Contract1, {Set of Contract Details for Contract 1}}
                     { ContractN, {Set of Contract Details for Contract N}}

                   }

}

I figured I could write this like:

from Contact in crm.Linq<contact>()
join Account in crm.Linq<account>()
on Contact.parentcustomerid.Value equals Account.accountid.Value
where Contact.emailaddress1.Equals(someEmail)
select new {
  Contact,
  Account,
  Contracts = (from Contract in crm.Linq<contract>()
                     where Contract.customerid.Value == Account.accountid.Value
                     select new {
                                        Contract,
                                        LineItems = (from Detail in crm.Linq<contractdetail>()
                                        where Detail.contractid.Value == Contract.contractid.Value
                                        select Detail)
                                      }
                     )
    }

 

I have no luck with this however. I get the dreaded "Lambda Parameter not in scope" error in QueryFormatter on line 474.

Is this type of query possible?

Thank you very much for any help
Tim

Coordinator
Oct 1, 2009 at 4:57 PM

This code query translates to FetchXML OK, but I haven't tested whether it returns the right results (it should though):

         var foo = from Contact in p.Linq<contact>()
                      join Account in p.Linq<account>()
                        on Contact.parentcustomerid.Value equals Account.accountid.Value
                      join Contract in p.Linq<contract>()
                        on Account.accountid.Value equals Contract.customerid.Value
                      join Detail in p.Linq<contractdetail>()
                        on Contract.contractid.Value equals Detail.contractid.Value
                      where Contact.emailaddress1.Equals("")
                      select new
                      {
                          Contact,
                          Account,
                          Contract,
                          Detail,
                      };

If it works, you have would have to bang on the result somewhat, to get out put you are looking for (i.e. group by contactid and accountid). You should probably do this by calling ToList() on the query above to get it executed, and then use Linq to Objects to the banging out of CRM.

Sorry, that's the best I can do. Please post if you get this working, I'm sure other users would be interested.

Michael

Oct 1, 2009 at 5:14 PM

friism,

 

I will try this shortly. I can't believe it never occurred to me to use Linq on the resulting objects. I will let you know.

 

Thanks

Tim

 

Oct 5, 2009 at 11:10 PM

friism,

 

This works, but only if the data is all there. I am having trouble getting a left-join to work in LinqtoCRM. I am going to start a new post on it.

Thanks

Tim