Odata expressions

A collection of snippets and samples related to the Power Platform.

Odata expressions

References for Odata filters

In this article

Filter results

Use the $filter system query option to set criteria for which entities will be returned.

Standard filter operators

The Web API supports the standard OData filter operators listed in the following table.

Operator Description Odata Filter field example
Comparison Operators    
eq Equal revenue eq 100000
ne Not Equal revenue ne 100000
gt Greater than revenue gt 100000
ge Greater than or equal revenue ge 100000
lt Less than revenue lt 100000
enddate lt utcnow() (comparisons work with dates)
le Less than or equal revenue le 100000
Logical Operators    
and Logical and revenue lt 100000 and revenue gt 2000
or Logical or contains(name,'(sample)') or contains(name,'test')
not Logical negation not contains(name,'sample')
Grouping Operators    
( ) Precedence grouping (contains(name,'sample') or contains(name,'test')) and revenue gt 5000

[!NOTE] This is a sub-set of the 11.2.5.1.1 Built-in Filter Operations. Arithmetic operators and the comparison has operator are not supported in the Web API.

Standard query functions

The Web API supports these standard OData string query functions:

Function Odata Filter field example
contains contains(name,'(sample)')
endswith endswith(name,'Inc.')
startswith startswith(name,'a')

[!NOTE] This is a sub-set of the 11.2.5.1.2 Built-in Query Functions. Date, Math, Type, Geo and other string functions aren’t supported in the web API.

Checking for null or non-null values

In the Odata filter, there’s a nuance to filtering on null values. The “null” expression from the assistant is not needed. Instead, just use plain text in the odata query.

The following example will include rows where ctd_myfield contains data

ctd_myfield ne null

Dataverse Web API query functions

Dataverse provides a number of special functions that accept parameters, return Boolean values, and can be used as filter criteria in a query. See for a list of these functions. The following is an example of the searching for accounts with a number of employees between 5 and 2000.

GET [Organization URI]/api/data/v9.1/accounts?$select=name,numberofemployees
&$filter=Microsoft.Dynamics.CRM.Between(PropertyName='numberofemployees',PropertyValues=["5","2000"])

More information: Compose a query with functions.

Use Lambda operators

The Web API allows you to use two lambda operators, which are any and all to evaluate a Boolean expression on a collection.

any operator

The any operator returns true if the Boolean expression applied is true for any member of the collection, otherwise it returns false. The any operator without an argument returns true if the collection is not empty.

Aggregate and Grouping results

By using $apply you can aggregate and group your data dynamically. Possible use cases with $apply:

Use Case Example
List of unique statuses in the query accounts?$apply=groupby((statuscode))
Aggregate sum of the estimated value opportunities?$apply=aggregate(estimatedvalue with sum as total)
Average size of the deal based on estimated value and status opportunities?$apply=groupby((statuscode),aggregate(estimatedvalue with average as averagevalue)
Sum of estimated value based on status opportunities?$apply=groupby((statuscode),aggregate(estimatedvalue with sum as total))
Total opportunity revenue by Account name opportunities?$apply=groupby((parentaccountid/name),aggregate(estimatedvalue with sum as total))
Primary contact names for accounts in ‘WA’ accounts?$apply=filter(address1_stateorprovince eq 'WA')/groupby((primarycontactid/fullname))
Last created record date and time accounts?$apply=aggregate(createdon with max as lastCreate)
First created record date and time accounts?$apply=aggregate(createdon with min as firstCreate)

The aggregate functions are limited to a collection of 50,000 records. Further information around using aggregate functionality with Dataverse can be found here: Use FetchXML to construct a query.

Additional details on OData data aggregation can be found here: OData Extension for Data Aggregation Version 4.0. Note that Dataverse supports only a sub-set of these aggregate methods.

Videos

Odata introduction - Jon Levesque and Ahmad Najjar on YouTube

Odata website

Web API Documentation on docs

Effective November 2020:

  • Common Data Service (CDS) has been renamed to Microsoft Dataverse. Learn more
  • Some terminology in Microsoft Dataverse has been updated. For example, entity is now table and field is now column. Learn more