Today’s guest blogger is Brad Bosak, a VP of Development at Sonoma Partners
The Dynamics CRM Web API (OData v4) has a lot of great functionality and in most cases is on par with the Organization (SOAP) service. The old Organization Data (OData v2) service, introduced in Dynamics CRM 2011, is being deprecated in favor of this new endpoint.
A few months ago, I hit a query limitation while using the Organization Data service that I had to work around. With the release of the new Web API endpoint, I wanted to revisit my options and hopefully streamline my mobile app.
The Old – Organization Data Service
The snag I encountered was the OData service’s restriction on querying multi-level relationship properties. Unfortunately, it only supports going one level deep. For example, we can query for an account and information from the account’s primary contact pretty easily:
This query will correctly return the account name along with the primary contact’s name and phone number. However, if we wanted to add another level to this query, for instance retrieve information from the primary contact record’s owning user, then things get more complex. For example, the following query does not work:
Running this will result in the following error:
The request includes an $expand path with 2 segment(s), but the maximum allowed is 1
In my case, I wasn’t working with a huge data set, so I ended up just making separate queries to get the related data needed for my mobile app. That said, the multiple query approach still bothered me, so I dug into the new endpoint to see if anything had changed.
The New – Web API
I was hoping the new Web API had more support for querying multiple levels. I’m happy to announce that it does…just not exactly how I had hoped it would.
The following is a translation of our query to retrieve the account name and related primary contact’s name and phone number:
My next step was to see if we could expand beyond 1 level. The Web API does allow you to add some additional options other than $select inside of the $expand. Supported options include $select, $filter, $orderby, and $top. Unsupported options inside of the $expand are $skip, $count, $search, $levels…and $expand. I was hoping to be able to add another expand inside of the first to go another level deeper, but doing that gave me a ‘Not Implemented’’ exception.
Things were not looking good, but good ol’ FetchXML came to the rescue!
No, I am not telling you to go use the old SOAP endpoint. The Web API actually has support for fetch queries. If we want to do our multi-level relationship query in one call, we can use the following fetch:
<fetch mapping="logical" version="1.0">
<attribute name="name" />
<condition value="56958E9C-5D67-DA11-82FE-0003FF19CD51" attribute="accountid" operator="eq" />
<link-entity name="contact" to="primarycontactid" from="contactid">
<attribute name="fullname" />
<attribute name="telephone1" />
<link-entity name="systemuser" to="ownerid" from="systemuserid">
<attribute name="internalemailaddress" />
We can then encode and execute our fetch (it’s ugly…but it does work):
Running the above query will give us a result with the following format:
"contact1_x002e_telephone1":"+1 (555) 555-5555",
In most cases the standard OData queries will get the job done, but it’s nice to know we can fall back to FetchXML if things get too complex.