Dataverse Web API and OData: The Queries I Actually Use

The Dataverse Web API is OData v4 — which sounds simple until you run into lookup filter syntax, cross-entity queries that need FetchXML, or a paginated result set you didn’t expect. This article covers the patterns that come up repeatedly in real projects.

The base URL and authentication

Every Web API request goes to:

https://{org}.crm.dynamics.com/api/data/v9.2/{EntitySetName}

Use v9.2 unless you have a specific reason not to. It’s the current stable version and supports all modern features including Dataverse search and elastic tables.

Authentication is always OAuth 2.0 with a bearer token. In Azure or Power Automate, use the Dataverse connector or an HTTP action with a managed identity or app registration:

GET /api/data/v9.2/accounts
Authorization: Bearer {token}
OData-MaxVersion: 4.0
OData-Version: 4.0
Accept: application/json

Always include the OData-MaxVersion and OData-Version headers. Without them, you may get unexpected behavior in some environments.

---

$select always — never omit it

This is the single biggest performance mistake in Dataverse queries. Never do this:

GET /api/data/v9.2/accounts

That returns every column on every record, including binary fields, large text fields, and all the internal Microsoft columns. Instead, always specify exactly what you need:

GET /api/data/v9.2/accounts?$select=accountid,name,emailaddress1,statecode

The performance difference on large tables is significant. Make this a habit.

---

$filter deep dive

Basic comparisons

OData operators are words, not symbols:

eq, ne, gt, ge, lt, le
and, or, not

GET /api/data/v9.2/accounts?$filter=statecode eq 0 and revenue gt 100000

String functions

# Starts with
$filter=startswith(name, 'Contoso')

# Contains (case-insensitive in Dataverse)
$filter=contains(name, 'holdings')

# Ends with
$filter=endswith(emailaddress1, '.com')

Filtering on lookup fields — the common gotcha

This trips up almost everyone. When filtering on a lookup (e.g., primarycontactid), you cannot use the schema name directly:

# WRONG — this will error
$filter=primarycontactid eq '00000000-0000-0000-0000-000000000001'

# CORRECT — use the underscore-wrapped logical name with _value suffix
$filter=_primarycontactid_value eq '00000000-0000-0000-0000-000000000001'

The pattern is _{lookupschemaname}_value — underscores on both sides, _value at the end. This is the standard Dataverse Web API syntax for filtering on lookup GUIDs.

For polymorphic lookups (like regardingobjectid on activities), you need a type cast via the navigation property:

$filter=regardingobjectid_account/accountid eq '{guid}'

Filtering on OptionSet values

Use the integer value, not the label:

$filter=statecode eq 0 and statuscode eq 1

Filtering on date fields

Use ISO 8601 format:

$filter=createdon ge 2026-01-01T00:00:00Z
$filter=createdon ge 2026-01-01 and createdon lt 2026-02-01

For “today” filtering from Power Automate, pass a dynamic value using utcNow():

@{formatDateTime(utcNow(), 'yyyy-MM-dd')}T00:00:00Z

---

$expand — related records

$expand follows navigation properties to include related records in a single request.

One-to-many (collection-valued navigation)

GET /api/data/v9.2/accounts?
  $select=accountid,name&
  $expand=contact_customer_accounts($select=contactid,fullname,emailaddress1)

This returns each account with its related contacts nested inline. Use $filter, $select, $top, and $orderby inside the expand parentheses.

Many-to-one (single-valued navigation)

GET /api/data/v9.2/contacts?
  $select=contactid,fullname&
  $expand=parentcustomerid_account($select=name,telephone1)

Nested expand (up to 2 levels deep)

GET /api/data/v9.2/accounts?
  $select=name&
  $expand=contact_customer_accounts(
    $select=fullname;
    $expand=contact_tasks($select=subject,scheduledend)
  )

Note: Dataverse supports a maximum of 10 $expand clauses per query and 2 levels of nesting. Beyond that, use FetchXML.

---

FetchXML via the Web API

When OData can’t get you there — aggregate queries, complex multi-entity joins, or queries involving activity parties — use FetchXML via the Web API. You don’t need to drop down to the SDK.

Encode your FetchXML as a URL parameter:

GET /api/data/v9.2/accounts?fetchXml={encoded-fetchxml}

Here’s a practical example — get accounts with open opportunity count:

