Wednesday, March 23, 2016

Web API - Querying with Expand

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)

I would suggest that the best alternative is to create your query as FetchXml and submit  this query to the Web Api

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
<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();



No comments:

Post a Comment