Dynamics CRM 2011 · Javascript · OData · REST

CRM 2011: OData/REST Query Expand Filter

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:

http://.../ContactSet?$select=FullName,contact_originating_lead/Subject&$expand=contact_originating_lead

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s