FetchXML is the native query language of Microsoft Dataverse. If you're building anything serious on the Power Platform — flows, plugins, portals, or canvas apps — you will hit a wall with OData eventually. This guide is the syllabus I wish existed when I started: every feature, every operator, real patterns you'll actually use in production.
📋 Table of Contents
- What is FetchXML
- Basic Structure
- Filtering & Operators
- AND vs OR Logic
- Link-Entities (Joins)
- Aggregate Queries
- Paging
- FetchXML in Power Automate
- FetchXML in Plugins / C#
- FetchXML via Web API
- XrmToolBox: FetchXML Builder
- Real-World Patterns
- Performance Tips
- FetchXML vs OData
- Quick Reference Cheat Sheet
1. What is FetchXML
FetchXML is an XML-based query language built specifically for Microsoft Dataverse (formerly Common Data Service / CDS). It's the closest thing Dataverse has to SQL — you write structured XML describing which records you want, and Dataverse translates it internally into the right database query.
It predates OData in the Dynamics ecosystem and remains more powerful for complex scenarios: multi-level joins, aggregates, cross-entity filtering, and paging over large datasets.
When to use FetchXML vs the alternatives
- FetchXML — complex joins, aggregates, paging over 5,000 records, cross-entity conditions, working inside plugins or C# code
- OData ($filter) — simple single-entity queries via Web API, quick REST calls, Power Automate "List Rows" without complex joins
- LINQ (early-bound C#) — type-safe queries in plugin/tool code, good for simple lookups, limited aggregate support
Where FetchXML runs
- Power Automate — "List rows" action on Dataverse connector → FetchXML input
- Power Apps — via
Collectwith a Dataverse table or inside canvas app formulas using the connector - Plugins / C# —
IOrganizationService.RetrieveMultiple(new FetchExpression(fetchXml)) - Web API —
GET /api/data/v9.2/entity?fetchXml=<encoded xml> - XrmToolBox — FetchXML Builder plugin for visual building and live testing
- Power Pages / Portals — FetchXML-based data lists and liquid templates
<!-- Minimal valid FetchXML -->
<fetch>
<entity name="account">
<attribute name="name" />
<attribute name="emailaddress1" />
</entity>
</fetch>2. Basic Structure
Every FetchXML query follows the same skeleton. Learn this once and everything else is layers on top.
The fetch element
| Attribute | Values | What it does |
|---|---|---|
top | integer | Limits result count (like SQL TOP). Omit for full result set. |
distinct | true / false | Removes duplicate rows. Essential when using outer joins. |
no-lock | true / false | Adds NOLOCK hint. Use for read-heavy reporting queries. |
aggregate | true / false | Switches the query to aggregate mode (section 6). |
page | integer | Page number for paging (section 7). |
count | integer | Page size for paging. Max 5000. |
entity, attribute, order
<entity name="logical_name">— the table you're querying. Always use the logical name (lowercase, prefixed).<attribute name="field_name" />— columns to return. Omit completely to return all columns (not recommended for production).<order attribute="field_name" descending="false" />— sort order. Stack multiple for multi-column sort.
<fetch top="50" distinct="true" no-lock="true">
<entity name="contact">
<attribute name="fullname" />
<attribute name="emailaddress1" />
<attribute name="createdon" />
<order attribute="createdon" descending="true" />
<order attribute="fullname" descending="false" />
</entity>
</fetch>
Tip: Never omit all
<attribute> elements in production queries. Selecting all columns is a silent performance killer — you end up fetching columns you never use across potentially thousands of rows.
3. Filtering — <filter> and <condition>
Filters are where FetchXML earns its keep. The <filter> element wraps one or more <condition> elements and can be nested arbitrarily deep.
condition anatomy
<condition attribute="fieldname" operator="eq" value="somevalue" />Complete Operator Reference
| Operator | SQL Equivalent | Notes |
|---|---|---|
eq | = value | Exact match |
ne | != value | Not equal |
gt | > value | Greater than |
lt | < value | Less than |
ge | >= value | Greater than or equal |
le | <= value | Less than or equal |
like | LIKE '%val%' | Use % wildcard. Leading % kills index — avoid. |
not-like | NOT LIKE | Inverse of like |
in | IN (v1,v2,v3) | Uses <value> child elements |
not-in | NOT IN | Uses <value> child elements |
between | BETWEEN v1 AND v2 | Uses two <value> child elements |
null | IS NULL | No value attribute needed |
not-null | IS NOT NULL | No value attribute needed |
eq-userid | Current user ID | No value needed. Dynamic current user. |
ne-userid | Not current user | |
eq-businessid | Current BU ID | |
today | = CAST(GETDATE() AS DATE) | Date fields only |
tomorrow | Tomorrow's date | |
yesterday | Yesterday's date | |
this-week | Current calendar week | |
this-month | Current calendar month | |
this-year | Current year | |
last-seven-days | Rolling 7 days back | |
last-x-days | Rolling N days back | Requires value="N" |
next-x-days | Rolling N days forward | Requires value="N" |
last-x-months | Rolling N months back | Requires value="N" |
next-x-months | Rolling N months forward | |
on | Exact date | Date fields, value = YYYY-MM-DD |
on-or-before | <= date | |
on-or-after | >= date | |
begins-with | LIKE 'val%' | Index-safe unlike leading wildcard like |
ends-with | LIKE '%val' | Avoid on large tables |
contain-values | Multi-select option set | Uses <value> child elements |
not-contain-values | Multi-select exclusion |
Examples
<!-- IN operator -->
<condition attribute="statuscode" operator="in">
<value>1</value>
<value>2</value>
<value>290560001</value>
</condition>
<!-- BETWEEN operator -->
<condition attribute="createdon" operator="between">
<value>2024-01-01</value>
<value>2024-12-31</value>
</condition>
<!-- Dynamic date operators -->
<condition attribute="modifiedon" operator="last-x-days" value="30" />
<!-- Null check -->
<condition attribute="emailaddress1" operator="null" />
<!-- Current user -->
<condition attribute="ownerid" operator="eq-userid" />
<!-- Like with trailing wildcard only (index-safe) -->
<condition attribute="name" operator="like" value="Contoso%" />4. AND vs OR Logic
By default, all conditions inside a <filter> are ANDed together. Set type="or" to switch to OR. Nest filters to build complex boolean trees.
<!-- Default AND: active accounts in the US -->
<filter>
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="address1_country" operator="eq" value="United States" />
</filter><!-- OR: either status applies -->
<filter type="or">
<condition attribute="statuscode" operator="eq" value="1" />
<condition attribute="statuscode" operator="eq" value="290560001" />
</filter><!-- Mixed AND + OR nesting:
(statecode = 0) AND (country = 'US' OR country = 'Canada') -->
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
<filter type="or">
<condition attribute="address1_country" operator="eq" value="United States" />
<condition attribute="address1_country" operator="eq" value="Canada" />
</filter>
</filter><!-- Real pattern: projects that are completed OR cancelled OR have no tasks -->
<fetch>
<entity name="project">
<attribute name="name" />
<attribute name="statuscode" />
<filter type="and">
<condition attribute="ownerid" operator="eq-userid" />
<filter type="or">
<condition attribute="statuscode" operator="eq" value="290560002" />
<condition attribute="statuscode" operator="eq" value="290560003" />
<condition entityname="tsk" attribute="taskid" operator="null" />
</filter>
</filter>
<link-entity name="task" from="regardingobjectid" to="projectid"
link-type="outer" alias="tsk">
<attribute name="taskid" />
</link-entity>
</entity>
</fetch>
Watch out: The
entityname attribute on a <condition> lets you filter on a joined entity's columns from the parent filter. This only works with outer joins and is how you implement "NOT EXISTS" patterns. The value of entityname must match the alias on the <link-entity>.
5. Link-Entities (Joins)
FetchXML joins are called link-entities. They work like SQL JOINs and can be nested multiple levels deep.
link-type values
| link-type | SQL Equivalent | When to use |
|---|---|---|
inner | INNER JOIN | Only return parent records that have a matching child. Default if omitted. |
outer | LEFT OUTER JOIN | Return parent records even when no child exists. Required for "not exists" patterns. |
Basic inner join
<!-- Contacts and their parent Account name -->
<fetch>
<entity name="contact">
<attribute name="fullname" />
<attribute name="emailaddress1" />
<link-entity name="account" from="accountid" to="parentcustomerid"
link-type="inner" alias="acc">
<attribute name="name" alias="accountname" />
<attribute name="telephone1" alias="accountphone" />
</link-entity>
</entity>
</fetch>Multi-level joins
<!-- Three levels deep: Task → Stage → Project -->
<fetch>
<entity name="task">
<attribute name="subject" />
<attribute name="statuscode" />
<link-entity name="stage" from="stageid" to="stageid"
link-type="inner" alias="stg">
<attribute name="name" alias="stagename" />
<link-entity name="project" from="projectid" to="projectid"
link-type="inner" alias="proj">
<attribute name="name" alias="projectname" />
<attribute name="statuscode" alias="projectstatus" />
</link-entity>
</link-entity>
</entity>
</fetch>Filtering on a joined entity
<!-- Contacts whose account is in a specific industry -->
<fetch>
<entity name="contact">
<attribute name="fullname" />
<link-entity name="account" from="accountid" to="parentcustomerid"
link-type="inner" alias="acc">
<attribute name="name" alias="accountname" />
<filter>
<condition attribute="industrycode" operator="eq" value="7" />
</filter>
</link-entity>
</entity>
</fetch>Cross-entity condition (entityname attribute)
This pattern filters on a linked entity's column from the parent entity's filter block. Most commonly used for "NOT EXISTS" — find parent records where no child row matches.
<!-- Accounts with NO contacts -->
<fetch distinct="true">
<entity name="account">
<attribute name="name" />
<filter>
<condition entityname="con" attribute="contactid" operator="null" />
</filter>
<link-entity name="contact" from="parentcustomerid" to="accountid"
link-type="outer" alias="con">
<attribute name="contactid" />
</link-entity>
</entity>
</fetch>
Rule: Cross-entity conditions (
entityname=) always require the join to be link-type="outer". An inner join would already exclude the non-matching rows before the NULL check runs.
6. Aggregate Queries
Set aggregate="true" on <fetch> to switch to aggregate mode. In this mode, every <attribute> must declare an aggregate function or be a groupby column.
Aggregate functions
| Function | Description |
|---|---|
count | Count of all rows (including nulls) |
countcolumn | Count of non-null values in a column |
sum | Sum of numeric column |
avg | Average of numeric column |
min | Minimum value |
max | Maximum value |
<!-- Count contacts per account -->
<fetch aggregate="true">
<entity name="contact">
<attribute name="contactid" aggregate="count" alias="contactcount" />
<attribute name="parentcustomerid" groupby="true" alias="accountid" />
</entity>
</fetch><!-- Sum and average of estimated revenue grouped by industry -->
<fetch aggregate="true">
<entity name="opportunity">
<attribute name="estimatedvalue" aggregate="sum" alias="totalrevenue" />
<attribute name="estimatedvalue" aggregate="avg" alias="avgrevenue" />
<attribute name="opportunityid" aggregate="count" alias="dealcount" />
<attribute name="industrycode" groupby="true" alias="industry" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch><!-- Distinct count: unique companies with open cases -->
<fetch aggregate="true">
<entity name="incident">
<attribute name="customerid" aggregate="countcolumn" distinct="true" alias="uniquecustomers" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch>Grouping by date parts
<!-- Cases created per month -->
<fetch aggregate="true">
<entity name="incident">
<attribute name="incidentid" aggregate="count" alias="casecount" />
<attribute name="createdon" groupby="true" dategrouping="month" alias="createmonth" />
<attribute name="createdon" groupby="true" dategrouping="year" alias="createyear" />
<order alias="createyear" descending="false" />
<order alias="createmonth" descending="false" />
</entity>
</fetch>
Note: Aggregate FetchXML results are capped at 50,000 groups. For reporting over very large datasets, consider Azure Synapse Link or Dataverse long-term data export instead.
7. Paging
Dataverse returns a maximum of 5,000 records per request. For anything larger you must page through results using either simple page numbering or paging cookies.
Simple page/count attributes
<!-- Page 2, 100 records per page -->
<fetch page="2" count="100">
<entity name="contact">
<attribute name="fullname" />
<attribute name="createdon" />
<order attribute="createdon" descending="true" />
</entity>
</fetch>Paging cookies (correct approach for large datasets)
Simple page numbering degrades at scale because the database still scans from the beginning every time. Paging cookies pass a server-side bookmark that picks up exactly where the previous page ended — far more efficient.
The response from Dataverse includes a @Microsoft.Dynamics.CRM.fetchxmlpagingcookie annotation. Decode it and inject it into the next request:
<!-- First request: no cookie -->
<fetch page="1" count="5000">
<entity name="contact">
<attribute name="fullname" />
<order attribute="contactid" descending="false" />
</entity>
</fetch>
<!-- Subsequent requests: inject the decoded cookie -->
<fetch page="2" count="5000"
paging-cookie="<cookie page="1"><contactid ... /></cookie>">
<entity name="contact">
<attribute name="fullname" />
<order attribute="contactid" descending="false" />
</entity>
</fetch>
Important: Always include an
<order> on a unique field (like the primary key) when paging. Without a deterministic sort order, you can get duplicate or skipped records across pages.
Paging in C# (plugin or tool)
// C# paging loop example
string fetchXml = @"<fetch count='5000'>
<entity name='contact'>
<attribute name='fullname' />
<order attribute='contactid' />
</entity>
</fetch>";
var allRecords = new List<Entity>();
int pageNumber = 1;
string pagingCookie = null;
while (true)
{
string pagedFetch = PatchFetchXmlPage(fetchXml, pageNumber, pagingCookie);
var result = service.RetrieveMultiple(new FetchExpression(pagedFetch));
allRecords.AddRange(result.Entities);
if (!result.MoreRecords) break;
pagingCookie = result.PagingCookie;
pageNumber++;
}8. FetchXML in Power Automate
The Dataverse connector's List rows action accepts a FetchXML query directly. This unlocks joins, aggregates, and paging that you simply cannot do with OData filter expressions.
Basic setup
- Add action: Microsoft Dataverse → List rows
- Set Table Name to your entity
- Expand Advanced options
- Paste your FetchXML into the Fetch Xml Query field
<fetch top="500" no-lock="true">
<entity name="contact">
<attribute name="fullname" />
<attribute name="emailaddress1" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch>Injecting dynamic values safely
In Power Automate, FetchXML is a string. You can inject dynamic expressions using @{expression} syntax. The critical rule: never inject user-controlled input directly — always validate or use GUIDs from trigger/action outputs.
<!-- Safe: injecting a trigger GUID value -->
<fetch>
<entity name="task">
<attribute name="subject" />
<attribute name="statuscode" />
<filter>
<condition attribute="regardingobjectid" operator="eq"
value="@{triggerOutputs()?['body/accountid']}" />
<condition attribute="createdon" operator="last-x-days"
value="@{variables('DaysBack')}" />
</filter>
</entity>
</fetch>Dynamic IN clause from an array variable
<!-- Step 1: Build <value> tags using Select action -->
<!-- Select "from": array of IDs, "map": concat('<value>', item(), '</value>') -->
<!-- Step 2: Join them and inject into FetchXML -->
<fetch>
<entity name="project">
<attribute name="name" />
<filter>
<condition attribute="projectid" operator="in">
@{join(body('Select_ID_values'), '')}
</condition>
</filter>
</entity>
</fetch>Handling paging in Power Automate
The List Rows action does not auto-page. Use a Do Until loop: check the response for @odata.nextLink or use the paging cookie from the response headers to fetch the next batch.
Tip: For flows processing large volumes, consider moving paging logic to an Azure Function or plugin. Power Automate's 30-day / 30-second action limits make deep paging expensive.
9. FetchXML in Plugins / C#
Inside a Dataverse plugin or any code using IOrganizationService, pass your FetchXML to RetrieveMultiple via a FetchExpression.
Basic retrieval
string fetchXml = @"
<fetch top='50'>
<entity name='contact'>
<attribute name='fullname' />
<attribute name='emailaddress1' />
<filter>
<condition attribute='statecode' operator='eq' value='0' />
</filter>
</entity>
</fetch>";
EntityCollection results = service.RetrieveMultiple(new FetchExpression(fetchXml));
foreach (Entity entity in results.Entities)
{
string name = entity.GetAttributeValue<string>("fullname");
string email = entity.GetAttributeValue<string>("emailaddress1");
}Reading aliased join attributes
When you alias an attribute from a joined entity, it comes back in the Entity as an AliasedValue.
// FetchXML has: <attribute name="name" alias="accountname" /> on the link-entity
var aliasedValue = entity.GetAttributeValue<AliasedValue>("accountname");
string accountName = aliasedValue?.Value as string;Reading aggregate results
// FetchXML aggregate result: alias="contactcount"
var aliasedCount = entity.GetAttributeValue<AliasedValue>("contactcount");
int count = Convert.ToInt32(aliasedCount?.Value);Building FetchXML dynamically in C#
// Use XElement for safe construction — no string concatenation
var fetch = new XElement("fetch", new XAttribute("top", 100),
new XElement("entity", new XAttribute("name", "contact"),
new XElement("attribute", new XAttribute("name", "fullname")),
new XElement("filter",
new XElement("condition",
new XAttribute("attribute", "statecode"),
new XAttribute("operator", "eq"),
new XAttribute("value", "0")
)
)
)
);
string fetchXml = fetch.ToString();
var results = service.RetrieveMultiple(new FetchExpression(fetchXml));10. FetchXML via Web API
You can execute FetchXML directly against the Dataverse Web API by passing it as a URL query parameter. This is useful for integrations, testing from Postman, or server-side code calling the API directly.
Endpoint pattern
GET https://<org>.api.crm.dynamics.com/api/data/v9.2/<entity_plural>?fetchXml=<url-encoded-xml>URL-encode your FetchXML
<!-- Raw FetchXML -->
<fetch top="10">
<entity name="contact">
<attribute name="fullname" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch>
<!-- URL encoded (key characters) -->
< → %3C
> → %3E
" → %22
/ → %2F
= → %3DFull example in JavaScript
const fetchXml = `<fetch top="10">
<entity name="contact">
<attribute name="fullname" />
<filter><condition attribute="statecode" operator="eq" value="0" /></filter>
</entity>
</fetch>`;
const encoded = encodeURIComponent(fetchXml);
const url = `${orgUrl}/api/data/v9.2/contacts?fetchXml=${encoded}`;
const response = await fetch(url, {
headers: {
'Authorization': `Bearer ${accessToken}`,
'OData-MaxVersion': '4.0',
'OData-Version': '4.0',
'Accept': 'application/json'
}
});
const data = await response.json();
console.log(data.value); // array of recordsRequest formatted values
To get the display label of option sets (formatted values) in the response, add a Prefer header:
Prefer: odata.include-annotations="OData.Community.Display.V1.FormattedValue"Formatted values then appear in the response as fieldname@OData.Community.Display.V1.FormattedValue.
11. XrmToolBox: FetchXML Builder
FetchXML Builder (by Jonas Rapp) is the fastest way to build, test, and debug FetchXML queries against a live environment. Every serious Dataverse developer should have it installed.
Key features to use
- Visual query builder — drag-and-drop entities and fields, set filters via dropdowns. Good starting point even if you'll hand-edit the XML.
- Execute and preview — run the query and see results instantly against your connected environment. No need to deploy a flow or plugin to test.
- View as SQL — converts your FetchXML to approximate T-SQL. Useful for understanding what's happening under the hood and for writing the equivalent SQL for reporting.
- Copy as code — exports the query as ready-to-use C#, JavaScript, or Power Automate-compatible string.
- Aggregate mode toggle — switches the builder into aggregate mode with a checkbox.
- Paging cookie test — lets you page through results directly in the tool.
Workflow tip
- Build the query visually in FetchXML Builder
- Switch to XML view and refine edge cases (nested filters, cross-entity conditions)
- Execute and validate results against real data
- Copy the final XML into your flow, plugin, or API call
Tip: FetchXML Builder also supports the
no-lock hint and shows row counts per page. Use it to benchmark query performance before committing to production code.
12. Common Real-World Patterns
Pattern 1: Find records with no related records (NOT EXISTS)
<!-- Accounts with zero contacts -->
<fetch distinct="true">
<entity name="account">
<attribute name="name" />
<attribute name="accountid" />
<filter>
<condition entityname="con" attribute="contactid" operator="null" />
</filter>
<link-entity name="contact" from="parentcustomerid" to="accountid"
link-type="outer" alias="con">
<attribute name="contactid" />
</link-entity>
</entity>
</fetch>Pattern 2: Get the latest record per group
<!-- Most recent case per customer -->
<fetch aggregate="true">
<entity name="incident">
<attribute name="customerid" groupby="true" alias="customerid" />
<attribute name="createdon" aggregate="max" alias="latestcase" />
</entity>
</fetch>Pattern 3: Multi-condition OR across linked entities
<!-- Projects that are complete OR cancelled OR have stages with no tasks -->
<fetch distinct="true" no-lock="true">
<entity name="project">
<attribute name="name" />
<attribute name="statuscode" />
<filter type="and">
<condition attribute="retailerid" operator="eq"
value="abf8e77a-845b-f011-bec1-6045bda966da" />
<filter type="or">
<condition attribute="statuscode" operator="eq" value="290560003" />
<condition attribute="statuscode" operator="eq" value="290560002" />
<condition entityname="tsk" attribute="taskid" operator="null" />
</filter>
</filter>
<link-entity name="stage" from="projectid" to="projectid"
link-type="outer" alias="stg">
<link-entity name="task" from="stageid" to="stageid"
link-type="outer" alias="tsk">
<attribute name="taskid" />
</link-entity>
</link-entity>
</entity>
</fetch>Pattern 4: Hierarchy query — tasks for a specific project via stages
<!-- All tasks under a project by traversing Project → Stage → Task -->
<fetch>
<entity name="task">
<attribute name="subject" />
<attribute name="statuscode" />
<attribute name="ownerid" />
<link-entity name="stage" from="stageid" to="stageid"
link-type="inner" alias="stg">
<attribute name="name" alias="stagename" />
<link-entity name="project" from="projectid" to="projectid"
link-type="inner" alias="proj">
<attribute name="name" alias="projectname" />
<filter>
<condition attribute="projectid" operator="eq"
value="3fa85f64-5717-4562-b3fc-2c963f66afa6" />
</filter>
</link-entity>
</link-entity>
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</entity>
</fetch>Pattern 5: Active records modified in the last N days, owned by current user
<fetch top="200">
<entity name="opportunity">
<attribute name="name" />
<attribute name="estimatedvalue" />
<attribute name="modifiedon" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
<condition attribute="ownerid" operator="eq-userid" />
<condition attribute="modifiedon" operator="last-x-days" value="14" />
</filter>
<order attribute="modifiedon" descending="true" />
</entity>
</fetch>Pattern 6: Count of records per status with a join condition
<!-- Task count per project, only for active projects -->
<fetch aggregate="true">
<entity name="task">
<attribute name="taskid" aggregate="count" alias="taskcount" />
<link-entity name="stage" from="stageid" to="stageid"
link-type="inner" alias="stg">
<link-entity name="project" from="projectid" to="projectid"
link-type="inner" alias="proj">
<attribute name="projectid" groupby="true" alias="projectid" />
<attribute name="name" groupby="true" alias="projectname" />
<filter>
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>
</link-entity>
</entity>
</fetch>13. Performance Tips
1. Never use a leading wildcard in LIKE
<!-- BAD: forces a full table scan -->
<condition attribute="name" operator="like" value="%contoso%" />
<!-- GOOD: index-seekable -->
<condition attribute="name" operator="like" value="contoso%" />
<!-- Or use begins-with explicitly -->
<condition attribute="name" operator="begins-with" value="contoso" />2. Always use no-lock on read-only reporting queries
<fetch no-lock="true">
<!-- Adds NOLOCK hint. Prevents blocking on busy tables.
Only use for reporting — not for business-critical transactional reads. -->
</fetch>3. Select only the columns you need
<!-- BAD: returns all columns -->
<entity name="contact">
<!-- no attribute elements = SELECT * -->
</entity>
<!-- GOOD: return only what you use -->
<entity name="contact">
<attribute name="fullname" />
<attribute name="emailaddress1" />
</entity>4. Filter as early as possible — push filters to the lowest join level
<!-- Filter on the join entity directly, not at the top -->
<link-entity name="account" from="accountid" to="parentcustomerid"
link-type="inner" alias="acc">
<filter>
<!-- This runs during the join, not after -->
<condition attribute="statecode" operator="eq" value="0" />
</filter>
</link-entity>5. Use top and paging — never retrieve all records speculatively
<!-- Always bound your queries in automation flows -->
<fetch top="5000" no-lock="true">
<entity name="contact">
<attribute name="fullname" />
</entity>
</fetch>6. Avoid deeply nested joins unless necessary
Each additional join level multiplies the SQL complexity. If you're going 4+ levels deep, consider whether a Dataverse Custom API or pre-computed rollup field could reduce query depth.
7. Use distinct only when you have to
distinct="true" forces a DISTINCT on the SQL side which means sorting the entire result set. Only use it when outer joins actually produce duplicates that matter for your output.
8. Index-backed fields filter faster
Dataverse automatically indexes primary keys, lookup fields, and some system fields like statecode, statuscode, ownerid, createdon, and modifiedon. Filtering on custom text fields without enabling a custom index will always be slower.
14. FetchXML vs OData
| Capability | FetchXML | OData ($filter) |
|---|---|---|
| Simple single-entity queries | ✅ | ✅ (simpler syntax) |
| Multi-level joins | ✅ | ❌ (expand limited to 1 level) |
| Outer joins / NOT EXISTS | ✅ | ❌ |
| Aggregate functions (sum, count, avg) | ✅ | ❌ |
| Group by with date parts | ✅ | ❌ |
| Dynamic date operators (last-x-days, this-week) | ✅ | ❌ |
| Cross-entity conditions (entityname=) | ✅ | ❌ |
| Paging with cookies | ✅ | ✅ (@odata.nextLink) |
| Readable / writeable by non-developers | ❌ (verbose XML) | ✅ (concise URL params) |
| Works in Power Automate List Rows | ✅ | ✅ |
| Works in Canvas App formula bar | Limited | ✅ |
| Direct URL testing (Postman, browser) | ⚠️ (URL-encode required) | ✅ (natural params) |
| Formatted value annotations | ✅ (via API header) | ✅ (via API header) |
| Tool support (FetchXML Builder) | ✅ (excellent) | ❌ |
| NOLOCK hint | ✅ | ❌ |
Decision rule
- Use OData for simple filters on a single table, quick API tests, Canvas App formulas.
- Use FetchXML for anything involving joins, aggregates, dynamic dates, NOT EXISTS, or paging beyond page 1 of a large dataset.
- In plugins and C# code, prefer FetchXML — it's more predictable than LINQ and gives you full control over what the database executes.
Quick Reference Cheat Sheet
fetch attributes
| Attribute | Purpose |
|---|---|
top="N" | Limit result count |
distinct="true" | Remove duplicate rows |
no-lock="true" | NOLOCK hint for read queries |
aggregate="true" | Enable aggregate mode |
page="N" | Page number |
count="N" | Page size (max 5000) |
paging-cookie="..." | Server-side cursor for efficient paging |
Comparison operators
| Operator | Meaning |
|---|---|
eq / ne | Equal / Not equal |
gt / lt | Greater than / Less than |
ge / le | Greater or equal / Less or equal |
like / not-like | String pattern match (% wildcard) |
begins-with | Index-safe prefix match |
in / not-in | Set membership (use <value> children) |
between | Range (two <value> children) |
null / not-null | IS NULL / IS NOT NULL |
Dynamic date operators
| Operator | Notes |
|---|---|
today / yesterday / tomorrow | No value needed |
this-week / this-month / this-year | No value needed |
last-seven-days | No value needed |
last-x-days / next-x-days | value="N" required |
last-x-months / next-x-months | value="N" required |
on / on-or-before / on-or-after | value = YYYY-MM-DD |
Aggregate functions
| Function | Usage |
|---|---|
count | Row count including nulls |
countcolumn | Non-null value count |
sum | Numeric sum |
avg | Numeric average |
min / max | Min / Max value |
groupby="true" | Group by this column (no aggregate function) |
dategrouping | Values: day, week, month, quarter, year |
link-entity
| Attribute | Values / Notes |
|---|---|
name | Logical name of the entity to join |
from | Field on the child entity |
to | Field on the parent entity |
link-type | inner (default) or outer |
alias | Required for accessing attributes and cross-entity conditions |
Cross-entity condition (NOT EXISTS)
<filter>
<condition entityname="alias" attribute="pkfield" operator="null" />
</filter>
<link-entity ... link-type="outer" alias="alias">
<attribute name="pkfield" />
</link-entity>Skeleton query
<fetch top="500" distinct="false" no-lock="true">
<entity name="entity_logical_name">
<attribute name="field1" />
<attribute name="field2" />
<order attribute="field1" descending="false" />
<filter type="and">
<condition attribute="statecode" operator="eq" value="0" />
</filter>
<link-entity name="related_entity" from="fk_field" to="pk_field"
link-type="inner" alias="rel">
<attribute name="name" alias="relatedname" />
</link-entity>
</entity>
</fetch>
