When working with Dataverse data in C#, QueryExpression is one of the most powerful and commonly used query APIs — especially in plugins, custom workflow activities, and server-side integrations.

In the previous post, we explored:

• LinkEntity joins
• Nested joins
• AliasedValue handling
• Retrieving related table data

But retrieving relational data is only half the story.

In real-world enterprise systems, developers often deal with:

• Tens of thousands of records
• Bulk data processing
• Plugin execution limits
• Performance bottlenecks

This is where paging, ordering, and query optimization become critical.

In this deep dive, we’ll explore how to efficiently retrieve and manage large datasets using QueryExpression.

Understanding Dataverse Retrieval Limits

Dataverse enforces a maximum record return limit per request.

Query Type	Max Records per Call
Standard RetrieveMultiple	5,000
Elastic tables	500

This means:

• You cannot retrieve unlimited records in one call
• Large datasets require paging
• Ignoring paging leads to incomplete data retrieval

Paging in QueryExpression

Paging allows you to retrieve records in controlled batches.

This is done using the PagingInfo object.

Basic Paging Setup

var query = new QueryExpression("account")
{
    ColumnSet = new ColumnSet("name"),
    PageInfo = new PagingInfo()
    {
        Count = 5000,
        PageNumber = 1
    }
};

var results = service.RetrieveMultiple(query);

Explanation

• Count → Number of records per page
• PageNumber → Current page index

This retrieves only the first 5,000 records.

To retrieve all records, we must loop through pages.

Retrieving All Records Using Paging Loop

Below is the production-ready paging pattern used in real projects.

var allRecords = new List<Entity>();

var query = new QueryExpression("account")
{
    ColumnSet = new ColumnSet("name"),
    PageInfo = new PagingInfo()
    {
        Count = 5000,
        PageNumber = 1
    }
};

EntityCollection results;

do
{
    results = service.RetrieveMultiple(query);

    allRecords.AddRange(results.Entities);

    query.PageInfo.PageNumber++;
    query.PageInfo.PagingCookie = results.PagingCookie;

}
while (results.MoreRecords);

How This Works

Element	Purpose
MoreRecords	Indicates more pages exist
PagingCookie	Tracks paging state
PageNumber++	Moves to next page

Without PagingCookie, Dataverse cannot correctly continue the dataset traversal.

What Is a Paging Cookie?

A paging cookie is an internal Dataverse marker that tracks:

• Last retrieved record position
• Query state
• Page continuity

It prevents:

• Duplicate records
• Skipped records
• Paging inconsistencies

Example (sanitized):

<cookiepage="1">  <accountidlast="{GUID}" /></cookie>

You never create cookies manually — you just reuse the returned value.

Ordering Results in QueryExpression

Ordering is often overlooked — but it’s critical for paging accuracy.

Without ordering:

• Record order is undefined
• Paging may return duplicates
• Data consistency is not guaranteed

Adding OrderExpression

query.AddOrder("name",OrderType.Ascending);

Multiple Order Conditions

query.AddOrder("name", OrderType.Ascending);
query.AddOrder("createdon", OrderType.Descending);

Dataverse applies ordering sequentially.

Paging Best Practice

Always order by a stable column such as:

• Primary Key (accountid)
• createdon
• modifiedon

This ensures deterministic paging.

TopCount vs Paging

Developers often confuse TopCount with paging.

They serve different purposes.

Using TopCount

query.TopCount=10;

This retrieves only the first 10 records.

Comparison

Feature	TopCount	Paging
Large dataset retrieval	❌	✅
Loop support	❌	✅
Performance friendly	⚠️	✅
Use case	Quick preview	Bulk processing

Use TopCount when:

• You need a quick subset
• Dashboard widgets
• Validation checks

Performance Optimization Techniques

This is where QueryExpression usage becomes enterprise-grade.

Retrieve Only Required Columns

Avoid:

ColumnSet=new ColumnSet(true);

Instead:

ColumnSet=new ColumnSet("name", "accountnumber");

Why?

• Reduces payload size
• Improves SQL performance
• Lowers memory usage

Avoid Unnecessary Joins

Each LinkEntity adds:

• SQL join cost
• Execution time
• Memory overhead

Only join when data is required.

Apply Filters Early

Filtering reduces dataset size before retrieval.

query.Criteria.AddCondition("statecode",ConditionOperator.Equal,0);

This improves performance significantly.

Limit Page Size Strategically

Max is 5,000 — but smaller pages may perform better in:

• Low-memory plugins
• Real-time workflows
• Throttled environments

Common enterprise size:

1,000 – 2,000 records per page

Avoid Large Retrievals Inside Plugins

This is a critical real-world mistake.

Synchronous plugins have:

• ~2-minute execution limit
• Sandbox CPU limits
• SQL throttling

Retrieving 50k+ records may cause:

• Plugin timeouts
• Transaction rollbacks
• System performance degradation

Better Alternatives for Bulk Retrieval

Instead of plugins, use:

Tool	Use Case
Azure Functions	Scalable processing
Console Apps	Bulk data jobs
Power Automate	Scheduled batches
Data Export Service	Reporting sync
Synapse Link	Analytics workloads

Plugins should retrieve only necessary transactional data.

Real-World Scenario Example

Scenario:
A nightly job exports all Active Accounts with Contacts.

Best approach:

1. Console / Azure Function
2. QueryExpression with paging
3. LeftOuter joins
4. Ordered retrieval
5. Batch processing

Not a plugin.

Common Mistakes to Avoid

Mistake	Impact
No paging	Incomplete data
No ordering	Duplicate records
ColumnSet(true)	Performance hit
Large plugin retrieval	Timeouts
Excess joins	Slow queries

Conclusion

QueryExpression becomes truly enterprise-ready when you master:

• Paging
• Paging cookies
• Ordering
• Dataset limits
• Performance optimization

With these techniques, you can:

• Retrieve millions of records safely
• Avoid plugin failures
• Optimize server performance
• Build scalable Dataverse solutions

What’s Next?

Now that we’ve covered:

• Joins & LinkEntity
• Nested joins
• Paging
• Ordering
• Performance tuning

The next logical deep dive is:

Aggregates & Grouping in Dataverse Queries

We’ll explore:

• Aggregate FetchXML
• GroupBy queries
• Sum / Count / Avg
• When QueryExpression falls short
• FetchXML vs QueryExpression trade-offs