You already probably knew about Expand functionality in CRM 2011 OData/REST Query. It is a useful functionality to include fields from a single related entity.
But here is a quick preview:
In the example above, I want to retrieve full name of contacts and also the subject of their originating leads.
I can further do a query on the lead subject equals ‘ab’
http://.../ContactSet?$select=FullName,contact_originating_lead/Subject&$expand=contact_originating_lead&$filter=contact_originating_lead/Subject eq 'ab'
Just as easy as 1, 2, 3. 🙂
Some caveats though:
1. You can expand on multiple relationships but beware of query performance.
http://.../ContactSet?$select=FullName,contact_originating_lead/Subject,account_primary_contact/Name,contactleads_association/Address1_PostOfficeBox&$expand=contact_originating_lead,account_primary_contact,contactleads_association&$filter=FirstName eq 'ab'
In saying that I was never able to get expanding One-To-Many relationship working, and I find expanding the Many-To-Many relationship hard to work with, so the only one that works well for me was the Many-To-One. Kinda make sense to me due to the join.
2. Filtering on multiple entity types is unsupported. This means if you are adding filter, you can only filter on a single entity type.
http://.../ContactSet?$select=FullName,contact_originating_lead/Subject&$expand=contact_originating_lead&$filter=FirstName eq 'ab' and contact_originating_lead/Subject eq 'ab'
The above is unsupported because it is filtering on contact entity and also contact_orginating_lead relationship entity.
3. Although you can expand to all types of relationships, filtering with expand works only for many-to-one relationship with the entity on query.
Cheers – Sy