What is Multi-Query?
A template-driven engine that fetches a root SQL query and recursively fetches child collections, building a complete record tree in one API call. No N+1 problems, no manual joins, no multi-round-trip orchestration code.
The Problem It Solves
Enterprise applications frequently need to retrieve hierarchical data — an employee with all their payslips, each payslip with its deductions, or a department with all its teams and members. Traditionally this forces one of three painful approaches:
- N+1 queries: Fetch 50 employees, then issue 50 individual payslip queries — one per employee — resulting in 51 database round-trips for a single report.
- Mega joins: Write a single SQL statement with multiple JOIN clauses and GROUP BY aggregations, then re-flatten the result in application code, duplicating parent columns for every child row.
- Multiple API calls: Call
/employees, then for each ID call/payslips?employeeId=X, then for each payslip call/deductions?payslipId=Y— client code becomes fragile orchestration logic scattered across front-end components.
How It Works
Write a QueryTemplate JSON document defining the root SQL query, any caller-supplied parameters, output options, and one or more child collection definitions. Each child carries its own SQL and can have further grandchild collections.
dbo.Shared_Configurations
Insert a row where Code is your unique catalogue code (e.g. PAYROLL_REPORT_DEPT) and Value is the serialised JSON template. The engine reads from this table at execution time; no deployment is required to add or update a template.
Issue a GET request to /api/v1/expressions/multiquery/{templateCode} with a valid Bearer JWT, or embed the directive {{multi-query:sqlserver.TEMPLATE_CODE}} inside any BizFirst expression. Parameters declared in the template are passed as query-string values.
The SqlServerDeriveEngine extracts the caller's TenantID from the JWT, then executes the root query. For every root row returned it executes each child query, substituting @parent.ColumnName tokens with values from that row and @TenantID with the verified tenant. The process repeats recursively up to maxDepth levels.
Architecture
The Multi-Query Engine is composed of five project layers, each with a clearly bounded responsibility:
| Layer | Project | Responsibility |
|---|---|---|
| Domain | BizFirst.Domain |
Defines QueryTemplate, ChildCollection, QueryParameter, and QueryOptions domain models. No infrastructure dependencies. |
| Services | BizFirst.Services |
Contains the engine orchestration logic: template resolution, recursive child execution, result tree assembly, and cache management. |
| SQL Server Infrastructure | BizFirst.Infrastructure.SqlServer |
Implements the SqlServerDeriveEngine: ADO.NET query execution, @parent token substitution, and tenant parameter injection. |
| Api.Base | BizFirst.Api.Base |
Provides shared controller base classes, JWT parsing, tenant context resolution, and the expression directive handler. |
| Api | BizFirst.Api |
Exposes the REST endpoints: execute JSON, execute HTML, run-stored, and run-direct. Enforces TenantAdmin role on all routes. |
Key Concepts
| Concept | Description |
|---|---|
| Template Code | A unique string identifier stored in dbo.Shared_Configurations.Code. Used in REST paths and expression directives to address the template. |
| QueryTemplate JSON | The JSON document that defines the complete query tree: root SQL, parameters, options, and child collections. Stored in dbo.Shared_Configurations.Value. |
| ChildCollection | A nested query definition inside a QueryTemplate. Each collection has a collectionName, its own SQL, and may contain further child collections for deeper hierarchies. |
| @parent Tokens | Placeholder syntax (@parent.ColumnName, @parent.id, @parent.parent.ColumnName) in child SQL that is replaced at runtime with column values from the current parent row. |
| outputFormat | Controls whether the engine returns json (compact nested JsonArray) or html (expandable cascaded HTML table). Declared in template options; can be overridden per-request. |
| TenantID Injection | The engine reads the caller's verified TenantID from the JWT and injects it as a SQL parameter in every query in the tree. Callers have no mechanism to supply or override this value. |
AND TenantID = @TenantID from its SQL will have @TenantID injected as a parameter regardless; template authors must include the predicate in their WHERE clause to apply it.
What Multi-Query Is Not
Multi-Query is not a general-purpose ORM, a graph database interface, or a replacement for stored procedures that perform complex business logic. It is optimised for read operations — fetching structured record trees for reporting, display, and export. Write operations, transactional logic, and complex aggregations should continue to use the appropriate BizFirst service layer.