Query data using the Web API
When you use the Web API to create a query against a Dataverse table, you need to make the following decisions:
Decision | Description |
---|---|
Select columns | Which columns of data to return |
Join tables | Which related tables to include in the results |
Order rows | What order to return the results |
Filter rows | Which rows of data to return |
Page results | How many rows of data to return |
Aggregate data | How to group and aggregate the data returned |
Count number of rows | How to count the number of rows |
This article is about querying data found in tables. You can also use Web API to query data about table definitions, or entity metadata. The structure of the data is different, so many of the capabilities described here do not apply. More information: Query table definitions using the Web API and Query schema definitions
Entity collections
Every query begins with a collection of entities. Entity collections can be:
- EntitySet resources: One of the Web API EntitySet collections.
- Filtered collections: A set of entities returned by a collection-valued navigation property for a specific record.
- An expanded collection-valued navigation property. More information: Expand collection-valued navigation properties
EntitySet resources
To find all the EntitySet resources available in your environment, send a GET
request to the Web API service document:
Request:
GET [Organization URI]/api/data/v9.2/
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata",
"value": [
{
"name": "aadusers",
"kind": "EntitySet",
"url": "aadusers"
},
{
"name": "accountleadscollection",
"kind": "EntitySet",
"url": "accountleadscollection"
},
{
"name": "accounts",
"kind": "EntitySet",
"url": "accounts"
},
... <Truncated for brevity>
[
}
Tip
These values are usually the plural name of the table, but they can be different. Use this query to confirm you're using the correct EntitySet resource name.
To retrieve data from the account EntityType, you start with the accounts
EntitySet resource.
GET [Organization URI]/api/data/v9.2/accounts?$select=name
Filtered collections
You can query any collection of entities represented by a collection-valued navigation property of a specified record.
If you want to retrieve data from the account EntityType, where a specific user is the OwningUser, you can use the user_accounts
collection-valued navigation property from the specified systemuser record.
GET [Organization URI]/api/data/v9.2/systemusers(<systemuserid value>)/user_accounts?$select=name
To locate the name of the collection-valued navigation property:
- For any Dataverse tables and relationships, you can check the Web API Entity Type Reference
- For any custom tables or relationships, look for the collection-valued navigation properties within the $metadata service document
OData query options
The following table describes the OData query options the Dataverse Web API supports.
Option | Use to | More information |
---|---|---|
$select |
Request a specific set of properties for each entity or complex type. | Select columns |
$expand |
Specify the related resources to be included in line with retrieved resources. | Join tables |
$filter |
Filter a collection of resources. | Filter rows |
$orderby |
Request resources in a particular order. | Order rows |
$apply |
Aggregate and group your data. | Aggregate data |
$top |
Specify the number of items in the queried collection to be included in the result. Don't use $top when you retrieve pages of data. |
Use the $top query option |
$count |
Request a count of the matching resources included with the resources in the response. | Count number of rows |
You can apply multiple options to a query. Separate query options from the resource path with a question mark (?). Separate each option after the first with an ampersand (&). Option names are case-sensitive.
The Dataverse Web API doesn't support these OData query options: $skip
,$search
,$format
.
Use parameter aliases with query options
You can use parameter aliases for $filter
and $orderby
query options, but not inside the $expand
option. Parameter aliases allow you to use the same value multiple times in a request. If the alias isn't assigned a value, it's assumed to be null.
Without parameter aliases:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue
&$orderby=revenue asc,name desc
&$filter=revenue ne null
With parameter aliases:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue
&$orderby=@p1 asc,@p2 desc
&$filter=@p1 ne @p3&@p1=revenue&@p2=name
You can also use parameter aliases when using functions. More information: Use Web API functions
Select columns
Important
When you query data, it's important to limit the amount of data returned to optimize performance. Only select the columns with data that you need.
Use the $select
query option to choose which columns to return with your query. In OData, every column is represented as a property. If you don't include a $select
query option, all properties are returned.
The following example requests the name
and revenue
properties from one row of the accounts
EntitySet resource:
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue&$top=1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,revenue)",
"value": [
{
"@odata.etag": "W/\"81052965\"",
"name": "Litware, Inc. (sample)",
"revenue": 20000.0000,
"_transactioncurrencyid_value": "228f42f8-e646-e111-8eb7-78e7d162ced1",
"accountid": "4624eff7-53d3-ed11-a7c7-000d3a993550"
}
]
}
The primary key property is always returned so you don't need to include it in your $select
. In this example, accountid
is the primary key.
Other property values may also be included. In this case, the _transactioncurrencyid_value
lookup property for the related Currency (TransactionCurrency) table/entity reference is included because revenue
is a currency property.
Which properties are available?
All the available properties for an entity are found in the $metadata service document. More information: Web API Properties
The entity types included with Dataverse are described in the Web API Entity Type Reference.
Tip
The easiest way to quickly discover which properties are available is to send a request using the $top
query option with a value of 1
without using $select
.
Formatted values
Formatted values are string values generated on the server that you can use in your application. Formatted values include:
- The localized labels for choice, choices, yes/no, status, and status reason columns
- The primary name value for lookup and owner properties
- Currency values with currency symbols
- Formatted date values in the user's time zone
To include formatted values in your results, use this request header:
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Formatted values are one of several annotations you can request. Use Prefer: odata.include-annotations="*"
to include all annotations. More information: Request annotations
The formatted value is returned with the record with an annotation that follows this convention:
<property name>@OData.Community.Display.V1.FormattedValue
For example:
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue,_primarycontactid_value,customertypecode,modifiedon
&$top=1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
The following table describes the values and formatted values that are returned for the requested properties.
Property | Value | Formatted value |
---|---|---|
name |
Litware, Inc. (sample) |
None |
revenue |
20000.0000 |
$20,000.00 |
_primarycontactid_value |
70bf4d48-34cb-ed11-b596-0022481d68cd |
Susanna Stubberod (sample) |
customertypecode |
1 |
Competitor |
modifiedon |
2023-04-07T21:59:01Z |
4/7/2023 2:59 PM |
_transactioncurrencyid_value |
228f42f8-e646-e111-8eb7-78e7d162ced1 |
US Dollar |
accountid |
78914942-34cb-ed11-b596-0022481d68cd |
None |
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,revenue)",
"value": [
{
"@odata.etag": "W/\"81359849\"",
"name": "Litware, Inc. (sample)",
"revenue@OData.Community.Display.V1.FormattedValue": "$20,000.00",
"revenue": 20000.0000,
"_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
"_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"customertypecode@OData.Community.Display.V1.FormattedValue": "Competitor",
"customertypecode": 1,
"modifiedon@OData.Community.Display.V1.FormattedValue": "4/7/2023 2:59 PM",
"modifiedon": "2023-04-07T21:59:01Z",
"_transactioncurrencyid_value@OData.Community.Display.V1.FormattedValue": "US Dollar",
"_transactioncurrencyid_value": "228f42f8-e646-e111-8eb7-78e7d162ced1",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd"
}
]
}
Lookup property data
When a lookup property represents a multi-table, or polymorphic, relationship, you need to request specific annotations to determine which table contains the related data.
For example, many tables have records that users or teams may own. Ownership data is stored in a lookup column named ownerid
. This column is a single-valued navigation property in OData. You could use $expand
to create a join to get this value, but you can't use $select
. However, you can use the corresponding _ownerid_value
lookup property with $select
.
When you include the _ownerid_value
lookup property with your $select
, it returns a GUID value. This value doesn't tell you whether the owner of the record is a user or a team. You need to request annotations to get this data.
To include these annotations in your results, use this request header:
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"
Tip
Or you can use Prefer: odata.include-annotations="*"
to include all annotations. More information: Request annotations
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,_ownerid_value&$top=2
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"
The following response returns two different account records. A team
owns the first one, and a systemuser
owns the second one. The _ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname
annotation provides this information.
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="Microsoft.Dynamics.CRM.associatednavigationproperty,Microsoft.Dynamics.CRM.lookuplogicalname"
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_ownerid_value)",
"value": [
{
"@odata.etag": "W/\"81550512\"",
"name": "Adventure Works (sample)",
"_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
"_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "team",
"_ownerid_value": "39e0dbe4-131b-e111-ba7e-78e7d1620f5e",
"accountid": "1adef0b8-54d3-ed11-a7c7-000d3a993550"
},
{
"@odata.etag": "W/\"81359849\"",
"name": "Litware, Inc. (sample)",
"_ownerid_value@Microsoft.Dynamics.CRM.associatednavigationproperty": "ownerid",
"_ownerid_value@Microsoft.Dynamics.CRM.lookuplogicalname": "systemuser",
"_ownerid_value": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd"
}
]
}
<lookup property name>@Microsoft.Dynamics.CRM.lookuplogicalname
is the logical name of the related table.<lookup property name>@Microsoft.Dynamics.CRM.associatednavigationproperty
is the name of the corresponding single-valued navigation property. You can use$expand
using this value in another request to get more data from the related record.
Join tables
To control what data is returned from related table records, use the $expand
query option with navigation properties.
- You can include up to 15
$expand
options in a query. Each$expand
option creates a join that can affect performance. - Queries which expand collection-valued navigation properties may return cached data for those properties that doesn't reflect recent changes. It is recommended to use
If-None-Match
header with valuenull
to override browser caching. More information: HTTP Headers for more details.
The following table describes the query options you can apply in certain $expand
options:
Option | Description |
---|---|
$select |
Select which properties are returned. More information: Select columns |
$filter |
For collection-valued navigation properties, limit the records returned. More information: Filter rows |
$orderby |
For collection-valued navigation properties, control the order of records returned. Not supported with nested $expand . More information: Nested $expand on collection-valued navigation properties |
$top |
For collection-valued navigation properties, limit the number of records returned. Not supported with nested $expand . More information: Nested $expand on collection-valued navigation properties |
$expand |
Expand navigation properties in the related entity set. Using $expand in an $expand is called a nested $expand . More information: Nested expand of single-valued navigation properties & Nested $expand on collection-valued navigation properties |
These options are a subset of the query options described in the 11.2.4.2.1 Expand Options section of OData Version 4.0 Part 1: Protocol Plus Errata 02. The options $skip
, $count
, $search
, and $levels
aren't supported for the Dataverse Web API.
Use these options with $expand
by adding them in parentheses after the name of the navigation property. Separate each option with a semicolon (;).
For example, the following query:
Requests the
account.name
propertyJoins the
AccountTasks
collection-valued navigation property requesting:- The
task.subject
property - Where the
task.subject
contains the string "Task
" - Ordered by the
task.createdon
date, descending
- The
/accounts?$select=name&$expand=Account_Tasks($select=subject;$filter=contains(subject,'Task');$orderby=createdon desc)
Limit columns with $select
As with any query, always limit the columns returned using $select
when you use $expand
. For example, the following request returns the contact.fullname
and task.subject
values in the expanded results from the account
entity type:
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$expand=primarycontactid($select=fullname),Account_Tasks($select=subject)
Prefer: odata.maxpagesize=1
If-None-Match: null
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
OData-Version: 4.0
Preference-Applied: odata.maxpagesize=1
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,primarycontactid(fullname),Account_Tasks(subject))",
"value": [
{
"@odata.etag": "W/\"80649578\"",
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"fullname": "Susanna Stubberod (sample)",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd"
},
"Account_Tasks": [
{
"@odata.etag": "W/\"80649460\"",
"subject": "Task 1 for Litware",
"_regardingobjectid_value": "78914942-34cb-ed11-b596-0022481d68cd",
"activityid": "f68393c1-34cb-ed11-b597-000d3a993550"
}
],
"Account_Tasks@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts(78914942-34cb-ed11-b596-0022481d68cd)/Account_Tasks?$select=subject"
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts?$select=name&$expand=primarycontactid($select=fullname),Account_Tasks($select=subject)&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b78914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520first%253d%2522%257b78914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
Navigation property type differences
It's important to remember there are two types of navigation properties. More information: Web API Navigation Properties
Single-valued navigation properties correspond to lookup attributes that support many-to-one relationships and allow setting a reference to another record.
Collection-valued navigation properties correspond to one-to-many or many-to-many relationships.
Expanding a collection-valued navigation property can make the size of the response large in ways it's difficult to anticipate. It's important that you include limits to control how much data is returned. You can limit the number of records by using paging. More information: Page results
There is a significant difference in how paging is applied to nested $expand options applied to collection valued navigation properties. More information: Expand collection-valued navigation properties
Expand single-valued navigation properties
The following example demonstrates how to retrieve contact records including the primary contact and the user who created the records.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$expand=primarycontactid($select=contactid,fullname),createdby($select=fullname)
Prefer: odata.maxpagesize=2
If-None-Match: null
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
Preference-Applied: odata.maxpagesize=2
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,primarycontactid(contactid,fullname),createdby(fullname))",
"value": [
{
"@odata.etag": "W/\"80649578\"",
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"fullname": "Susanna Stubberod (sample)"
},
"createdby": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
},
{
"@odata.etag": "W/\"80649580\"",
"name": "Adventure Works (sample)",
"accountid": "7a914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"contactid": "72bf4d48-34cb-ed11-b596-0022481d68cd",
"fullname": "Nancy Anderson (sample)"
},
"createdby": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts?$select=name%0A&$expand=primarycontactid($select=contactid,fullname),createdby($select=fullname)&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b7A914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520first%253d%2522%257b78914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
The createdby
single-valued navigation property returns an instance of the systemuser EntityType. Both systemuserid
and ownerid
properties are returned. This is because systemuser
inherits from principal EntityType and shares the ownerid
primary key with team EntityType through this inheritance.
However, the User (SystemUser) table has the primary key of SystemUserId. Both systemuserid
and ownerid
properties have the same value. More information: EntityType inheritance
Return references
Instead of returning data, you can also return references, or links, to the related records by expanding the single-valued navigation property with the /$ref
option. The following example returns JSON objects with an @odata.id
property that has a URL for each primary contact.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$expand=primarycontactid/$ref
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
Preference-Applied: odata.maxpagesize=2
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,primarycontactid,primarycontactid/$ref())",
"value": [
{
"@odata.etag": "W/\"80649578\"",
"name": "Litware, Inc. (sample)",
"_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"@odata.id": "[Organization URI]/api/data/v9.2/contacts(70bf4d48-34cb-ed11-b596-0022481d68cd)"
}
},
{
"@odata.etag": "W/\"80649580\"",
"name": "Adventure Works (sample)",
"_primarycontactid_value": "72bf4d48-34cb-ed11-b596-0022481d68cd",
"accountid": "7a914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"@odata.id": "[Organization URI]/api/data/v9.2/contacts(72bf4d48-34cb-ed11-b596-0022481d68cd)"
}
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts?$select=name%0A&$expand=primarycontactid/$ref&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b7A914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520first%253d%2522%257b78914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
You can only use the /$ref
option with single-valued navigation properties. If you use it with a collection-valued navigation property, you get the following error:
{
"error": {
"code": "0x80060888",
"message": "Expand with $ref is only supported on lookup type navigation property."
}
}
Nested expand of single-valued navigation properties
You can expand single-valued navigation properties to multiple levels by nesting an $expand
option in another $expand
option.
The following query returns task
records and expands the related contact
, the account
related to the contact
, and the systemuser
who created theaccount
record:
Request:
GET [Organization URI]/api/data/v9.2/tasks?$select=subject
&$expand=regardingobjectid_contact_task($select=fullname;
$expand=parentcustomerid_account($select=name;
$expand=createdby($select=fullname)))
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
Preference-Applied: odata.maxpagesize=2
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#tasks(subject,regardingobjectid_contact_task(fullname,parentcustomerid_account(name,createdby(fullname))))",
"value": [
{
"@odata.etag": "W/\"80730855\"",
"subject": "Task 1 for Susanna Stubberod",
"activityid": "e9a8c72c-dbcc-ed11-b597-000d3a993550",
"regardingobjectid_contact_task": {
"fullname": "Susanna Stubberod (sample)",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"parentcustomerid_account": {
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"createdby": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
}
}
},
{
"@odata.etag": "W/\"80730861\"",
"subject": "Task 2 for Susanna Stubberod",
"activityid": "c206f534-dbcc-ed11-b597-000d3a993550",
"regardingobjectid_contact_task": {
"fullname": "Susanna Stubberod (sample)",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"parentcustomerid_account": {
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"createdby": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
}
}
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/tasks?$select=subject&$expand=regardingobjectid_contact_task($select=fullname;$expand=parentcustomerid_account($select=name;$expand=createdby($select=fullname)))&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253cactivityid%2520last%253d%2522%257bC206F534-DBCC-ED11-B597-000D3A993550%257d%2522%2520first%253d%2522%257bE9A8C72C-DBCC-ED11-B597-000D3A993550%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
Expand collection-valued navigation properties
There are some important differences in the response that depend on whether you use nested $expand
with a collection-valued navigation property anywhere in your query.
Nested $expand | Single $expand | |
---|---|---|
Paging | Paging on expanded rows. | Paging only on EntitySet resource. <property name>@odata.nextLink URLs for expanded rows don't include paging information. |
$top or $orderby supported |
No | Yes |
Single $expand on collection-valued navigation properties
If you use only single-level $expand
, no paging is applied applied to the expanded rows. If you include the Prefer: odata.maxpagesize
request header, paging is only applied to the EntitySet resource of the query.
Each expanded collection-valued navigation property returns a <property>@odata.nextLink
URL that includes no paging information. It's a URL that represents the filtered collection for the relationship with your query options appended. You can use that URL to send a separate GET
request and it returns the same rows that were returned in your original request. You can apply paging to that request.
Because no paging is applied to the expanded records, up to 5,000 related records can be returned for each expanded collection-valued navigation property. Depending on your data and the query, it could be a lot of data. Returning that much data could affect performance and possibly cause your request to time out. Be cautious about the queries you compose. You can use $top
, $filter
, and $orderby
options to control the total number of records returned.
The following example includes a single expand of the Account_Tasks
and contact_customer_accounts
while retrieving account records. The Prefer: odata.maxpagesize=1
request header ensures that only one account record is returned in the first page.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,accountid
&$expand=Account_Tasks($select=subject),contact_customer_accounts($select=fullname)
Prefer: odata.maxpagesize=1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
Preference-Applied: odata.maxpagesize=1
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountid,Account_Tasks(subject),contact_customer_accounts(fullname))",
"value": [
{
"@odata.etag": "W/\"80649578\"",
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"Account_Tasks": [
{
"@odata.etag": "W/\"80730894\"",
"subject": "Task 1 for Litware",
"_regardingobjectid_value": "78914942-34cb-ed11-b596-0022481d68cd",
"activityid": "be9f6557-e2cc-ed11-b597-000d3a993550"
},
{
"@odata.etag": "W/\"80730903\"",
"subject": "Task 2 for Litware",
"_regardingobjectid_value": "78914942-34cb-ed11-b596-0022481d68cd",
"activityid": "605dbd65-e2cc-ed11-b597-000d3a993550"
},
{
"@odata.etag": "W/\"80730909\"",
"subject": "Task 3 for Litware",
"_regardingobjectid_value": "78914942-34cb-ed11-b596-0022481d68cd",
"activityid": "a718856c-e2cc-ed11-b597-000d3a993550"
}
],
"Account_Tasks@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts(78914942-34cb-ed11-b596-0022481d68cd)/Account_Tasks?$select=subject",
"contact_customer_accounts": [
{
"@odata.etag": "W/\"80648695\"",
"fullname": "Susanna Stubberod (sample)",
"_parentcustomerid_value": "78914942-34cb-ed11-b596-0022481d68cd",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd"
}
],
"contact_customer_accounts@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts(78914942-34cb-ed11-b596-0022481d68cd)/contact_customer_accounts?$select=fullname"
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts?$select=name,accountid&$expand=Account_Tasks($select=subject),contact_customer_accounts($select=fullname)&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b7A914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520first%253d%2522%257b78914942-34CB-ED11-B596-0022481D68CD%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
Compare this response with the following example, which includes a nested $expand
. Scroll the example response horizontally to see that only the @odata.nextLink
URL for the account result contains paging information.
Nested $expand on collection-valued navigation properties
If you use a nested $expand
anywhere in your query, and you've included the Prefer: odata.maxpagesize
request header, paging is applied to each of the expanded collections.
Each expanded collection-valued navigation property returns a <property>@odata.nextLink
URL that includes paging information. You can use that URL to send a separate GET
request and it will return the next set of records that weren't included in your original request.
You can't use $top
or $orderby
options to limit the total number of records returned with a nested $expand
. The following error is returned if you use these options:
{
"error": {
"code": "0x80060888",
"message": "Only $select and $filter clause can be provided while doing $expand on many-to-one relationship or nested one-to-many relationship."
}
}
The following example is based on the previous example and uses the same data. The only difference is the addition in the URL of this nested $expand
on a single-valued navigation property to return the owning user of the contact: ;$expand=owninguser($select=fullname)
.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,accountid
&$expand=Account_Tasks($select=subject),contact_customer_accounts($select=fullname;
$expand=owninguser($select=fullname))
Prefer: odata.maxpagesize=1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
Preference-Applied: odata.maxpagesize=1
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,accountid,Account_Tasks(subject),contact_customer_accounts(fullname,owninguser(fullname)))",
"value": [
{
"@odata.etag": "W/\"80649578\"",
"name": "Litware, Inc. (sample)",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"Account_Tasks": [
{
"subject": "Task 1 for Litware",
"activityid": "be9f6557-e2cc-ed11-b597-000d3a993550"
}
],
"Account_Tasks@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts(78914942-34cb-ed11-b596-0022481d68cd)/Account_Tasks?$select=subject,description&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%2520countOfRecords%253d%25221%2522%253e%253cactivityid%2520last%253d%2522%257bbe9f6557-e2cc-ed11-b597-000d3a993550%257d%2522%2520first%253d%2522%257bbe9f6557-e2cc-ed11-b597-000d3a993550%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E",
"contact_customer_accounts": [
{
"fullname": "Susanna Stubberod (sample)",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"owninguser": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
}
],
"contact_customer_accounts@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts(78914942-34cb-ed11-b596-0022481d68cd)/contact_customer_accounts?$select=fullname&$expand=owninguser($select=fullname)&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%2520countOfRecords%253d%25221%2522%253e%253ccontactid%2520last%253d%2522%257b70bf4d48-34cb-ed11-b596-0022481d68cd%257d%2522%2520first%253d%2522%257b70bf4d48-34cb-ed11-b596-0022481d68cd%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/accounts?$select=name,accountid&$expand=Account_Tasks($select=subject,description),contact_customer_accounts($select=fullname;$expand=owninguser($select=fullname))&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%2520countOfRecords%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b78914942-34cb-ed11-b596-0022481d68cd%257d%2522%2520first%253d%2522%257b78914942-34cb-ed11-b596-0022481d68cd%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
Compare this response with the previous example, which doesn't use a nested $expand
. In this response, the Prefer: odata.maxpagesize=1
request header is applied to the task
records returned with Account_Tasks
. Only one task is returned instead of three. The Account_Tasks@odata.nextLink
URL returns the next two tasks. Scroll the example response horizontally to see that Account_Tasks@odata.nextLink
, contact_customer_accounts@odata.nextLink
, and@odata.nextLink
URLs contain paging information.
Order rows
Use the $orderby
query option to specify the order in which items are returned. Use the asc
or desc
suffix to specify ascending or descending order, respectively. The default is ascending. The following example retrieves the name
and revenue
properties of accounts, ordered by ascending revenue
and descending name
:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue
&$orderby=revenue asc,name desc
&$filter=revenue ne null
Ordering and paging
How a page is ordered makes a big difference when paging data. If the information about how the results are ordered is ambiguous, Dataverse can't consistently or efficiently return paged data.
Specify an order for your query. With FetchXml, if you don't add any order elements to your query, Dataverse adds an order based on the primary key of the table. However QueryExpression does not, and when your query specifies distinct
results, no primary key values are returned, so Dataverse can't add this default order. You must specify a paging order. Without any order specified, distinct
query results might be returned in random order. OData doesn't provide any option to return distinct results, but you should still apply an order when retrieving paged results.
Paging is dynamic. Each request is evaluated independently as they're received. A paging cookie tells Dataverse the previous page. With this paging cookie data, Dataverse can start with the next record after the last one on the preceding page.
Paging works best going forward. If you go back and retrieve a page you previously retrieved, the results can be different because records could be added, deleted, or modified during since you last retrieved the page. In other words, if your page size is 50 and you go back, you get 50 records, but they might not be the same 50 records. If you keep progressing forward through the pages of a data set, you can expect all the records are returned in a consistent sequence.
Deterministic ordering is important
Deterministic ordering means that there's a way to calculate an order consistently. With a given set of records, the records are always returned in the same order. If you need consistent orders and paging, you must include some unique values or combination of column values that are and specify an order for them to be evaluated.
Nondeterministic example
Let's look at an example that is nondeterministic. This data set contains only State and Status information and is filtered to only return records in an open State. The results are ordered by Status. The first three pages are requested. The results look like this:
State | Status | Page |
---|---|---|
Open | Active | 1 Start |
Open | Active | 1 |
Open | Active | 1 End |
Open | Active | |
Open | Active | |
Open | Inactive | |
Open | Inactive |
The paging cookie saves information about the last record on the page. When the next page is requested, the last record from the first page isn't included. However, given the nondeterministic data, there's no guarantee that the other two records on the first page aren't included in the second page.
To achieve deterministic ordering, add orders on columns that contain unique values, or values that are semi-unique.
Deterministic example
This query is like the nondeterministic one, but it includes the Case ID column that includes unique values. It's also ordered by Status, but also ordered using Case ID. The results look like this:
State | Status | Case ID | Page |
---|---|---|---|
Open | Active | Case-0010 | 1 Start |
Open | Active | Case-0021 | 1 |
Open | Active | Case-0032 | 1 End |
Open | Active | Case-0034 | |
Open | Active | Case-0070 | |
Open | Inactive | Case-0015 | |
Open | Inactive | Case-0047 |
In the next page, the cookie will have Case-0032
stored as the last record in the first page, so page two will start with the next record after that record. The results look like this:
State | Status | Case ID | Page |
---|---|---|---|
Open | Active | Case-0010 | 1 Start |
Open | Active | Case-0021 | 1 |
Open | Active | Case-0032 | 1 End |
Open | Active | Case-0034 | 2 Start |
Open | Active | Case-0070 | 2 |
Open | Inactive | Case-0015 | 2 End |
Open | Inactive | Case-0047 |
Because this query orders unique column values, the order is consistent.
Best practices for orders when paging data
Note
When possible, queries should order on the primary key for the table because Dataverse is optimized for ordering on the primary key by default. Ordering by non-unique or complex fields cause excess overhead and slower queries.
When you retrieve a limited set of data to display in an application, or if you need to return more than 5,000 rows of data, you need to page the results. The choices you make in determining the order of the results can determine whether the rows in each page of data you retrieve overlaps with other pages. Without proper ordering, the same record can appear in more than one page.
To prevent the same record from appearing in more than one page, apply the following best practices:
It's best to include a column that has a unique identifier. For example:
- Table primary key columns
- Autonumber columns
- User/contact IDs
If you can't include a column with a unique identifier, include multiple fields that will most likely result in unique combinations. For example:
- First name + last name + email address
- Full name + email address
- Email address + company name
Anti-patterns for orders when paging data
The following are ordering choices to avoid:
Orders that don't include unique identifiers
Orders on calculated fields
Orders that have single or multiple fields that aren't likely to provide uniqueness such as:
- Status and state
- Choices or Yes/No
- Name values by themselves. For example
name
,firstname
,lastname
- Text fields like titles, descriptions, and multi-line text
- Non unique number fields
Filter rows
Use the $filter
query option to filter a collection of resources.
Dataverse evaluates each resource in the collection using the expression set for $filter
. Only records where the expression evaluates to true
are returned in the response. Records aren't returned if the expression evaluates to false
or null
, or if the user doesn't have read access to the record.
The following table describes the operators and functions you can use in $filter
expressions.
Description | More information | |
---|---|---|
Comparison operators | Use the eq ,ne ,gt ,ge ,lt , and le operators to compare a property and a value. |
Comparison operators |
Logical operators | Use and , or , and not to create more complex expressions. |
Logical operators |
Grouping operators | Use parentheses: (), to specify the precedence to evaluate a complex expression. | Grouping operators |
OData query functions | Evaluate string values using contains , endswith , and startswith functions. |
Use OData query functions |
Dataverse query functions | Use more than 60 specialized functions designed for business applications. | Dataverse query functions |
Lambda expressions | Create expressions based on values of related collections. | Filter using values of related collections |
If you're using a lookup property in a $filter
, you can also use a filtered collection with the corresponding collection-valued navigation property. For example, these two queries return the same results:
accounts?$filter=_owninguser_value eq '<systemuserid value>'&$select=name
systemusers(<systemuserid value>)/user_accounts?$select=name
Comparison operators
The following table describes the operators you can use to compare a property and a value.
Operator | Description | Example |
---|---|---|
eq |
Equal | $filter=revenue eq 100000 |
ne |
Not Equal | $filter=revenue ne 100000 |
gt |
Greater than | $filter=revenue gt 100000 |
ge |
Greater than or equal | $filter=revenue ge 100000 |
lt |
Less than | $filter=revenue lt 100000 |
le |
Less than or equal | $filter=revenue le 100000 |
Column comparison
You can use comparison operators to compare property values in the same row. Only comparison operators can be used to compare values in the same row, and the column types must match. For example, the following query returns any contacts where firstname
equals lastname
:
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname&$filter=firstname eq lastname
Logical operators
The following table describes the logical operators you can use to create more complex expressions.
Operator | Description | Example |
---|---|---|
and |
Logical and | $filter=revenue lt 100000 and revenue gt 2000 |
or |
Logical or | $filter=contains(name,'(sample)') or contains(name,'test') |
not |
Logical negation | $filter=not contains(name,'sample') |
Grouping operators
Use parentheses () with logical operators to specify the precedence to evaluate a complex expression; for example:
$filter=(contains(name,'sample') or contains(name,'test')) and revenue gt 5000
Dataverse query functions
Use more than 60 specialized functions designed for business applications. These functions provide special capabilities, as described in the following table.
Note
The Contains function is for use with columns that have full-text indexing. Only the Dynamics 365 KBArticle (article) table has columns that have full-text indexing. Use the OData contains
function instead.
The Web API Query Function Reference has the complete list. Each article provides a syntax example you can copy.
You must use the function's fully qualified name and append the Service namespace (Microsoft.Dynamics.CRM
) to the name of the function.
Each function has a PropertyName
parameter that specifies the property to be evaluated. The function may have more parameters, such as PropertyValue
, PropertyValues
, or PropertyValue1
and PropertyValue2
. When these parameters exist, you must supply a value, or values, to compare to the PropertyName
parameter.
The following example shows uses the Between function to search for accounts with between 5 and 2,000 employees.
GET [Organization URI]/api/data/v9.2/accounts?$select=name,numberofemployees
&$filter=Microsoft.Dynamics.CRM.Between(PropertyName='numberofemployees',PropertyValues=["5","2000"])
Filter using string values
Keep the following points in mind when you filter on string values:
- All filters using string values are case insensitive.
- You must URL encode special characters in filter criteria. More information: URL encode special characters
- You may use wildcard characters, but avoid trailing wildcards. More information: Use wildcard characters
- You can use OData query functions:
contains
,startswith
, andendswith
. More information: Use OData query functions - You must manage single quotes when you use filters that accept an array of string values. More information: Manage single quotes
URL encode special characters
If the string you are using as a value in a filter function includes a special character, you need to URL encode it. For example, if you use this function: contains(name,'+123')
, it will not work because +
is a character that can't be included in a URL. If you URL encode the string, it will become contains(name,'%2B123')
and you will get results where the column value contains +123
.
The following table shows the URL encoded values for common special characters.
Special character |
URL encoded character |
---|---|
$ |
%24 |
& |
%26 |
+ |
%2B |
, |
%2C |
/ |
%2F |
: |
%3A |
; |
%3B |
= |
%3D |
? |
%3F |
@ |
%40 |
Use wildcard characters
When composing filters using strings, you can apply the following wildcard characters:
Characters | Description | T-SQL documentation and examples |
---|---|---|
% |
Matches any string of zero or more characters. This wildcard character can be used as either a prefix or a suffix. | Percent character (Wildcard - Character(s) to Match) (Transact-SQL) |
_ |
Use the underscore character to match any single character in a string comparison operation that involves pattern matching. | _ (Wildcard - Match One Character) (Transact-SQL) |
[] |
Matches any single character within the specified range or set that is specified between brackets. | [ ] (Wildcard - Character(s) to Match) (Transact-SQL) |
[^] |
Matches any single character that isn't within the range or set specified between the square brackets. | [^] (Wildcard - Character(s) Not to Match) (Transact-SQL) |
More information: Use wildcard characters in conditions for string values
Trailing wildcards not supported
It's important not to use trailing wild cards because they aren't supported. Queries that use these anti-patterns introduce performance problems because the queries can't be optimized. Here are some examples of trailing wildcards:
startswith(name,'%value')
endswith(name,'value%')
Use OData query functions
The following table describes the OData query functions you can use to filter on string values:
Function | Example |
---|---|
contains |
$filter=contains(name,'(sample)') |
endswith |
$filter=endswith(name,'Inc.') |
startswith |
$filter=startswith(name,'a') |
You can use these functions with the logical operator not
to negate the result.
Manage single quotes
Some filters accept an array of string values, such as the In Query function. When you specify values in these filters that contain single quote, or apostrophe, characters, such as O'Brian
or Men's clothes
, you must use double quotes around the values; for example:
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname
&$filter=Microsoft.Dynamics.CRM.In(PropertyName=@p1,PropertyValues=@p2)
&@p1='lastname'
&@p2=["OBrian","OBryan","O'Brian","O'Bryan"]
If you don't, you get the following error: Invalid JSON. A comma character ',' was expected in scope 'Array'. Every two elements in an array and properties of an object must be separated by commas.
If the filter is for a single value, replace the single quote character with two consecutive single quote characters; for example:
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname
&$filter=lastname eq 'O''Bryan'
If you don't, you get an error like this: There is an unterminated literal at position 21 in 'lastname eq 'O'Bryan''.
Filter based on related data values
You can filter rows returned based on values in related tables. How you filter depends on the type of relationship.
Filter using lookup column property values
You can filter based on values in single-valued navigation properties that represent lookup columns. Use this pattern:
<single-valued navigation property>/<property name>
The following example returns account records based on the value of the primarycontactid/fullname
column:
Request:
GET [Organization URI]/api/data/v9.2/accounts?$filter=primarycontactid/fullname eq 'Susanna Stubberod (sample)'
&$select=name,_primarycontactid_value
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_primarycontactid_value)",
"value": [
{
"@odata.etag": "W/\"81359849\"",
"name": "Litware, Inc. (sample)",
"_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
"_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd"
}
]
}
You can also compare values further up the hierarchy of single-valued navigation properties.
The following example returns the first account where the contact record represents the primarycontactid
, where 'System Administrator' created the record, using primarycontactid/createdby/fullname
in the $filter
.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$filter=primarycontactid/createdby/fullname eq 'System Administrator'
&$select=name,_primarycontactid_value
&$expand=primarycontactid(
$select=fullname,_createdby_value;
$expand=createdby($select=fullname))
&$top=1
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(name,_primarycontactid_value,primarycontactid(fullname,_createdby_value,createdby(fullname)))",
"value": [
{
"@odata.etag": "W/\"81359849\"",
"name": "Litware, Inc. (sample)",
"_primarycontactid_value@OData.Community.Display.V1.FormattedValue": "Susanna Stubberod (sample)",
"_primarycontactid_value": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd",
"primarycontactid": {
"fullname": "Susanna Stubberod (sample)",
"_createdby_value@OData.Community.Display.V1.FormattedValue": "System Administrator",
"_createdby_value": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd",
"createdby": {
"fullname": "System Administrator",
"systemuserid": "4026be43-6b69-e111-8f65-78e7d1620f5e",
"ownerid": "4026be43-6b69-e111-8f65-78e7d1620f5e"
}
}
}
]
}
Filter using values of related collections
Use the Lambda operators any
and all
to evaluate values in a collection to filter the results.
any
: Returnstrue
if the expression applied is true for any member of the collection; otherwise, it returns false. Theany
operator without an argument returnstrue
if the collection isn't empty.all
: Returns true if the expression applied is true for all members of the collection; otherwise, it returns false.
The syntax looks like this:
<collection>/[any | all](o:<expression to evaluate>)
In this case, o
is the variable that represents items in the collection. The convention is to use the first letter of the type.
In the expression, use o/<property or collection name>
to refer to a property or collection of a given item.
You can include conditions on multiple collection-valued navigation properties and nested collections. You can't include conditions on collection-valued navigation properties that are nested in a lookup navigation property. For example, $filter=primarycontactid/new_contact_account/any(a:a/accountid eq '{GUID}')
isn't supported.
More information: Lambda Operators at odata.org
Lambda operator examples
The following example retrieves all account entity records that have at least one email with "sometext" in the subject:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'sometext'))
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
The following example retrieves all account entity records that have all associated tasks closed:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Tasks/all(t:t/statecode eq 1)
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
The following example retrieves all account entity records that have at least one email with "sometext" in the subject and whose state code is active:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'sometext') and
e/statecode eq 0)
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
The following example creates a nested query using any
and all
operators:
GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=(contact_customer_accounts/any(c:c/jobtitle eq 'jobtitle' and
c/opportunity_customer_contacts/any(o:o/description ne 'N/A'))) and
endswith(name,'Inc.')
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Page results
Use the Prefer: odata.maxpagesize
request header to control the number of records returned. If you don't specify a number, up to 5,000 records may be returned for each request. You can't request a page size larger than 5,000.
Note
Dataverse doesn't support the $skip
query option, so you can't use the combination of $top
and $skip
for paging. More information: Use the $top query option
The following example returns just the first two contact records:
Request:
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.maxpagesize=2
Response:
HTTP/1.1 200 OK
OData-Version: 4.0
Preference-Applied: odata.maxpagesize=2
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#contacts(fullname)",
"value": [
{
"@odata.etag": "W/\"72201545\"",
"fullname": "Yvonne McKay (sample)",
"contactid": "49b0be2e-d01c-ed11-b83e-000d3a572421"
},
{
"@odata.etag": "W/\"80648695\"",
"fullname": "Susanna Stubberod (sample)",
"contactid": "70bf4d48-34cb-ed11-b596-0022481d68cd"
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/contacts?$select=fullname&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253ccontactid%2520last%253d%2522%257bD5026A4D-D01C-ED11-B83E-000D3A572421%257d%2522%2520first%253d%2522%257b49B0BE2E-D01C-ED11-B83E-000D3A572421%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
When there are more records than requested, the @odata.nextLink
annotation provides a URL you can use with GET
to return the next page of data, as shown in the following example:
Request:
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253ccontactid%2520last%253d%2522%257bD5026A4D-D01C-ED11-B83E-000D3A572421%257d%2522%2520first%253d%2522%257b49B0BE2E-D01C-ED11-B83E-000D3A572421%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.maxpagesize=2
Response:
HTTP/1.1 200 OK
OData-Version: 4.0
Preference-Applied: odata.maxpagesize=2
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#contacts(fullname)",
"value": [
{
"@odata.etag": "W/\"80648710\"",
"fullname": "Nancy Anderson (sample)",
"contactid": "72bf4d48-34cb-ed11-b596-0022481d68cd"
},
{
"@odata.etag": "W/\"80648724\"",
"fullname": "Maria Campbell (sample)",
"contactid": "74bf4d48-34cb-ed11-b596-0022481d68cd"
}
],
"@odata.nextLink": "[Organization URI]/api/data/v9.2/contacts?$select=fullname&$skiptoken=%3Ccookie%20pagenumber=%223%22%20pagingcookie=%22%253ccookie%2520page%253d%25222%2522%253e%253ccontactid%2520last%253d%2522%257bF2318099-171F-ED11-B83E-000D3A572421%257d%2522%2520first%253d%2522%257bBB55F942-161F-ED11-B83E-000D3A572421%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
You should cache the results returned, or the @odata.nextLink
URL value, and use it to return to previous pages.
Don't change the @odata.nextLink
URL value or append any query options to it. For every subsequent request for more pages, you should use the same odata.maxpagesize
preference value used in the original request. You can continue paging through the data until no @odata.nextLink
annotation is included in the results.
In the earlier examples, encoded information was set as the value of the $skiptoken
parameter in the @odata.nextLink
URL value. The server sets this encoded information to control paging. You shouldn't modify the encoded information or encode it further. Use the URL value provided to retrieve the next page.
Use the $top query option
Use the $top
query option to limit the number of results returned. Don't use $top
with the Prefer: odata.maxpagesize
request header. If you include both, $top
is ignored.
The following example returns just the first three account rows:
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue&$top=3
Aggregate data
Use the $apply
option to aggregate and group your data dynamically.
The aggregate functions are limited to a collection of 50,000 records. Further information around using aggregate functionality with Dataverse can be found here: Aggregate data using FetchXml.
You can find more information about OData data aggregation here: OData extension for data aggregation version 4.0. Dataverse supports only a subset of these aggregate methods.
Note
groupby
with datetime values is not supported.$orderby
with aggregate values is not supported. This will return the error:The query node SingleValueOpenPropertyAccess is not supported
.
Following are some examples:
- List of unique statuses in the query
- Count by status values
- Aggregate sum of revenue
- Average revenue based on status
- Sum of revenue based on status
- Total account revenue by primary contact name
- Primary contact names for accounts in 'WA'
- Last created record date and time
- First created record date and time
These samples don't show the complete request and response for brevity.
List of unique statuses in the query
GET accounts?$apply=groupby((statuscode))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2
}
]
}
Count by status values
GET accounts?$apply=groupby((statuscode),aggregate($count as count))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"count@OData.Community.Display.V1.FormattedValue": "8",
"count": 8
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"count@OData.Community.Display.V1.FormattedValue": "1",
"count": 1
}
]
}
Aggregate sum of revenue
GET accounts?$apply=aggregate(revenue with sum as total)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"total@OData.Community.Display.V1.FormattedValue": "$440,000.00",
"total": 440000.000000000
}
]
}
Average revenue based on status
GET accounts?$apply=groupby((statuscode),aggregate(revenue with average as averagevalue))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"averagevalue@OData.Community.Display.V1.FormattedValue": "$53,750.00",
"averagevalue": 53750.000000000
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"averagevalue@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"averagevalue": 10000.000000000
}
]
}
Sum of revenue based on status
GET accounts?$apply=groupby((statuscode),aggregate(revenue with sum as total))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Active",
"statuscode": 1,
"total@OData.Community.Display.V1.FormattedValue": "$430,000.00",
"total": 430000.000000000
},
{
"statuscode@OData.Community.Display.V1.FormattedValue": "Inactive",
"statuscode": 2,
"total@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"total": 10000.000000000
}
]
}
Total account revenue by primary contact name
GET accounts?$apply=groupby((primarycontactid/fullname),aggregate(revenue with sum as total))
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"total@OData.Community.Display.V1.FormattedValue": "$10,000.00",
"total": 10000.000000000,
"contact_fullname": "Jim Glynn (sample)"
},
{
"total@OData.Community.Display.V1.FormattedValue": "$80,000.00",
"total": 80000.000000000,
"contact_fullname": "Maria Campbell (sample)"
},
... <truncated for brevity>
]
}
Primary contact names for accounts in 'WA'
GET accounts?$apply=filter(address1_stateorprovince eq 'WA')/groupby((primarycontactid/fullname))
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"contact_fullname": "Rene Valdes (sample)"
},
{
"contact_fullname": "Robert Lyon (sample)"
},
{
"contact_fullname": "Scott Konersmann (sample)"
}
]
}
Last created record date and time
GET accounts?$apply=aggregate(createdon with max as lastCreate)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"lastCreate@OData.Community.Display.V1.FormattedValue": "3/25/2023 10:42 AM",
"lastCreate": "2023-03-25T17:42:47Z"
}
]
}
First created record date and time
GET accounts?$apply=aggregate(createdon with min as firstCreate)
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"
Response body
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts",
"value": [
{
"firstCreate@OData.Community.Display.V1.FormattedValue": "3/25/2023 10:42 AM",
"firstCreate": "2023-03-25T17:42:46Z"
}
]
}
Count number of rows
Use the $count=true
query option to include a count of entities that match the filter criteria, up to 5,000.
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=accountid&$count=true
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
OData-Version: 4.0
{
"@odata.context": "[Organization URI]/api/data/v9.2/$metadata#accounts(accountid)",
"@odata.count": 9,
"value": [
{
"@odata.etag": "W/\"81359849\"",
"accountid": "78914942-34cb-ed11-b596-0022481d68cd"
},
... <Truncated for brevity>
]
}
The response @odata.count
annotation contains the number of rows, up to 5,000, that matches the filter criteria irrespective of the page size requested.
Note
If you want to retrieve a snapshot within the past 24 hours of the total number of rows for a table beyond 5,000, use the RetrieveTotalRecordCount function.
If the count value is 5,000 and you want to know whether the count is exactly 5,000 or greater than 5,000, you can add the following header:
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.totalrecordcount,Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded"
This header adds the following annotations to the result:
@Microsoft.Dynamics.CRM.totalrecordcount
@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded
When used with the $count=true
query option and there are more than 5,000 records, the following values are returned:
"@odata.count": 5000,
"@Microsoft.Dynamics.CRM.totalrecordcount": 5000,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": true,
If there are fewer than 5000 records, the actual count is returned.
"@odata.count": 58,
"@Microsoft.Dynamics.CRM.totalrecordcount": 58,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": false,
If you don't include the $count=true
query option, the total @Microsoft.Dynamics.CRM.totalrecordcount
value is -1
.
The following example shows that there are 10 accounts that match the $filter
, but only the first three accounts are returned:
Request:
GET [Organization URI]/api/data/v9.2/accounts?$select=name?
&$filter=contains(name,'sample')
&$count=true
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Prefer: odata.maxpagesize=3
Prefer: odata.include-annotations="Microsoft.Dynamics.CRM.*"
Response:
HTTP/1.1 200 OK
Content-Type: application/json; odata.metadata=minimal
OData-Version: 4.0
Preference-Applied: odata.maxpagesize=3
Preference-Applied: odata.include-annotations="Microsoft.Dynamics.CRM.*"
{
"@odata.context":"[Organization URI]/api/data/v9.2/$metadata#accounts(name)",
"@odata.count":10,
"@Microsoft.Dynamics.CRM.totalrecordcount": 5000,
"@Microsoft.Dynamics.CRM.totalrecordcountlimitexceeded": true,
"value":[
{
"@odata.etag":"W/\"502482\"",
"name":"Fourth Coffee (sample)",
"accountid":"655eaf89-f083-e511-80d3-00155d2a68d3"
},
{
"@odata.etag":"W/\"502483\"",
"name":"Litware, Inc. (sample)",
"accountid":"675eaf89-f083-e511-80d3-00155d2a68d3"
},
{
"@odata.etag":"W/\"502484\"",
"name":"Adventure Works (sample)",
"accountid":"695eaf89-f083-e511-80d3-00155d2a68d3"
}
],
"@odata.nextLink":"[Organization URI]/api/data/v9.2/accounts?$select=name&$filter=contains(name,'sample')&$skiptoken=%3Ccookie%20pagenumber=%222%22%20pagingcookie=%22%253ccookie%2520page%253d%25221%2522%253e%253caccountid%2520last%253d%2522%257b695EAF89-F083-E511-80D3-00155D2A68D3%257d%2522%2520first%253d%2522%257b655EAF89-F083-E511-80D3-00155D2A68D3%257d%2522%2520%252f%253e%253c%252fcookie%253e%22%20istracking=%22False%22%20/%3E"
}
To get just a number that represents the count of a collection, append /$count
, as in the following example:
Request:
GET [Organization URI]/api/data/v9.2/accounts/$count
Accept: application/json
OData-MaxVersion: 4.0
OData-Version: 4.0
Response:
HTTP/1.1 200 OK
Content-Type: text/plain
OData-Version: 4.0
10
See also
Search for Dataverse records
Web API Query Data Sample (C#)
Web API Query Data Sample (Client-side JavaScript)
Perform operations using the Web API
Compose HTTP requests and handle errors
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for