Dataverse Query Execution Laboratory: QueryExpression, FetchXML, LINQ, and Web API Performance Engineering

Understanding how Microsoft Dataverse executes queries, enforces security, handles pagination, and exposes data through different programming interfaces.

Power Platform Engineering Academy — Five Years in Five Days

Technical domain: Microsoft Dataverse
Application: APP-005 — Query Execution Laboratory
Technologies: Dataverse SDK, C#, QueryExpression, FetchXML, LINQ, Web API, Model-Driven Apps
Status: Pre-implementation engineering design

Introduction

Retrieving records from Microsoft Dataverse appears straightforward.

A developer constructs a query, sends it to the platform, and receives a collection of records.

However, the simplicity of the API hides several architectural concerns.

Dataverse queries execute within a managed, security-aware platform. Developers do not control the underlying SQL execution plan directly, and different query interfaces expose different capabilities.

QueryExpression, FetchXML, LINQ, and the Dataverse Web API can retrieve similar datasets, but their query representations, feature support, pagination mechanisms, and diagnostic characteristics are not identical.

A query that performs well against 100 records may behave differently against 100,000 records.

A query that returns all expected rows for an administrator may return fewer rows for a user with restricted access.

A query that works correctly on the first page may silently produce incomplete results if the client ignores pagination.

These are engineering problems, not merely syntax differences.

APP-005 — Dataverse Query Execution Laboratory investigates these mechanisms through an independent C# application and a functional Model-Driven App.

Our central question is:

How can we design Dataverse queries that are correct, secure, measurable, and scalable across different query interfaces?

1. Dataverse Query Architecture

Dataverse exposes several supported query mechanisms.

These approaches are not equivalent to submitting arbitrary SQL.

The Dataverse service evaluates the query within its own execution and security model.

A developer can control which columns, conditions, relationships, and ordering rules are requested.

The developer generally cannot inspect or control the underlying database execution plan as though Dataverse were a directly administered SQL Server database.

This distinction is fundamental when investigating performance.

2. Application Architecture

The proposed laboratory contains three layers.

APP-005 Query Execution Laboratory
│
├── C# Query Runner
│   ├── QueryExpression
│   ├── FetchXML
│   ├── LINQ
│   ├── Web API
│   ├── Pagination
│   ├── Timing
│   └── Result Comparison
│
├── Microsoft Dataverse
│   ├── pea_queryrecord
│   ├── pea_queryexecution
│   └── pea_queryscenario
│
└── Model-Driven App
    ├── Entry Dashboard
    ├── Query Scenarios
    ├── Query Records
    ├── Execution Results
    └── Diagnostic Views

The C# application executes controlled queries.

Dataverse stores the test dataset and execution results.

The Model-Driven App exposes the dataset, query scenarios, and diagnostics.

The objective is to compare query behavior using reproducible inputs rather than unrelated queries against different tables.

3. Dataverse Data Model

We will extend the academy’s PEA.Dataverse solution.

3.1 Query Record

Logical name: pea_queryrecord

The pea_batchid column allows each experiment to identify its own dataset.

This prevents unrelated records from contaminating query comparisons.

3.2 Query Scenario

Logical name: pea_queryscenario

3.3 Query Execution

Logical name: pea_queryexecution

A production-quality benchmark would also record dataset size, execution order, client environment, and relevant query characteristics.

For the initial experiment, the table records the minimum information needed to compare executions.

4. Preparing a Reproducible Dataset

Query performance experiments require controlled data.

We propose generating records with predictable characteristics.

For example:

Batch: APP005-20261029
Records: 1,000
Categories:
- Standard
- Premium
- Enterprise
Regions:
- North
- South
- East
- West
Amounts:
- 0.00 to 10,000.00
Sequence:
- 1 to 1,000

The dataset should be generated programmatically using a fixed random seed or deterministic formulas.

This allows the same dataset to be recreated.

The application must verify the number of records successfully created before running benchmarks.

A requested dataset size is not evidence that every record was persisted.

Why dataset isolation matters

If a query retrieves records from multiple test batches, the result count can change between executions.

The experiment must therefore include a batch filter in every comparable query.

This ensures that QueryExpression, FetchXML, LINQ, and Web API are evaluated against the same logical dataset.

5. QueryExpression

QueryExpression is a structured SDK representation of a Dataverse query.

It allows developers to specify:

• Target table.
• Selected columns.
• Filtering conditions.
• Ordering.
• Related-table joins.
• Pagination.
• Distinct results.

Example:

