Tag: Dataverse

If you’ve worked with the Dataverse Web API, you’ve written $filter=revenue gt 100000 a hundred times. But what about questions like:

• “Which accounts have at least one email mentioning invoice?”
• “Which accounts have all their tasks closed?”

A plain comparison can’t answer these, because the data you’re testing lives in a collection of related rows, not on the account itself. This is exactly what OData lambda expressions are for.

What is a lambda expression in OData?

A lambda expression lets you test every row in a related collection against a condition, and keep the parent row only if the collection satisfies it. OData gives you two lambda operators:

Operator	Returns true when…
any	at least one related row meets the condition
all	every related row meets the condition

Calling any with no condition at all simply checks that the collection is not empty.

The syntax

<collection>/any(o:<condition using o>)
<collection>/all(o:<condition using o>)

• <collection> is a collection-valued navigation property (a one-to-many or many-to-many relationship name, for example Account_Emails).
• o is a range variable: a short name that stands for one row of the collection while it’s being evaluated. By convention, use the first letter of the related table (e for email, t for task).
• Inside the condition, use o/<column> to read a column on that related row.

Think of it as the C# LINQ you already know: accounts.Where(a => a.Emails.Any(e => e.Subject.Contains("invoice"))).

Example 1: any, accounts with a matching email

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'invoice'))

Read it as: return accounts where any email e has a subject containing “invoice”.

Example 2: all, accounts with every task closed

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Tasks/all(t:t/statecode eq 1)

Read it as: return accounts where all tasks t have statecode equal to 1 (completed).

Heads-up: by standard logic, all is true for an empty collection, so you should expect accounts with no tasks at all to show up too. If you only want accounts that have tasks and all are closed, combine it with an any check:

&$filter=Account_Tasks/any() and Account_Tasks/all(t:t/statecode eq 1)

Example 3: multiple conditions on the same related row

You can use and / or inside the lambda:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=Account_Emails/any(e:contains(e/subject,'invoice') and e/statecode eq 0)

Here one single email must satisfy both conditions: subject contains “invoice” and it’s active. That’s different from two separate any calls, where each could be satisfied by a different email.

Example 4: nested lambdas

Lambdas can be nested to go a level deeper, and combined with regular filters:

GET [Organization URI]/api/data/v9.2/accounts?$select=name
&$filter=(contact_customer_accounts/any(c:c/jobtitle eq 'CFO' and
c/opportunity_customer_contacts/any(o:o/description ne 'N/A')))
and endswith(name,'Inc.')

Meaning: accounts whose name ends with “Inc.” and that have a contact with job title “CFO” who has at least one opportunity with a description other than “N/A”.

Things to watch out for

1. Use the relationship’s navigation property name, not the table name. The exact names (like Account_Emails) are visible in the $metadata document or the table’s relationships in the maker portal.
2. Lookup navigation properties can’t wrap a collection. A filter that goes through a single-valued lookup and then into a collection, such as primarycontactid/new_contact_account/any(...), is not supported.
3. Performance matters. Keep nested lambdas shallow and always use $select to return only what you need. Dataverse also limits a query to 500 conditions in total.
4. String comparisons are case-insensitive, and special characters in values need URL encoding (for instance + becomes %2B).
5. Avoid leading wildcards like %value in string filters; they can’t be optimized.

Quick cheat sheet

I want accounts where…	Filter
there is at least one related row	Relation/any()
at least one related row matches	Relation/any(x:x/col eq 'v')
every related row matches	Relation/all(x:x/col eq 'v')
one row matches multiple conditions	Relation/any(x:x/a eq 1 and x/b eq 2)

Wrapping up

Whenever your filter question starts with “which records have any / all related records that…”, reach for any and all. Once the collection/any(x: ...) shape clicks, it replaces a lot of extra round trips and client-side filtering.

Reference: Filter rows by using OData, Microsoft Learn