Skip to content
d365ez
← All articles

Article #0016

D365 F&O

Mastering the SysDa Framework in D365 F&O: Clean, Dynamic Queries in X++

Learn how to use the SysDa framework in Dynamics 365 F&O to build clean, strongly typed, and dynamic X++ queries without legacy QueryRun boilerplate.

Category
D365 F&O
Published
Reading time
3 min read
Updated
Isometric visual metaphor of building structured database queries dynamically in D365 F&O

Environment & Context

  • Product: Dynamics 365 Finance & Operations
  • Technology: X++
  • Prerequisites: Basic familiarity with table buffers and standard X++ select statements

Why This Matters

Every D365FO developer knows the classic dilemma when building dynamic queries: do you write sprawling, hardcoded select statements with multiple if/else branches, or do you assemble a complex Query / QueryBuildDataSource / QueryRun tree packed with string-based range literals?

The SysDa (System Data Access) framework offers a modern, strongly typed alternative. Built by Microsoft for extensible scenarios, SysDa lets you compose dynamic SQL queries programmatically using clean, readable, and reusable object-oriented syntax.

Diagram showing the structure of SysDa query components in X++ The SysDa framework decouples table buffers, dynamic expressions, and statement execution into clean, testable objects.


The Core Building Blocks of SysDa

When writing a SysDa query, you work with three primary components:

  1. SysDaQueryObject: Encapsulates the target table buffer and fields.
  2. SysDa...Expression: Defines where-clause conditions (such as SysDaEqualsExpression, SysDaAndExpression, or SysDaGreaterThanExpression).
  3. SysDaSearchStatement: Executes the query and iterates through records cleanly.

Suppose we need a service method that queries CustTable based on an optional customer group and an optional blocked status.

Here is how to build this dynamically using SysDa:

using Microsoft.Dynamics.BusinessPlatform.SharedTypes;

public final class DynamicCustLookupService
{
    public static void findCustomers(CustGroupId _custGroup, CustVendorBlocked _blockedStatus)
    {
        CustTable custTable;
        
        // 1. Initialize the query object pointing to our table buffer
        SysDaQueryObject query = new SysDaQueryObject(custTable);
        
        // Container for our dynamic where clause
        SysDaBinaryExpression filterExpression = null;

        // 2. Add dynamic conditions conditionally
        if (_custGroup)
        {
            SysDaEqualsExpression groupExpr = new SysDaEqualsExpression(
                new SysDaFieldExpression(custTable, fieldStr(CustTable, CustGroup)),
                new SysDaValueExpression(_custGroup));
            
            filterExpression = groupExpr;
        }

        if (_blockedStatus != CustVendorBlocked::Null)
        {
            SysDaEqualsExpression blockedExpr = new SysDaEqualsExpression(
                new SysDaFieldExpression(custTable, fieldStr(CustTable, Blocked)),
                new SysDaValueExpression(_blockedStatus));

            // Combine expressions with AND logic if a previous filter exists
            filterExpression = filterExpression 
                ? new SysDaAndExpression(filterExpression, blockedExpr)
                : blockedExpr;
        }

        // 3. Attach the combined expression to the query object
        if (filterExpression)
        {
            query.whereClause(filterExpression);
        }

        // 4. Execute using SysDaSearchStatement
        SysDaSearchStatement searchStatement = new SysDaSearchStatement();
        
        while (searchStatement.findNext(query))
        {
            Info(strFmt("Customer: %1, Group: %2, Blocked: %3", 
                custTable.AccountNum, 
                custTable.CustGroup, 
                custTable.Blocked));
        }
    }
}

Pro Tip for Junior Developers

Choose the right tool for the job:

  • Use standard X++ while select for fixed, compile-time verified static queries.
  • Use AOT Queries / QueryRun when binding data to Form controls, SSRS reports, or data entities where kernel UI interactions are required.
  • Use SysDa for backend services, complex business engines, or algorithmic modules where queries must adapt dynamically without resorting to string-based SQL injection hazards or verbose QueryBuildRange code.

Common Pitfalls to Avoid

  • Forgetting Field References: When creating a SysDaFieldExpression, always pass the table buffer variable and use the compile-safe fieldStr() intrinsic function rather than raw string field names.
  • Unbound Buffers: Do not re-initialize the buffer instance (custTable = null;) after passing it to SysDaQueryObject, as the search statement directly populates the buffer instance passed to the constructor.

Key Takeaway

The SysDa framework brings modular, object-oriented query design to X++. By breaking clauses into composable expressions, you keep your codebase maintainable, unit-testable, and clean.

───

Disclaimer

The content on this blog is provided for informational and educational purposes only. Dynamics 365 EZ makes no representations or warranties of any kind, express or implied, about the accuracy, completeness, reliability, suitability, or availability of the information contained herein.

Any reliance you place on such information is strictly at your own risk. Dynamics 365 EZ shall not be liable for any errors or omissions, or for any losses, injuries, or damages arising from the use of, or reliance on, any information displayed on this site.