Using QueryExpressions on the Dataverse Web Api

Introduction

When using the Dataverse (OData) Web Api, there are four documented/supported ways to fetch data:

• OData query options ($filter, $select, etc)
• The ‘fetchXml’ query parameter (/api/data/v9.2/accounts?fetchXml=)
• Passing a view id (/api/data/v9.2/accounts?savedQuery= and /api/data/v9.2/accounts?userQuery=)
• Using bound/unbound actions/functions (/api/data/v9.2/WhoAmI)
• Please let me know if there are more options!

It turns out, that there is another method that we can use! This is the ‘queryExpression’ query parameter.

Using queryExpression on the Web Api

We can pass a JSON serialized queryExpression to the Web Api. This is the most simple example:

[DataverseOrgUrl]/api/data/v9.2/accounts?queryExpression={“entityName”:”account”}

We can also pass in filters, columns, order, etc:

{
  "Distinct": false,
  "PageInfo": {
    "PageNumber": 0,
    "Count": 0,
    "ReturnTotalRecordCount": false,
    "PagingCookie": null
  },
  "LinkEntities": [],
  "Criteria": {
    "FilterOperator": 0,
    "Conditions": [
      {
        "CompareColumns": false,
        "AttributeName": "accountnumber",
        "Operator": 6,
        "Values": [
          "%1"
        ],
        "EntityName": null
      }
    ],
    "Filters": []
  },
  "Orders": [
    {
      "AttributeName": "accountnumber",
      "OrderType": 1,
      "Alias": null,
      "EntityName": null
    }
  ],
  "EntityName": "account",
  "ColumnSet": {
    "AllColumns": false,
    "Columns": [
      "accountnumber"
    ],
    "AttributeExpressions": []
  },
  "NoLock": false
}

This JSON string was constructed using the following C# code:

var qeAccount = new QueryExpression("account");
qeAccount.ColumnSet = new ColumnSet("accountnumber");
qeAccount.Criteria.AddCondition("accountnumber", ConditionOperator.Like, "%1");
qeAccount.AddOrder("accountnumber", OrderType.Descending);

var qeAccountJson = JsonConvert.SerializeObject(qeAccount, Newtonsoft.Json.Formatting.Indented);

Useful? It depends.

So is this useful? It certainly has its use cases. My personal opinion though is that ‘static’ fetchXml queries are easier to read and grasp than queryExpressions. This counts for both C# code and serialized to JSON. Especially when lots of joins are involved, fetchXml is just way easier to understand and edit. However, when building dynamic queries in code, I prefer to use queryExpression. Because JSON is native to Javascript, this might be great for use in Model Driven Apps. I think that in a lot of cases, building a queryExpression will be easier do to in javascript than building OData queries. Especially if you don’t to depend on any third party libraries to help with the OData queries. I think that building very complex filters/joins/etc might be way easier using this method, than any other method available.

Another use case that I can think of, is when you are heavily using fetchXml queries in javascript (Model Driven Apps). Adding dynamics filters/joins/columns etc. to the fetchXml is very finicky to do through javascript. You can use the ‘FetchXmlToQueryExpression’ api to convert the fetchXml to a JSON serialized QueryExpression. You can then go ahead and modify the QueryExpresssion, and then use that on the web api.

For anyone wondering how, you can use the ‘FetchXmlToQueryExpression’ api like so:

[DataverseOrgUrl]/api/data/v9.2/FetchXmlToQueryExpression(FetchXml=@fetch)?@fetch='<fetch distinct="false" useraworderby="true" no-lock="false" mapping="logical"> <entity name="contact"> <attribute name="fullname"/> <filter type="and"> <filter type="and"> <link-entity name="task" to="contactid" from="regardingobjectid" link-type="all"> <filter type="and" anyallalias="task1"> <condition attribute="statecode" operator="ne" value="0"/> </filter> </link-entity> </filter> </filter> </entity> </fetch>'

Supported? No (t yet?)

This is not documented and thus not supported by Microsoft. The queryExpression query parameter may stop working at any moment. But they also might add it this to the documentation, who knows. So use at your own risk! Microsoft has recently updated the FetchXml documentation as well, adding some new features that were previously undocumented, so it might be possible that this queryExpression parameter will get full support as well.