ENGINEERED CODE BLOG

Power Pages: FetchXML with the Web API

Recently I was asked about executing a FetchXML query using the Web API in Power Pages, and while you won’t find any specific documentation on that in the Power Pages documentation, you’ll be happy to know that it is supported. In this blog post, I’ll look at how it’s done.

Is It Supported?

As I mentioned, you won’t find a specific reference to FetchXML in the Power Pages Web API documentation. But it was when I was playing around with the sample PCF Control for Power Pages that I noticed Microsoft had included a call to the Web API that uses FetchXML.

Specifically, on line 282 of /PortalWebAPIControl/index.ts you’ll see a RetrieveMultiple call executed that uses FetchXML to calculate an aggregate value.

So, while not specifically mentioned in the documentation, the use of it in a sample is good enough for me to think this is something that is supported.

Syntax

Another reason I feel confident using it is that the format follows the standard format for using FetchXML with the Dataverse Web API, which is documented.

To execute a query, you call the endpoint for the tables you are querying, and then use the fetchXml query string parameter. Be sure to encode the FetchXML using encodeURI().

So a very simple query would look something like (borrowing the webapi wrapper available in the Microsoft documentation):

webapi.safeAjax({

  type: "GET",

  url: "/_api/contacts?fetchXml=" + encodeURI('<fetch><entity name="contact"><attribute name="firstname" /><filter><condition attribute="lastname" operator="eq" value="Hayduk" /></filter></entity></fetch>'),

  contentType: "application/json"

}); 

A few things to note when using the fetchXml parameter:

  • You can’t include additional parameters like $top, $orderby or $select. Instead, using the count attribute inside your FetchXML to limit the number of results, use the attribute elements in the FetchXML to select which columns you want returned, and specify the column you want to order by directly in your FetchXML using the element.
  • You can do things like calculate averages using the aggregate features in FetchXML. Whatever alias you give the attributes will be available in the returned JSON.
  • Table permissions are enforced, as well as the column-level permissions that are specific to the Web API (using the Webapi/[tablename]/fields Site Setting). If you ask for a table or column that you don’t have access to, you’ll get an error.
  • You can use the paging cookies if you need to, as described here.

Compared to FetchXML in Liquid

You might already know that we can execute FetchXML using the Liquid tag. With that available to us, why would we want to use it via the Web API?

The nice thing about using the Web API in client-side JavaScript is that we can use it to build interactive user experience that don’t require a page reload. Liquid is a server-side language, so in order to execute FetchXML via Liquid, we have to make a roundtrip to the server.

A common technique is to build a “custom web service” by creating a special page that returns JSON created using Liquid and FetchXML. For that web page’s Page Template you turn the header and footer off, and then create a web template that queries Dataverse using Liquid and FetchXML and outputs data in JSON format. Query string parameters passed to the page can be used in the Liquid code to allow for certain defined aspects of the query to be changed. Then you can call that page from another page using AJAX calls in JavaScript.

We’ve talked about it on our Portals Community call, and others like Carl de Souza have blogged about it.

Does the Web API’s support of FetchXML make that technique obsolete? I don’t think so. That technique is still very useful in cases where you want to protect how you data can be queried.

Once you enable the Web API for a table, you can’t really control how it is queried. If a user has access to a table via the Web API, they can use OData filters or FetchXML to query it however they want (although only rows they have access to will be returned). However, if you leave the Web API turned off, and only expose the data via your own “custom web service”, you limit how they can query it, since the only changes possible to the query are only those made explicitly available via custom query string parameters.

I’ve used this in cases where a table is technically publicly available, but the client only wanted users to be able to find row if the knew the exact name. If we used the Web API for this, a user could have constructed their own query that removes any filter parameter, getting a full list of all the rows. Instead, we created our FetchXML query in Liquid. This query only accepted a single parameter, and only returned data if a row exists that exactly matched based on the name. Since that FetchXML is defined server-side and is therefore not editable by the end user, their only option was to filter by an exact name match.

So while FetchXML via the Web API is great, using it via Liquid is still a valuable option.

 

6 responses to “Power Pages: FetchXML with the Web API”

  1. L Hahne says:

    Is it secure to use the webApi like that? As people could manipulate the encodeURI-String and retrieve a lot of data. Setting it up a proper security model seems difficult to manage with the webapi as I find.

    • Nicholas Hayduk says:

      The Web API follows the Power Pages Table Permissions model, so it is as secure as however you setup your permissions. You can’t selectively disable any of the querying options available via the Web API, so once you’ve enabled it, it is important to understand that it opens up the querying using either FetchXML or OData filters. But if you’ve given the user read permissions to all of those rows in Dataverse, typically them being able to query those rows isn’t really different from a security standpoint.

      In cases where you don’t want them to be able to query, this is where the custom web service technique I’ve described is useful.

      Nick

  2. I have noticed if you have linked entities in the fetchxml, the web api permission requires *

    Have you noticed this as well

    • Nicholas Hayduk says:

      That hasn’t been my experience. I just tried with a simple query on contacts, where I linked the account table. I was able to access the name field on the account if the Web API permission was set to just “name”.

      Nick

  3. Sean Astrakhan says:

    Very Cool! Do you know if there’s a limit to how long the query can be? e.g. If I’m adding a bunch of fetchXML conditions will it crash.

    • Nicholas Hayduk says:

      I’m not sure what the actual limit is – I haven’t run into it yet. But I do suspect that there would be a max length for the URL enforced somewhere in the technology stack used for Power Pages.

      Nick

Leave a Reply

Your email address will not be published. Required fields are marked *

Contact

Engineered Code is a web application development firm and Microsoft Partner specializing in web portals backed by Dynamics 365 & Power Platform. Led by a professional engineer, our team of technology experts are based in Regina, Saskatchewan, Canada.