Query Dynamics CRM records by conection role

Question

I need to find all the contacts that are connected to a account via a Connection Role Pair. In SQL it looks like this...

SELECT ConnectionRoleBase.Name AS [Account Name], ContactBase.FullName AS [Conection Role], AccountBase.Name AS [Contact Name]
FROM ConnectionBase INNER JOIN
ConnectionRoleBase ON ConnectionBase.Record2RoleId = ConnectionRoleBase.ConnectionRoleId INNER JOIN
ContactBase ON ConnectionBase.Record2Id = ContactBase.ContactId INNER JOIN
AccountBase ON ConnectionBase.Record1Id = AccountBase.AccountId
WHERE(ConnectionRoleBase.Name LIKE N'%scientist%')

Can I do this using Odata? If so I'm not sure how to write the query because connections are not like other entities.

Can I use FetchXML? Again what how do I reference the Connection?

I need to query this from JavaScript a would prefer to use the XRMTServicesToolKit and Odata. Any suggestion or help would be appreciated.

Answer 1

You can use OData for this. The tables are ConnectionSet, ConnectionRoleSet, AccountSet, and ContactSet. You would need to do nested calls to do all of the required joins.

I prefer the CRMRestKit for doing OData queries, as I find it simplifies writing the query, but whatever tool you like should suffice. It would require using the ByQuery (aka RetrieveMultiple) in combination with Retrieve (aka retrieve individual).