Web API – Querying with Expand
By Joe Gill
Published On 23rd March 2016
From version 2016 onwards the Web Api in Dynamics CRM should be the preferred choice for new development as the old rest services are being deprecated. Something I noticed when I started to use the Web Api endpoint is that support for $expand is limited when retrieving multiple records.
The Expand option is used to retrieve related entity data as part of a request uri and for Web Api requests it only works when querying a single record. So for example using the old rest services you could query the accounts entity and use the expand option to also return the email address of the primary contact as follows
//XRMServices/2011/OrganizationData.svc/AccountSet?$select=Name,account_primary_contact/EMailAddress1& $expand=account_primary_contact
If you try and do the same query using the Web Api you will get the error “The navigation properties are not supported with $select clause”
//api/data/v8.0/accounts()?$select=accountnumber,name&$expand=primarycontactid($select=emailaddress1)
Interestingly expand does work if the query is for a single record
//api/data/v8.0/accounts(7614f8f4-55e2-e511-80dd-5065f38a79e1)?$select=accountnumber,name& $expand=primarycontactid($select=emailaddress1)
The Expand option is used to retrieve related entity data as part of a request uri and for Web Api requests it only works when querying a single record. So for example using the old rest services you could query the accounts entity and use the expand option to also return the email address of the primary contact as follows
//XRMServices/2011/OrganizationData.svc/AccountSet?$select=Name,account_primary_contact/EMailAddress1& $expand=account_primary_contact
If you try and do the same query using the Web Api you will get the error “The navigation properties are not supported with $select clause”
//api/data/v8.0/accounts()?$select=accountnumber,name&$expand=primarycontactid($select=emailaddress1)
Interestingly expand does work if the query is for a single record
//api/data/v8.0/accounts(7614f8f4-55e2-e511-80dd-5065f38a79e1)?$select=accountnumber,name& $expand=primarycontactid($select=emailaddress1)
I would suggest that the best alternative is to create your query as FetchXml and submit this query to the Web Api
<entity name=“account“>
<attribute name=“name“ />
<attribute name=“accountnumber“ />
<link-entity name=“contact“ from=“contactid“ to=“primarycontactid“ >
<attribute name=“emailaddress1“ />
</link-entity>
</entity>
</fetch>
I would suggest using the excellent Rest Builder to encode the FetchXML and generate the required JavaScript to call the Web Api endpoint
var req = new XMLHttpRequest();
req.open(“GET”, Xrm.Page.context.getClientUrl() + “/api/data/v8.0/accounts?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22account%22%3E%3Cattribute%20name%3D%22name%22%20%2F%3E%3Cattribute%20name%3D%22accountnumber%22%20%2F%3E%3Corder%20attribute%3D%22name%22%20descending%3D%22false%22%20%2F%3E%3Clink-entity%20name%3D%22contact%22%20from%3D%22contactid%22%20to%3D%22primarycontactid%22%20%3E%3Cattribute%20name%3D%22emailaddress1%22%20%2F%3E%3C%2Flink-entity%3E%3C%2Fentity%3E%3C%2Ffetch%3E”, true);
req.setRequestHeader(“OData-MaxVersion”, “4.0”);
req.setRequestHeader(“OData-Version”, “4.0”);
req.setRequestHeader(“Accept”, “application/json”);
req.setRequestHeader(“Prefer”, “odata.include-annotations=”OData.Community.Display.V1.FormattedValue””);
req.onreadystatechange = function () {
if (this.readyState === 4) {
req.onreadystatechange = null;
if (this.status === 200) {
var results = JSON.parse(this.response);
}
else {
alert(this.statusText);
}
}
};
req.send();
req.open(“GET”, Xrm.Page.context.getClientUrl() + “/api/data/v8.0/accounts?fetchXml=%3Cfetch%20version%3D%221.0%22%20output-format%3D%22xml-platform%22%20mapping%3D%22logical%22%20distinct%3D%22false%22%3E%3Centity%20name%3D%22account%22%3E%3Cattribute%20name%3D%22name%22%20%2F%3E%3Cattribute%20name%3D%22accountnumber%22%20%2F%3E%3Corder%20attribute%3D%22name%22%20descending%3D%22false%22%20%2F%3E%3Clink-entity%20name%3D%22contact%22%20from%3D%22contactid%22%20to%3D%22primarycontactid%22%20%3E%3Cattribute%20name%3D%22emailaddress1%22%20%2F%3E%3C%2Flink-entity%3E%3C%2Fentity%3E%3C%2Ffetch%3E”, true);
req.setRequestHeader(“OData-MaxVersion”, “4.0”);
req.setRequestHeader(“OData-Version”, “4.0”);
req.setRequestHeader(“Accept”, “application/json”);
req.setRequestHeader(“Prefer”, “odata.include-annotations=”OData.Community.Display.V1.FormattedValue””);
req.onreadystatechange = function () {
if (this.readyState === 4) {
req.onreadystatechange = null;
if (this.status === 200) {
var results = JSON.parse(this.response);
}
else {
alert(this.statusText);
}
}
};
req.send();
