Query data using the Web API
When you create a query, 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 provides the information you need to make these decisions when constructing a query to retrieve data using the Dataverse Web API.
Note
This article is about querying data found in tables. You can also use Web API to query data about table definitions, or entities. 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 either:
- 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 plural name of the table. But they can be different. Use this query to confirm you are using the correct EntitySet resource name.
If you want 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 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
Dataverse Web API supports the following OData query options:
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 retrieving pages of data. |
Use $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. All query options must be separated from the resource path using '?
'. After the first option, separate each option with an ampersand: '&
'. The names of all options are case sensitive.
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 for the same value to be used 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 is 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. If you don't include a $select
query option, all properties are returned. In OData, every column is represented as a property. More information: Web API Properties
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
For the entity types included with Dataverse, see 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
as shown in the following 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 response returns the values and formatted values 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 (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. This 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, 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
Use the $expand
query option with navigation properties to control what data is returned from related table records.
Note
- You are limited to no more than 15
$expand
options in a query. This is to protect performance. Each$expand
options creates a join that can impact 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.
You can apply the following query options within 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 within 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
Note
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"
}
Note
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 (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 within another $expand
option.
The following query returns task
records and expands the related contact
, the account
related to the contact
, and finally to the systemuser
who created the account
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
, there's no paging 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.
Note
Because no paging is applied to the expanded records, up to 5000 related records can be returned for each expanded collection-valued navigation property. Depending on your data and the query, this could be a lot of data. This could impact 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 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 with 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"
}
Note
Compare this response to the following example that includes a nested $expand
.
You need to 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."
}
}
This example is based on the previous example and uses the same data. The only difference in the URL is the addition 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"
}
Note
Compare this response to the previous example that doesn't use 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 will return the next two tasks.
You need to 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 if the suffix isn't applied. The following example shows retrieving the name
and revenue
properties of accounts ordered by ascending revenue
and by descending name
.
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue
&$orderby=revenue asc,name desc
&$filter=revenue ne null
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.
To compose $filter
expressions, you can apply the following operators and functions:
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 |
Tip
Remember that you can also use Filtered collections.
If you are using a lookup property in a $filter
, you could 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
Use the following comparison operators 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. For example, the following query returns any contacts where the firstname
equals lastname
.
GET [Organization URI]/api/data/v9.2/contacts?$select=fullname&$filter=firstname eq lastname
Note
- Only comparison operators can be used to compare values in the same row.
- The types of columns must match.
More information: Use column comparison in queries
Logical operators
Use the following logical operators 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.
See Web API Query Function Reference for the complete list. Each article provides a syntax example you can copy.
You must use the fully qualified name of these functions. The fully qualified name means you must append the Service namespace (Microsoft.Dynamics.CRM
) to the name of the function.
Each function has a PropertyName
parameter that specifies which 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 using the Between Function to search for accounts with a number of employees between 5 and 2000.
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 while filtering on string values:
- All filters using string values are case insensitive.
- You may use wildcard characters, but avoid trailing wildcards. More information: Use wildcard characters
- You can use OData query functions:
contains
,startswith
,endswith
. More information: Use OData query functions - You must manage single quotes when using filters that accept an array of string values. More information: Manage single quotes
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
When using wildcard characters, it's important not to use trailing wild cards. They aren't supported. Queries using these anti-patterns introduce performance problems because the queries can't be optimized. Some examples of trailing wildcards:
startswith(name,'%value')
endswith(name,'value%')
Use OData query functions
Use the following OData query functions to filter using string values:
Function | Example |
---|---|
contains |
$filter=contains(name,'(sample)') |
endswith |
$filter=endswith(name,'Inc.') |
startswith |
$filter=startswith(name,'a') |
You can use these functions together with the logical operator not
to negate the result.
Manage single quotes
When specifying values for comparison in filters that accept an array of string values, such as the In Query Function, which contain single quote "'
" (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"]
Otherwise 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'
Otherwise 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 of further up the hierarchy of single-valued navigation properties.
This example returns the first account where the contact record representing 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 Lambda operators, any
& all
, to evaluate values in a collection to filter the results.
Operator | Description |
---|---|
any |
Returns true if the 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 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.
Within the expression, use o/<property or collection name>
to refer to property or collection of a given item.
You can include conditions on multiple collection-valued navigation properties and nested collections.
More information: Lambda Operators at odata.org
Note
You cannot 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}')
is not supported.
Lambda operator examples
The example given below shows how you can retrieve 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 example given below shows how you can retrieve 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 example given below shows how you can retrieve all account entity records that have at least one email with "sometext" in the subject and whose statecode 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 example given below shows how you can also create 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
You can control the number of records returned using the Prefer: odata.maxpagesize
request header. If you don't specify the number records to return, up to 5000 records may be returned for each request. You can't request a page size larger than 5000.
Note
Dataverse doesn't support the $skip
query option, so the combination of $top
and $skip
can't be used for paging. More information: Use $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 or append any query options to the @odata.nextLink
URL value. 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 examples above you can see that there's encoded information set as the value of the $skiptoken
parameter within 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, just use the URL value provided to retrieve the next page.
Use $top query option
You can limit the number of results returned by using the $top
query option. The following example returns just the first three account rows.
GET [Organization URI]/api/data/v9.2/accounts?$select=name,revenue&$top=3
Note
You shouldn't use $top
together with the Prefer: odata.maxpagesize
request header. If you include both, $top
is ignored.
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: Use FetchXML aggregation.
More details on OData data aggregation can be found here: OData extension for data aggregation version 4.0. Dataverse supports only a subset of these aggregate methods.
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 5000.
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 5000, 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 5000, use the RetrieveTotalRecordCount Function.
If the count value is 5000 and you want to know whether the count is exactly 5000 or greater than 5000, 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 together with the $count=true
query option, and there are more than 5000 records you see these values:
"@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 representing the count of a collection, append /$count
to get that value.
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 across table data using Dataverse search
Work with Quick Find's search item limit
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
Submit and view feedback for