var query = new QueryExpression("pea_queryrecord")
{
    ColumnSet = new ColumnSet(
        "pea_name",
        "pea_region",
        "pea_amount",
        "pea_sequence")
};
query.Criteria.AddCondition(
    "pea_batchid",
    ConditionOperator.Equal,
    batchId);
query.Criteria.AddCondition(
    "pea_amount",
    ConditionOperator.GreaterThan,
    5000m);
query.AddOrder(
    "pea_sequence",
    OrderType.Ascending);
EntityCollection results =
    service.RetrieveMultiple(query);

The query returns matching records subject to the caller’s Dataverse permissions.

5.1 ColumnSet and projection

One of the most important performance decisions is selecting only the columns required.

Using:

new ColumnSet(true)

requests all columns.

This may increase payload size and processing work.

Microsoft recommends avoiding unnecessary retrieval of all columns.

The laboratory should compare explicit projection against broader retrieval while documenting the returned payload and elapsed client time.

Reference: Select columns using QueryExpression.

6. FetchXML

FetchXML is an XML-based query language supported by Dataverse.

It provides filtering, ordering, joins, aggregation, and other capabilities.

A comparable query might be:

<fetch>
  <entity name="pea_queryrecord">
    <attribute name="pea_name" />
    <attribute name="pea_region" />
    <attribute name="pea_amount" />
    <attribute name="pea_sequence" />
    <filter>
      <condition
        attribute="pea_batchid"
        operator="eq"
        value="APP005-20261029" />
      <condition
        attribute="pea_amount"
        operator="gt"
        value="5000" />
    </filter>
    <order
      attribute="pea_sequence"
      descending="false" />
  </entity>
</fetch>

The SDK executes FetchXML using FetchExpression.

var fetch = new FetchExpression(fetchXml);
EntityCollection results =
    service.RetrieveMultiple(fetch);

6.1 Why FetchXML matters

FetchXML is particularly important because it is used across multiple Dataverse capabilities.

Model-Driven App views use FetchXML query definitions.

FetchXML also supports aggregation and advanced query features that may not be represented identically through every other query interface.

This makes FetchXML valuable when investigating how application views retrieve data.

Reference: Use FetchXML to query data.

7. LINQ Through the Dataverse SDK

The Dataverse SDK provides LINQ query support through its organization service context.

This allows developers to express supported queries using familiar C# syntax.

A conceptual example:

using var context =
    new OrganizationServiceContext(service);
var records =
    from record in context.CreateQuery("pea_queryrecord")
    where record.GetAttributeValue<string>(
        "pea_batchid") == batchId
    select record;

However, the Dataverse LINQ provider is not equivalent to Entity Framework Core or LINQ to Objects.

Only supported expression patterns can be translated into Dataverse queries.

Developers must not assume that every C# method or arbitrary expression can execute on the server.

The experiment should distinguish server-translated operations from client-side processing.

Reference: Use LINQ to construct a query.

8. Dataverse Web API and OData

The Dataverse Web API exposes REST-based queries using OData.

A comparable request is:

GET https://<organization>.crm.dynamics.com/api/data/v9.2/<entity-set>?$select=pea_name,pea_region,pea_amount,pea_sequence&$filter=pea_batchid eq 'APP005-20261029' and pea_amount gt 5000&$orderby=pea_sequence asc
Accept: application/json

The actual entity set name must be discovered from Dataverse metadata.

The Web API supports OData query options such as $select, $filter, $orderby, and $expand, subject to Dataverse-specific capabilities and limitations.

The Web API also supports FetchXML queries.

This means a REST client can execute either OData-style queries or supported FetchXML queries depending on the requirements.

Reference: Query data using the Web API.

9. Pagination and Complete Result Retrieval

Pagination is one of the most important correctness concerns in Dataverse querying.

A query returning the first page of results is not necessarily a query returning the complete dataset.

For QueryExpression, the client can use paging information and the returned paging cookie.

Conceptually:

query.PageInfo = new PagingInfo
{
    Count = 500,
    PageNumber = 1
};
var allRecords = new List<Entity>();
while (true)
{
    EntityCollection page =
        service.RetrieveMultiple(query);
    allRecords.AddRange(page.Entities);
    if (!page.MoreRecords)
        break;
    query.PageInfo.PageNumber++;
    query.PageInfo.PagingCookie =
        page.PagingCookie;
}

The query should use deterministic ordering.

Dataverse supports paging cookies for many query shapes, but not every query can return one.

When a paging cookie is unavailable, the platform may use simple paging behavior with different limitations.

A robust query client must inspect the returned paging information rather than assuming a cookie will always be present.

