Left Joins in LINQtoCRM

Oct 5, 2009 at 11:18 PM

 

Hello all,

I was recently trying to do some subqueries in a fairly complex query. That did not work out, so I switched to one query with many joins that I would have to process locally. No surprise, but it seems that the data is not consistent in our CRM database. Mainly, we have contracts without line items, which is a bit of an issue. Because LINQ does not have a built-in left join, I am trying to simulate it using examples on the net. I am getting a "server was unable to process request" error. My query is as follows:

var dataset = from Contact in crm.Linq<contact>()
                          join Account in crm.Linq<account>()
                          on Contact.parentcustomerid.Value equals Account.accountid.Value
                          join Contract in crm.Linq<contract>()
                          on Account.accountid.Value equals Contract.customerid.Value
                          join ContractDetail in crm.Linq<contractdetail>()
                          on Contract.contractid.Value equals ContractDetail.contractid.Value into tempholder
                          from LineItem in tempholder.DefaultIfEmpty()
                          where Contact.emailaddress1.Equals(someEmail)
                          select new { name = Contact.fullname, account = Account.name, price = Contract.totalprice, detail = (LineItem.productserialnumber==null?"NO SERIAL":LineItem.productserialnumber) }; 


Has anyone been able to do a left-join with LINQtoCRM? This is only a demo query, I plan on pulling back a lot more data, and want to avoid multiple queries if possible (overhead is too great it seems)
Thank you as always
Tim Reynolds

Coordinator
Oct 6, 2009 at 11:13 AM

You should look at the generated FetchXML. You can get it by doing something like this: provider.Log = new XmlIndentingWriter(Console.Out);

It may the emailaddress comparison that's bad. Also try and see if you can construct the query you are looking for using CRM advanced find and then inspect the FetchXML using this trick: http://ronaldlemmen.blogspot.com/2006/11/using-advanced-find-for-fetchxml.html

... and compare with the LinqtoCRM FetchXML.

Developer
Oct 6, 2009 at 11:26 AM

The unit tests contain simple examples of LeftJoins. See for example TestLeftJoin in the class QueryTestsWithNewSelector.

Dec 17, 2009 at 4:08 PM

TestLeftJoin inside of QueryTestsWithNewSelector does not work.

 

No value nodes found for Type CrmSdk.account