How can I link entities in a CRM 4.0 query using the web service and return columns from both?

Question

I have a query that I need to execute against CRM. It is the equivalent of the following in SQL:

SELECT A.*, B.*
FROM Table1 A INNER JOIN Table2 B
ON A.ID = B.ID

I created a query and added a linkentity as follows:

link.JoinOperator = JoinOperator.Inner;
link.LinkFromEntityName = EntityName.new_Table1.ToString();
link.LinkFromAttributeName = "new_Table1ID";
link.LinkToEntityName = EntityName.new_Table2.ToString();
link.LinkToAttributeName = "new_Table2ID";
query.LinkEntities = new LinkEntity[] { link };

However, it only returns the columns from new_Table1.

Is there a way to link in this fashion and return the columnns from both entities without having to create two queries?

Answer 1

QueryExpression doesn't support this. You'll need to use Fetch XML.

From 'Using FetchXML' in the SDK - you'll end up with something like this:

<fetch mapping='logical'>
      <entity name='account'>
<attribute name='accountid'/>
<attribute name='name'/>
<link-entity name='systemuser' to='owninguser'>
   <filter type='and'>
      <condition attribute='lastname' operator='ne' value='Cannon' />
   </filter>
   <attribute name='name' />
</link-entity>
      </entity>
   </fetch>

If you haven't used FetchXML yet, I highly recommend http://www.stunnware.com/?area=products&group=swtools4 for building and testing FetchXML. That way you can get a preview of your Result XML.

Answer 2

You can also use LinqtoCRM. A query retrieving the roles of systemusers looks like this:

var res = from u in p.Linq()
        join sr in p.Linq() on u.systemuserid.Value equals sr.systemuserid.Value
        join r in p.Linq() on sr.roleid.Value equals r.roleid.Value
        select new { u.fullname, r.name };

This works across both normal and (with CRMMetal) many-to-many relationships. You can watch a web cast here.