Use OData to query data

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.
• A collection returned by a function.

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>
   [
}

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:

• 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

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:

• Limiting the columns returned using $select. Learn more about selecting columns
• Ordering results using $orderby. Learn more about ordering columns
• Limiting the rows returned using $top. Learn about limiting the number of rows
• Showing formatted values using the request header: Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue". Learn about formatted values

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.

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.

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 is 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 standard table rows, 500 elastic table 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.

• You can't join tables without any relationship. OData only allows using the $expand query option to join tables using navigation properties that are part of the relationships in the data model.

• You can't use nested $expand with N:N relationships.

• Aggregation limitations lists the following limitations for aggregations using OData:

  • Get distinct number with CountColumn
  • Time zone when grouping by date
  • Row aggregate
  • Per query limit

• Perform cross table column comparisons. OData supports filtering on column values in the same row, but they must be in the same table.

• You don't need to override the default sort order for choice columns. The default behavior when sorting choice columns is to use the integer values rather than the localized label value.

• You can't use the Late Materialize query performance optimization.

Community tools

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:

• OData Version 4.0. Part 1: Protocol Plus Errata 03 11.2 Requesting Data
• OData Version 4.0. Part 2: URL Conventions Plus Errata 03 5 Query Options

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.

Next steps

Learn how to select columns.