9.1 Paging and consistency

Pagination also introduces a consistency problem.

If records are created, updated, or deleted while the client retrieves multiple pages, the dataset may change between requests.

A multi-page retrieval is not automatically a transactionally consistent snapshot of the entire table.

The laboratory should first benchmark against a stable dataset, then introduce concurrent modifications to investigate the consequences.

Reference: Page results using QueryExpression.

10. Independent C# Query Laboratory

The proposed project is:

PowerPlatformEngineeringAcademy/
└── Dataverse/
    └── APP005.QueryExecutionLaboratory/
        ├── APP005.QueryExecutionLaboratory.csproj
        └── Program.cs

Create the project:

dotnet new console --name APP005.QueryExecutionLaboratory
cd APP005.QueryExecutionLaboratory
dotnet add package Microsoft.PowerPlatform.Dataverse.Client

The independent console application will:

1. Authenticate to Dataverse.
2. Generate a deterministic dataset.
3. Execute a QueryExpression query.
4. Execute a comparable FetchXML query.
5. Compare returned record identifiers.
6. Measure client-side elapsed time.
7. Persist execution results.
8. Report mismatches.

The first implementation will focus on QueryExpression and FetchXML. LINQ and Web API comparisons will require their own adapters to ensure equivalent query semantics and measurement boundaries.

11. Complete C# Query Runner

The following Program.cs generates a small deterministic dataset, executes equivalent QueryExpression and FetchXML queries, retrieves all matching pages, and compares the returned record identifiers.

The sample intentionally uses a small dataset of 100 records to limit initial resource consumption. Larger datasets should be generated only when the experiment is ready for controlled performance testing.

<CodeBlock language=”csharp” editable>
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Security;
using Microsoft.Crm.Sdk.Messages;
using Microsoft.PowerPlatform.Dataverse.Client;
using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Query;

const string DataTable = “pea_queryrecord”;
const string LogTable = “pea_queryexecution”;

string connectionString =
Environment.GetEnvironmentVariable(“DATAVERSE_CONNECTION”)
?? throw new InvalidOperationException(
“DATAVERSE_CONNECTION is missing.”);

using var client = new ServiceClient(connectionString);

if (!client.IsReady)
throw new InvalidOperationException(
$”Connection failed: {client.LastError}”);

var identity = (WhoAmIResponse)client.Execute(
new WhoAmIRequest());

Console.WriteLine($”Connected user: {identity.UserId}”);

string batchId = $”APP005-{Guid.NewGuid()}”;
const int datasetSize = 100;
const int pageSize = 25;

Console.WriteLine($”Batch: {batchId}”);

// Deterministic values within a uniquely identified batch.
for (int i = 1; i <= datasetSize; i++)
{
var record = new Entity(DataTable)
{
[“pea_name”] = $”Query Record {i}”,
[“pea_batchid”] = batchId,
[“pea_sequence”] = i,
[“pea_region”] = i % 2 == 0 ? “North” : “South”,
[“pea_amount”] = (decimal)(i * 100),
[“pea_active”] = i % 3 != 0,
[“pea_eventdate”] = DateTime.UtcNow.Date
};

client.Create(record);

}

Console.WriteLine($”Created {datasetSize} records.”);

List<Guid> ExecuteQueryExpression()
{
var query = new QueryExpression(DataTable)
{
ColumnSet = new ColumnSet(
“pea_name”,
“pea_sequence”,
“pea_amount”),
PageInfo = new PagingInfo
{
Count = pageSize,
PageNumber = 1
}
};

query.Criteria.AddCondition(
    "pea_batchid",
    ConditionOperator.Equal,
    batchId);
query.Criteria.AddCondition(
    "pea_amount",
    ConditionOperator.GreaterThan,
    5000m);
query.AddOrder(
    "pea_sequence",
    OrderType.Ascending);
var ids = new List<Guid>();
while (true)
{
    EntityCollection page =
        client.RetrieveMultiple(query);
    ids.AddRange(page.Entities.Select(e => e.Id));
    if (!page.MoreRecords)
        break;
    query.PageInfo.PageNumber++;
    query.PageInfo.PagingCookie = page.PagingCookie;
}
return ids;

}

