AI/ML, AWS, Azure, DevOps, GCP, M365, Microsoft Power Platform, SharePoint: Liquid code in Power Pages

Thursday, May 7, 2026

Liquid code in Power Pages - FetchXML

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.

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 Collect with 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="&lt;cookie page=&quot;1&quot;&gt;&lt;contactid ... /&gt;&lt;/cookie&gt;">
  <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
=    →  %3D

Full 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 records

Request 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>

Thursday, May 7, 2026

Liquid code in Power Pages - FetchXML

📋 Table of Contents

1. What is FetchXML

When to use FetchXML vs the alternatives

Where FetchXML runs

2. Basic Structure

The fetch element

entity, attribute, order

3. Filtering — <filter> and <condition>

condition anatomy

Complete Operator Reference

Examples

4. AND vs OR Logic

5. Link-Entities (Joins)

link-type values

Basic inner join

Multi-level joins

Filtering on a joined entity

Cross-entity condition (entityname attribute)

6. Aggregate Queries

Aggregate functions

Grouping by date parts

7. Paging

Simple page/count attributes

Paging cookies (correct approach for large datasets)

Paging in C# (plugin or tool)

8. FetchXML in Power Automate

Basic setup

Injecting dynamic values safely

Dynamic IN clause from an array variable

Handling paging in Power Automate

9. FetchXML in Plugins / C#

Basic retrieval

Reading aliased join attributes

Reading aggregate results

Building FetchXML dynamically in C#

10. FetchXML via Web API

Endpoint pattern

URL-encode your FetchXML

Full example in JavaScript

Request formatted values

11. XrmToolBox: FetchXML Builder

Key features to use

Workflow tip

12. Common Real-World Patterns

Pattern 1: Find records with no related records (NOT EXISTS)

Pattern 2: Get the latest record per group

Pattern 3: Multi-condition OR across linked entities

Pattern 4: Hierarchy query — tasks for a specific project via stages

Pattern 5: Active records modified in the last N days, owned by current user

Pattern 6: Count of records per status with a join condition

13. Performance Tips

1. Never use a leading wildcard in LIKE

2. Always use no-lock on read-only reporting queries

3. Select only the columns you need

4. Filter as early as possible — push filters to the lowest join level

5. Use top and paging — never retrieve all records speculatively

6. Avoid deeply nested joins unless necessary

7. Use distinct only when you have to

8. Index-backed fields filter faster

14. FetchXML vs OData

Decision rule

Quick Reference Cheat Sheet

fetch attributes

Comparison operators

Dynamic date operators

Aggregate functions

link-entity

Cross-entity condition (NOT EXISTS)

Skeleton query

No comments:

Post a Comment

Featured Post

Liquid code in Power Pages - FetchXML

Popular posts