<fetch aggregate="true">
  <entity name="account">
    <attribute name="accountid" alias="accountid" groupby="true" />
    <attribute name="name" alias="name" groupby="true" />
    <link-entity name="opportunity" from="parentaccountid" to="accountid" link-type="outer">
      <attribute name="opportunityid" alias="open_opps" aggregate="count" />
      <filter>
        <condition attribute="statecode" operator="eq" value="0" />
      </filter>
    </link-entity>
  </entity>
</fetch>

URL-encode this and pass it as the fetchXml query parameter. In Power Automate, use the encodeUriComponent() expression function.

The response returns your alias values as property names. Formatted values (like readable numbers) are available through the @OData.Community.Display.V1.FormattedValue annotation on each aliased property. Include the Prefer: odata.include-annotations="*" header to get these formatted values in the response.

---

Pagination with @odata.nextLink

Dataverse returns a maximum of 5,000 records per page by default. If your query can return more, you need to handle pagination.

The response will include a @odata.nextLink property when there are more pages:

{
  "value": [...],
  "@odata.nextLink": "https://org.crm.dynamics.com/api/data/v9.2/accounts?$select=name&$skiptoken=..."
}

Never try to parse or construct the skiptoken yourself. Just pass the full @odata.nextLink URL as your next request.

Pagination in Power Automate

Use a Do Until loop:

1. Initialize a nextLink variable as empty string
2. Make the first request
3. Append results to an array variable
4. Set nextLink to @{body('HTTP_Request')?['@odata.nextLink']}
5. Loop until nextLink is empty, using the nextLink URL as the request URL

For very large datasets, consider using Dataverse’s Prefer: odata.maxpagesize=500 header to control page size and reduce memory pressure in your flow.

Setting a page size preference

GET /api/data/v9.2/accounts?$select=name
Prefer: odata.maxpagesize=100

Valid range is 1–5000. Smaller pages mean more requests but lower memory per request — useful in Power Automate where flow run history and variable size matter.

---

Calling the Web API from Power Automate

Use the HTTP with Microsoft Entra ID connector (formerly “HTTP with Azure AD”) or the Dataverse connector’s “Perform an unbound action” / “List rows” actions. For full OData control, use the HTTP connector with Entra ID auth.

Connector setup:

• Base Resource URL: https://{org}.crm.dynamics.com
• Microsoft Entra ID Resource URI: https://{org}.crm.dynamics.com

Request:

Method: GET
URI: https://{org}.crm.dynamics.com/api/data/v9.2/accounts?$select=name,revenue&$filter=statecode eq 0&$top=50
Headers:
  OData-MaxVersion: 4.0
  OData-Version: 4.0
  Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"

The Prefer: odata.include-annotations header is useful — it returns formatted display values alongside raw values. For example, OptionSet fields will return both statecode: 0 and statecode@OData.Community.Display.V1.FormattedValue: "Active".

Parsing the response

The response body is a JSON object. Records are in the value array:

body('HTTP_Request')?['value']

Access a specific field on the first record:

first(body('HTTP_Request')?['value'])?['name']

---

Error handling patterns

The Web API returns standard HTTP status codes. The ones you’ll encounter most:

Code	Meaning	Common cause
400	Bad Request	Malformed OData, invalid property name
401	Unauthorized	Token expired or missing
403	Forbidden	User lacks privilege for the operation
404	Not Found	Record doesn’t exist or wrong entity set name
429	Too Many Requests	API limits hit — back off and retry

For 429s, respect the Retry-After header. In Power Automate, configure the action’s retry policy to handle this automatically.

Error responses have this shape:

{
  "error": {
    "code": "0x80040217",
    "message": "account With Id = ... Does Not Exist"
  }
}

In Power Automate, check outputs('HTTP_Request')?['statusCode'] and branch on it before accessing the body.

---

Quick reference

Need	OData approach
Specific columns	$select=col1,col2
Filter records	$filter=field eq value
Lookup filter	$filter=_lookupname_value eq guid
Related records	$expand=navprop($select=...)
Sort	$orderby=createdon desc
Limit	$top=100
Count	$count=true
Aggregates / complex joins	Use FetchXML
Pagination	Follow @odata.nextLink

If you’re building FetchXML queries, don’t write them by hand — use FetchXML Builder from XrmToolBox. It generates the XML, lets you test it against your environment, and converts between FetchXML and OData. It’s the single most useful tool in XrmToolBox and I use it on every project.

For testing OData queries directly, Postman with the Dataverse Web API works well. Set up OAuth 2.0 auth once and you can iterate on queries without writing any code.