Edit

Share via

Use OData to query data

  • Article

Every query begins with a collection of entities. Entity collections can be:

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 the results of this request to confirm you're using the correct EntitySet resource name.

For example, start with the accounts EntitySet resource to retrieve data from the account entity type.

GET [Organization URI]/api/data/v9.2/accounts

Filtered collections

You can query any collection of entities represented by a collection-valued navigation property of a specified record. For example, if you want to retrieve data from the account entity type 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:

Retrieve Data

To retrieve data from a collection, send a GET request to the collection resource. The following example shows retrieving data from the account entity type.

This example also demonstrates:

Request:

GET [Organization URI]/api/data/v9.2/accounts?$select=name,statecode,statuscode&$orderby=name&$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,statecode,statuscode)",
   "value": [
      {
         "@odata.etag": "W/\"112430907\"",
         "name": "A. Datum Corporation (sample)",
         "statecode@OData.Community.Display.V1.FormattedValue": "Active",
         "statecode": 0,
         "statuscode@OData.Community.Display.V1.FormattedValue": "Active",
         "statuscode": 1,
         "accountid": "4b757ff7-9c85-ee11-8179-000d3a9933c9"
      }
   ]
}

Refine your query

After you select the table to start your query with, refine the query to get the data you need. The following articles explain how to complete your query.

Article Task
Select columns Specify which columns of data to return.
Join tables Specify which related tables to return in the results.
Order rows Specify the sort order of the rows to return.
Filter rows Specify which rows of data to return.
Page results Specify how many rows of data to return with each request.
Aggregate data How to group and aggregate the data returned.
Count number of rows How to get a count of the number of rows returned.
Performance optimizations How to optimize performance

OData query options

Use these options to change the results returned from a collection. 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
$orderby Request resources in a particular order. Order rows
$filter Filter a collection of resources. Filter 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. Limit the number of rows
$count Request a count of the matching resources included with the resources in the response. Count number of rows

To apply multiple options, 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.

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. Learn to use Web API functions

Unsupported OData query options

The Dataverse Web API doesn't support the following OData query options: $skip,$search,$format.

URL length limitations

The length of a URL in a GET request is limited to 32 KB (32,768 characters). Including many complex OData query options as a parameter in the URL can reach the limit. You can execute a $batch operation using a POST request as a way to move the OData query options out of the URL and into the body of the request where the limit is twice as long. Sending a GET request within a $batch allows for URLs up to 64 KB (65,536 characters) in length. Learn more about batch operations using the Web API.

Limit the number of rows

To limit the number of rows returned, use the $top OData query option. Without this limit, Dataverse returns up to 5,000 rows.

Alternatively, specify a number of records to return using paging. Don't use $top when you request pages of data. Learn how to request paged results

Limitations

There are some things that you can do using FetchXml that OData doesn't support.

Community tools

Note

Tools created by the community are not supported by Microsoft. If you have questions or issues with community tools, contact the publisher of the tool.

The Dataverse REST Builder is an open source project that provides a user interface that helps you do many things using the Dataverse Web API, including composing queries.

The XrmToolBox FetchXMLBuilder is a free tool to compose and test FetchXml requests, but it also generates code for OData queries using the same designer experience.

OData version 4.0 features

The Dataverse Web API is an OData version 4.0 service. These sections of the OData 4.0 specification describe how to retrieve data:

This article and the other articles in this section describe the parts of the 4.0 OData specification implemented by the Dataverse Web API and how you can use it to retrieve business data from Dataverse.

Note

The OData version 4.01 is the latest version. It include enhancements and additional features not available in version 4.0, and therefore not currently available in the Dataverse Web API.

Next steps

Learn how to select columns.

Select columns