Query Object in AL | Business Central AL Data Querying

What Is a Query in AL?

A query in AL is an object that defines how data is selected, joined, filtered, grouped, and ordered from one or more tables. Queries do not modify data and do not provide UI for editing; they exist purely to read and return data efficiently.

A query:

• Reads data from tables
• Supports joins and aggregations
• Returns structured result sets
• Can be consumed by reports, APIs, OData, or AL code

A query does not:

• Store data
• Modify records
• Replace reports or pages

Queries are optimized for data retrieval performance.

Why Queries Exist in Business Central

ERP systems often need:

• Fast summaries and totals
• Structured datasets for integrations
• Optimized read-only access
• Consistent data views

Using tables directly for these purposes can be inefficient or complex. Queries exist to:

• Reduce processing overhead
• Push work to the database engine
• Improve performance and scalability
• Provide clean data contracts

They are especially important for analytics and integrations.

Basic Structure of a Query

A query is defined using the query object and consists of:

• Query properties
• Data items (tables)
• Columns (fields or expressions)
• Optional filters and ordering

Each part controls how data is retrieved and shaped.

The following example defines a query that retrieves customer feedback data.

query 50190 "Customer Feedback Query"
{
    QueryType = Normal;

    elements
    {
        dataitem(Feedback; "Customer Feedback")
        {
            column(EntryNo; "Entry No.") { }
            column(CustomerNo; "Customer No.") { }
            column(Status; Status) { }
            column(CreatedDate; "Created Date") { }
        }
    }
}
    

This query:

• Reads data from a single table
• Exposes selected columns
• Returns a structured result set
• Does not allow data modification

Using Joins in Queries

Queries support joining multiple tables using nested dataitems.

elements
{
    dataitem(Customer; Customer)
    {
        column(CustomerNo; "No.") { }
        column(CustomerName; Name) { }

        dataitem(Feedback; "Customer Feedback")
        {
            DataItemLink = "Customer No." = Customer."No.";

            column(FeedbackText; "Feedback Text") { }
            column(Status; Status) { }
        }
    }
}
    

This structure:

• Joins customers with related feedback
• Produces hierarchical result sets
• Mirrors relational data design

Aggregations in Queries

Queries can perform aggregations such as sums and counts.

column(FeedbackCount; Count)
{
    Method = Count;
}
    

Aggregations:

• Are executed at database level
• Improve performance
• Reduce AL-side processing

Query Properties

Query properties define how the query behaves.

Common properties include:

• QueryType
• OrderBy
• DataAccessIntent

These properties should be set correctly to optimize performance and intent.

Queries can be executed and read in AL.

var
    FeedbackQuery: Query "Customer Feedback Query";
begin
    FeedbackQuery.Open();
    while FeedbackQuery.Read() do begin
        Message('%1 - %2', FeedbackQuery.CustomerNo, FeedbackQuery.Status);
    end;
    FeedbackQuery.Close();
end;
    

This allows:

• Controlled data reading
• Integration logic
• Background processing

What Is a Query Extension?

A query extension allows you to add columns or dataitems to an existing query without modifying the original query object. This is essential for:

• Extending standard queries
• Adding custom data for integrations
• Preserving upgrade safety

Query extensions follow the same extensibility model as other AL objects.

The following example extends the earlier query to add a new column.

queryextension 50191 "Customer Feedback Query Ext" extends "Customer Feedback Query"
{
    elements
    {
        addlast(Feedback)
        {
            column(FeedbackStatusText; Format(Status))
            {
            }
        }
    }
}
    

This extension:

• Adds a derived column
• Does not change the base query
• Survives upgrades safely

When to Use Queries vs Reports

Queries should be used when:

• Data is read-only
• Performance is critical
• Output is structured
• No layout is required

Reports should be used when:

• Data must be processed sequentially
• Output formatting is required
• Batch execution is needed

Choosing correctly improves system performance.

Common Beginner Mistakes With Queries

Developers often:

• Use queries to modify data
• Over-fetch unnecessary columns
• Ignore performance properties
• Use reports when queries are sufficient

Queries should be lean and intentional.

Summary

Queries in AL provide a fast, structured, and efficient way to retrieve data from Business Central. Query Extensions allow these datasets to be expanded safely without modifying standard objects.

A well-designed query:

• Retrieves only required data
• Uses joins and aggregations correctly
• Supports integrations cleanly
• Preserves upgrade safety

Understanding queries and query extensions completes the read-only data access layer of AL development.