List<Guid> ExecuteFetchXml()
{
string safeBatchId = SecurityElement.Escape(batchId)
?? throw new InvalidOperationException(
“Invalid batch identifier.”);

var ids = new List<Guid>();
int pageNumber = 1;
string? pagingCookie = null;
while (true)
{
    string cookieAttribute = pagingCookie is null
        ? ""
        : $" paging-cookie='{SecurityElement.Escape(pagingCookie)}'";
    string fetchXml = $"""
    <fetch count='{pageSize}' page='{pageNumber}'{cookieAttribute}>
      <entity name='{DataTable}'>
        <attribute name='pea_name' />
        <attribute name='pea_sequence' />
        <attribute name='pea_amount' />
        <filter type='and'>
          <condition attribute='pea_batchid'
                     operator='eq'
                     value='{safeBatchId}' />
          <condition attribute='pea_amount'
                     operator='gt'
                     value='5000' />
        </filter>
        <order attribute='pea_sequence' />
      </entity>
    </fetch>
    """;
    EntityCollection page =
        client.RetrieveMultiple(
            new FetchExpression(fetchXml));
    ids.AddRange(page.Entities.Select(e => e.Id));
    if (!page.MoreRecords)
        break;
    pagingCookie = page.PagingCookie;
    pageNumber++;
}
return ids;

}

(List<Guid> ids, long elapsedMs) Measure(
string name,
Func<List<Guid>> operation)
{
var stopwatch = Stopwatch.StartNew();
var ids = operation();
stopwatch.Stop();

Console.WriteLine(
    $"{name}: {ids.Count} records, " +
    $"{stopwatch.ElapsedMilliseconds} ms");
var log = new Entity(LogTable)
{
    ["pea_name"] = $"APP005-{Guid.NewGuid():N}",
    ["pea_querytype"] = name,
    ["pea_scenario"] = "AmountGreaterThan5000",
    ["pea_batchid"] = batchId,
    ["pea_recordcount"] = ids.Count,
    ["pea_durationms"] = checked(
        (int)stopwatch.ElapsedMilliseconds),
    ["pea_success"] = true,
    ["pea_message"] = "Query completed",
    ["pea_executedon"] = DateTime.UtcNow
};
client.Create(log);
return (ids, stopwatch.ElapsedMilliseconds);

}

var queryExpressionResult = Measure(
“QueryExpression”,
ExecuteQueryExpression);

var fetchXmlResult = Measure(
“FetchXML”,
ExecuteFetchXml);

bool sameIds = queryExpressionResult.ids
.OrderBy(id => id)
.SequenceEqual(
fetchXmlResult.ids.OrderBy(id => id));

bool sameOrder = queryExpressionResult.ids
.SequenceEqual(fetchXmlResult.ids);

Console.WriteLine($”Same record IDs: {sameIds}”);
Console.WriteLine($”Same ordering: {sameOrder}”);

if (!sameIds || !sameOrder)
throw new InvalidOperationException(
“Query results are not equivalent.”);

Console.WriteLine(“Query comparison completed.”);
</CodeBlock>

11.1 Understanding the experiment

The program generates 100 records with predictable numeric values.

The filter pea_amount > 5000 should match records whose sequence numbers are 51 through 100, resulting in 50 matching records.

Both queries request 25 records per page, so a stable dataset should require two pages.

The program compares the record IDs returned by QueryExpression and FetchXML.

This is a stronger correctness check than comparing counts alone.

Two queries could return the same number of records while retrieving different records.

11.2 Limitations of the sample

The sample does not yet record page counts even though the proposed execution table includes a pea_pagecount column.

It also does not implement the LINQ and Web API adapters.

Additionally, it creates a new dataset for every execution and does not automatically clean up the generated records.

For performance benchmarking, dataset creation should be separated from timed query execution. The same dataset should be reused across multiple measurements, with execution order varied to reduce warm-up and caching bias.

The code measures client-observed query duration, not the Dataverse server’s internal execution time.

These are implementation tasks to complete during practical validation.

12. Query Correctness and Security

Dataverse queries are security-aware.

The records returned depend on the effective privileges of the calling identity.

A query executed by a System Administrator may return more records than the same query executed by a user with restricted record access.

Therefore, comparing result counts across different identities without accounting for security can produce misleading conclusions.

The experiment should explicitly test:

1. A privileged identity.
2. A user with limited table privileges.
3. A user with restricted record access.

The query definition should remain unchanged.

Only the execution identity should change.

This allows the laboratory to distinguish query semantics from security filtering.

13. Performance Engineering

A meaningful performance investigation must control the variables being compared.

The following dimensions should be recorded.

13.1 Recommended benchmark scenarios

Scenario A — Projection: Compare a query selecting three columns with one selecting all columns.

Scenario B — Selectivity: Compare filters matching approximately 1%, 10%, and 90% of the dataset.

