API Pages vs API Queries in Business Central: When to Use Each

When you need to expose data from Business Central, the first question is not how, but what kind of interaction you need:

Are you writing data into the system, or reading data out of it?

That single distinction determines whether you should use an API page or an API query.

Both expose data over HTTP using OData v4. Both can be consumed from tools like Power BI, Power Automate, or a custom application. At first glance they look interchangeable. They are not.

This post explains what each one actually does, where each one fits, and how to make the right choice before you start writing AL code.

The landscape before you choose

Business Central supports three web service types:

REST API
OData
SOAP

SOAP is deprecated and no longer available in Business Central SaaS. For on-premises deployments it still exists, but Microsoft recommends migrating any remaining SOAP integrations to OData or REST API. OData endpoints that expose UI pages directly are also deprecated and will stop working in version 30 (2027 Wave 1).

==> That leaves REST API as the current and recommended approach for all new integrations.

Within REST API development, you have two AL object types at your disposal:

the API page
the API query.

Microsoft’s documentation is clear on the direction: use API pages or API queries rather than exposing UI pages as web service endpoints, because they are purpose-built for this scenario and carry none of the overhead that comes with UI logic. API pages and queries are also faster.

API pages: built for data exchange

An API page is a page object with its PageType set to API. It exposes a single entity, backed by a single source table, through a versioned REST endpoint. The defining characteristic of an API page is that it supports full CRUD operations: you can read, create, update, and delete records through it, provided your permissions allow it.

Key construction rules

A handful of design decisions at the property and field level have outsized consequences for consuming systems.

Property or pattern	Why it matters	Consequence of getting it wrong
`ODataKeyFields = SystemId`	Exposes a single immutable GUID as the OData key	Power Automate and Power Apps do not work correctly with composite or non-GUID keys
Use Enums, not Options; name enum values with alphanumeric characters only	Enums generate typed metadata with all valid members enumerated; alphanumeric names survive schema version 2.0 without encoding	Options serialise as plain strings with no type information. Enum names with spaces or punctuation are returned as XML-encoded values like `_x0020_`
`DelayedInsert = true`	Prevents the parent record from being committed before subpage data is provided	Deep insert operations fail or produce incomplete parent records
`EntityCaption` and `EntitySetCaption`	Localised entity names exposed through the `entityDefinitions` endpoint	Consuming tools fall back to internal entity name strings with no translation support
Avoid `ODataEDMType`	Complex type fields are deprecated and evaluated at runtime per record	Significant compute overhead at scale; replace with flat first-level properties or navigation properties

The Enums rule has an important nuance introduced in version 24. From v24 onwards, all custom APIs default to schema version 2.0. Under schema version 2.0, Enum fields return the enum name rather than the caption, and any spaces or punctuation marks in the enum name are encoded as XML escape sequences.

The fix is straightforward: name your enum values with alphanumeric characters only so the name and caption diverge intentionally, and the API response stays readable without decoding.

Subpages, $expand

An API page is anchored to one source table. To include related data, for example the lines belonging to a sales order, you add a part page.

From the consuming side, related data is retrieved by appending $expand to the request. Parts are treated as 1-to-N relationships by default. If the relationship is 1-to-0 or 1-to-1, set Multiplicity = ZeroOrOne on the part.

API queries: built for data retrieval

An API query is a query object with its QueryType set to API. It is optimized for reading, and reading only. What makes it structurally different from an API page is what happens at the database level: an API query can join multiple tables server-side and return a flat dataset in a single response. The consumer receives everything in one payload, with no need for $expand and no secondary requests.

It is worth understanding why this is fast at a structural level. When you retrieve records from multiple tables in AL using nested record variables and FINDSET loops, each iteration may issue separate SQL statements. A query object replaces that pattern with a single SQL statement: the join, filtering, and aggregation all happen in the database before any data is returned to the application tier.

Join types

Joins are controlled by the SqlJoinType property on each lower data item. When omitted, the default is LeftOuterJoin. All five types map directly to their SQL equivalents and execute at the SQL Server level, meaning filtering and joining happen before any data reaches the Business Central application tier.

SqlJoinType	What it returns
`InnerJoin`	Only records with a matching value in both data items
`LeftOuterJoin` (default)	All records from the upper data item; null columns where no match exists in the lower
`RightOuterJoin`	All records from the lower data item; null columns where no match exists in the upper
`FullOuterJoin`	All records from both data items; null columns where no match exists on either side
`CrossJoin`	Every combination of rows from both data items (Cartesian product); `DataItemLink` must be left blank

No secondary requests, no $expand, no data assembly on the consuming side.

Aggregations and grouping

Aggregation is controlled by the Method property on individual columns. Setting a method on any column automatically groups the result by all remaining non-aggregated columns, equivalent to a SQL GROUP BY. You do not define grouping separately.

Method	What it calculates	Data type requirement	Key caveat
`Sum`	Total of all values in the column within each group	Decimal, Integer, BigInteger, Duration	Works with SIFT indexes for pre-calculated totals
`Average`	Mean value within each group	Decimal, Integer, BigInteger, Duration	Integer fields use integer division; remainder is discarded, not rounded
`Min`	Lowest value within each group	Decimal, Integer, BigInteger, Duration	None
`Max`	Highest value within each group	Decimal, Integer, BigInteger, Duration	None
`Count`	Number of records in each group	No source field required; column has name only	Equivalent to SQL `COUNT(*)`; counts on primary key of the data item. Also the mechanism for retrieving distinct values: because `Count` forces a `GROUP BY` on all other columns, ignoring the count value in the consumer produces a distinct list of those column combinations

