AL Query objects support joins through DataItemLink (parent → child field mapping) to maximize SQL Server pushdown. However, DataItemLink cannot express richer boolean logic—especially OR conditions—while real-world scenarios sometimes require alternative predicates as part of the join condition (a true join predicate, not only key mapping).
Concrete example (DataItemLink with OR)
Scenario: start from a Purchase Header and join Purchase Lines for that document, but only keep lines that are “still to be processed” using business logic such as:
- Remaining Quantity <> 0 OR Qty. Received Not Invoiced <> 0
Conceptually, you would want to express:
- Parent → child key mapping
- Document Type = Document Type
- Document No. = No.
- AND a business predicate as part of the link/join predicate:
- (Remaining Quantity <> 0 OR Qty. Received Not Invoiced <> 0)
Today, developers must move this logic to separate filters, duplicate branches/queries, or post-process in AL—reducing readability/maintainability and potentially degrading SQL pushdown.
Proposal
Allow boolean expressions (including OR and parentheses/grouping) in DataItemLink (or an equivalent “join predicate” mechanism), combinable with existing field-to-field link mappings.
Required guardrails (performance / system stability)
Enabling OR in join predicates clearly “opens Pandora’s box” in terms of query complexity and SQL cost. To avoid overly heavy queries that could negatively impact Business Central (including multi-tenant environments) and potentially bring systems down, the feature should include execution and complexity limits, for example:
- a cost threshold (estimated) beyond which execution is blocked or requires an explicit opt-in
- limits on expression complexity (number of OR clauses, grouping depth, etc.)
- Query-specific timeouts / max rows / max execution time (or reuse of existing platform limits)
- clear diagnostics/telemetry when a Query is rejected or throttled due to these thresholds
The goal is to increase expressiveness without putting platform stability at risk.
Expected benefits
- More expressive joins aligned with real business needs
- Less query duplication and fewer AL-side workarounds
- Improved readability (logic located where it belongs: the join/link)
- Better SQL pushdown, with guardrails to prevent abuse