Scenario C — Pagination: Compare page sizes of 100, 500, and 1,000 against a sufficiently large dataset.

Scenario D — Query representation: Execute equivalent QueryExpression and FetchXML queries.

Scenario E — Security: Repeat the same query using identities with different access scopes.

Scenario F — Joins: Compare a single-table query with a query retrieving related-table information.

Each scenario should isolate the intended variable as much as possible.

A single timing result is not sufficient evidence that one query mechanism is faster than another.

14. Query Limits and Documented Constraints

Microsoft documents several important query limits.

For QueryExpression and FetchXML, standard tables support a default and maximum page size of 5,000 records, while elastic tables use 500 records.

Paging cookies are recommended for larger result sets.

Simple paging cannot retrieve more than 50,000 records and may become increasingly expensive as the page number increases. <Cite refs={[“turn730470search0″,”turn730470search1”]}/>

The Web API uses @odata.nextLink for pagination. Clients should follow the returned URL without modifying its paging token. Dataverse does not support $skip as a general OData paging mechanism. <Cite ref=”turn730470search13″/>

FetchXML aggregation has additional limits, including the documented 50,000-record evaluation limit for aggregate queries. <Cite ref=”turn730470search7″/>

These constraints must be incorporated into application architecture rather than discovered only after a production integration encounters incomplete results.

15. Model-Driven App: PEA Query Execution Laboratory

Every academy application requires a functional Model-Driven App with an entry dashboard.

The proposed application will be named:

PEA Query Execution Laboratory

15.1 Entry dashboard

The dashboard should display:

A chart can compare measured durations by query mechanism.

However, the chart must not imply that the mechanisms were benchmarked under identical conditions unless the dataset, query semantics, security identity, and measurement methodology were controlled.

15.2 Query Scenarios

The Query Scenario form should document:

• Scenario name.
• Query mechanism.
• Filter definition.
• Expected record count.
• Description.
• Enabled status.

The form is the configuration and documentation interface for the experiment.

15.3 Query Records

The Query Record view should allow filtering by batch, category, region, and amount.

This gives engineers a direct way to inspect the underlying dataset.

15.4 Query Executions

The Query Execution view should expose:

• Query type.
• Scenario.
• Batch ID.
• Record count.
• Duration.
• Page count.
• Success status.
• Execution timestamp.

This provides the operational evidence required to compare query behavior.

16. Failure Injection

The experiment should deliberately investigate incorrect and incomplete queries.

The application should distinguish expected validation failures from unrelated operational failures.

For example, a network timeout must not be treated as proof that an invalid query was correctly rejected.

17. Architectural Lessons

APP-005 establishes several important engineering principles.

Query correctness precedes performance. A fast query returning incomplete data is not a successful query.

Pagination is part of the query contract. Retrieving the first page is not equivalent to retrieving every matching record.

Query representations are not identical. QueryExpression, FetchXML, LINQ, and OData expose overlapping but different capabilities.

Security affects query results. Record visibility is part of the execution context.

Performance requires controlled measurement. Small timing differences cannot establish general platform performance characteristics.

Stable ordering matters. Deterministic ordering is essential for reliable pagination.

These lessons will be reused in later experiments involving Web API integration, concurrency, security, and distributed processing.

18. Official Microsoft Learn References

1. Use QueryExpression to query data — SDK query construction and capabilities.
2. Select columns using QueryExpression — Projection and column selection.
3. Page results using QueryExpression — Paging cookies, deterministic ordering, and paging limits.
4. Use FetchXML to query data — FetchXML query language.
5. Page results using FetchXML — FetchXML pagination and cookies.
6. Aggregate data using FetchXML — Aggregation capabilities and limits.
7. Use LINQ to construct a query — SDK LINQ provider.
8. Use OData to query data — Web API querying.
9. Page results using OData — Web API pagination.

Conclusion

Dataverse querying is not simply about retrieving records.

It requires understanding the interaction between query representation, security, pagination, data volume, and platform execution behavior.

The Query Execution Laboratory provides a structured way to investigate these mechanisms using reproducible datasets, independent C# queries, and a Model-Driven App that exposes the results.

The most important lesson is:

A reliable Dataverse query must be correct, complete, security-aware, and measurable before it can be considered optimized.

APP-005 establishes the foundation for more advanced experiments involving Dataverse Web API contracts, SDK operations, transactions, optimistic concurrency, and enterprise integration architecture.

The proposed implementation remains pending execution and inspection against a real Dataverse environment.

Power Platform Engineering Academy — Five Years in Five Days

Article 005/100 — Query Execution Laboratory