When a query aggregates using Sum, Count, or Average and a matching SIFT index exists on the underlying table, Business Central reads pre-calculated totals rather than scanning rows. This makes aggregated API queries a strong choice for Power BI semantic models: the join, grouping, and totalling all happen server-side before any data leaves Business Central. The flip side is that SIFT indexes carry a write maintenance cost, which is worth considering when deciding which fields to aggregate.

The Count method also enables a pattern that has no direct AL equivalent elsewhere:

retrieving distinct values.

AL has no native DISTINCT keyword for record operations. Using Count on a column forces a GROUP BY on all other columns in the query. If the consumer simply ignores the count value, the result is a deduplicated list of every unique combination of the remaining columns.

DataAccessIntent on API queries

API queries support DataAccessIntent = ReadOnly directly at the object level since queries are always read-only.

No result set caching and the LastModifiedOn pattern

Business Central does not cache query result sets. Every call goes directly to SQL Server. If you are building an integration that polls data frequently, avoid pulling full datasets on each call. Filter on LastModifiedOn to retrieve only records changed since the last successful sync.

Side by side

	API page	API query
Supports read	Yes	Yes
Supports write (POST, PATCH, DELETE)	Yes	No
Deep insert (parent + children in one POST)	Yes	No
Multi-table data	Via subpages + $expand	Server-side join, flat result
Join types	Not applicable	Inner, Left, Right, Full, Cross
Aggregations with SIFT support	No	Yes (Sum, Average, Min, Max, Count)
Implicit GROUP BY on aggregation	Not applicable	Yes (automatic)
DataAccessIntent = ReadOnly	Yes (requires Editable = false)	Yes
Result set caching	No	No
Temp table source safe for large sets	No (>~100 records)	Not applicable
Webhook support	Yes	No
Power BI query folding	Yes	Yes
Power Automate / Power Apps compatible	Yes (requires SystemId as OData key)	Read-only
Discoverable in Tell Me + analysis mode	Not applicable	Yes

When to use which

For any read-only scenario, the default choice is an API query. 
Not one option among two, but the default.

Query objects are built for this: they execute at the SQL level, return flat pre-joined results, support server-side aggregation through SIFT indexes, and route to the read-only database replica through DataAccessIntent = ReadOnly.

Use an API page when the integration needs to write data to Business Central, when you need webhook subscriptions to react to record changes, or when deep insert of parent and child records in a single POST is required.

In practice, a well-designed integration uses both together.

An API page handles the write side. A webhook on that API page triggers a sync notification. API queries with DataAccessIntent = ReadOnly and LastModifiedOn filters feed the reporting layer. The write path and the read path are separate objects with separate concerns, and that separation is intentional.

Performance guidance

Several patterns that look harmless during development cause serious problems under production load.

Anti-pattern	Problem it causes	Fix
Exposing a UI page as a web service endpoint	Triggers such as `OnAfterGetCurrRecord` and `OnAfterGetRecord` run for every record returned, including FactBox calculations and defaulting logic	Use a dedicated API page instead; if the UI page must be exposed, guard all UI-specific logic with `GuiAllowed()`
Temp table as API page source with large result sets	Every record is fetched and inserted individually with no caching; paging behaves unpredictably	Avoid temp table sources when the endpoint is expected to return more than roughly 100 records
Parallel inserts of child records sharing the same parent	Concurrent calls lock the parent and integration record tables, causing failures and delays	Sequence the calls or use OData `$batch` to execute them one after another in a single transaction
Calculated fields on API pages	Evaluated for every record returned regardless of whether the consumer uses the value	Store the calculated value on the physical table where possible, or move it to a separate endpoint

When consuming Business Central endpoints from Power BI, the recommended approach is to build one API query per data domain and compose them at the semantic model level.

Think of each query as a lego block: a customer query, a sales header query, a ledger entry query, each with its own join logic, its own aggregations, and its own DataAccessIntent = ReadOnly. You pull them into Power BI as independent tables and define the relationships in the semantic model.

Each table in this diagram that shows a typical snowflake schema is a separate Query API.

This keeps each query focused, independently filterable, independently refreshable, and easy to extend without touching unrelated data. It also means query folding works cleanly for each table because there are no cross-source joins breaking the fold.

Query discoverability and analysis mode

The same lego block approach that works for Power BI also works inside Business Central itself. A normal query object with UsageCategory = ReportsAndAnalysis set becomes discoverable in Tell Me search and visible in the role explorer under Report and Analysis.

Users can open it directly in the client and switch to analysis mode, an interactive pivot-style experience where they can group by any column, apply filters per tab, and see aggregated totals, all without exporting to Excel or opening Power BI.

What about OData web services on query objects

Before API queries existed as a distinct object type, developers would create a Business Central query object and publish it as an OData web service through the Web Services page. This approach still works, but it carries the same deprecation risk as other OData UI-based endpoints. The cleaner path for new development is to define the query with QueryType = API from the start, rather than relying on the Web Services page to expose it.

Where people go wrong

What I sometimes see people doing ‘wrong’ is:

Trying to use API pages for reporting
Overusing $expand
Building one giant query instead of domain-based queries

If you design integrations with a clear separation between write and read paths, most performance and complexity problems disappear before they start.

Not because the platform is magical, but because you are using it the way it was designed. 🙂