Fluent BQL– Increase the readability & maintainability of your queries

Mark Franks | December 6, 2018

Introduction

In this post, I’ll provide a basic introduction to our new Fluent Business Query Language which is a complimentary technology to BQL. But before I do, let’s have a bit of a review of our Business Query Language (BQL) providing some context for those of you reading this post who may be new to the Acumatica platform..

To query and manipulate data from the Acumatica database, a developer will use BQL — part of the data access layer of Acumatica Framework. BQL statements represent a specific set of Acumatica queries that are translated into the appropriate SQL required by the back-end database by the framework.  This shields the developer from the language nuances inherent to various database providers and validate these queries at compilation time.

BQL solves two discrete and rather important problems developers often face.  One, a simple way to create queries from a set of predefined primitives and two, declaring queries on an attribute level in C#. Both of these problems are solved through the usage of generic types.

Sequences of same-level elements in BQL are implemented very similarly to a linked list structure, where each next element must be passed as a generic parameter of the previous one. This allows a developer to build a query by combining primitive elements together without many limits.  Yet, often for this flexibility to be manifested, a developer will sacrifice readability, and hence maintainability.

It’s no wonder that developers prefer to rewrite most of their BQL queries from scratch, instead of trying to analyze and modify older ones. If we are honest, we can’t really blame them! Unlike a computer, a person cannot easily analyze heavily nested structures, especially when there is no specific need of a nested structure.

There are other issues with BQL such as “numbered” overloading of base query components, multiple select class families with overlapping structures, as well as the problem of balancing angle brackets of generic classes, correlating rather poorly with parentheses of native SQL-queries.

All these quote, problems, point to the fact that BQL provides us a way to produce an endless variety of queries.  For developers, however, it is very difficult to read and maintain a number of complex BQL queries, due mostly to the structure of the BQL-language itself.

To address these problems, one of our engineering teams at Acumatica has come up with a solution by creating Fluent BQL.

What is Fluent BQL

Unlike BQL, which uses function-like generic class declarations, Fluent BQL uses fluent generic class declarations, based on nesting of generic classes. This naturally splits and organizes top-most components of a query such as joins and aggregates, and completely removes the ambiguity of command names. The query structure becomes more similar to the SQL structure, where each section does not depend on others, and can appear only in the places assigned to it. Nesting of classes in a command definition helps to reduce nesting of components in a command declaration. By using FBQL, a developer doesn’t have to pick up a suitable command overload.  Rather, just start typing a command and IntelliSense will offer continuations that are relevant for the current query state. Also, it should be noted that sections of a query are not separated by commas, which is good thing since they are not equal in a certain sense, and their count can vary only within very discrete limits.

Now let’s take a look at some code to get a peek at FBQL.  As an example, we will make a DAC’s field compatible. With fluent comparisons, a developer has to use PX.Data.BQL.Bql[Type].Field<TSelf> as a base class for the field as illustrated below.

public class MyTable : PX.Data.IBqlTable

{

// public abstract class myField : PX.Data.IBqlField { } — old way, not

compatible with fluent comparisons

public abstract class myField : PX.Data.BQL.BqlString.Field<myField> { }

// myField still implements IBqlField interface, since BqlField<myField,

IBqlString> class does it

public virtual string MyField { get; set; }

}

Note that all Acumatica core constants and fields of all the DAC’s are already compatible with fluent comparisons.

Another thing you can do with Fluent BQL is to use fluent comparisons with fields whose base class is not PX.Data.BQL.Bql[Type].Field<TSelf>. Wrapping the field in an

Use<>.As[Type] class will solve the usage problem.

public abstract class modernField : BqlField<modernField> {} //
modernField.IsEqual<Zero>
public virtual decimal? ModernField { get; set; }
public abstract class legacyField : IBqlField {} //
Use<legacyField>.AsDecimal.IsEqual<Zero>
public virtual decimal? LegacyField { get; set; }

Wrapping a modern-style field into a Use<>.As[Type] may seem a bit excessive.  However, if in the future, a legacy-style field is suddenly turned into a modern-style field, queries that utilize the Use<legacyField>.As[Type] will not be broken in your code. The Use<>.As[Type] can wrap not only legacy fields, but also any class that implements IBqlOperand, allowing it to be used in the fluent style.

Fluent Conditions

The Fluent conditions approach uses dot-separated chaining of TUnary conditions. All IBqlUnary classes, including fluent comparisons, of the Acumatica core can be used with fluent conditions. To append a condition to another one, just use .And<TUnary> or .Or<TUnary> nested classes of the first condition expression.


TCondition.And<TNextCondition>
TCondition.Or<TNextCondition>
// where TCondition : IBqlUnary
// where TNextCondition : IBqlUnary


BQL doesn’t have explicit brackets for conditions, but brackets could be appended by wrapping a part of a condition in additional Where-clause, which is no trivial task. Together with chaining-patterns, absence of explicit brackets, leads to a non-obvious, counter intuitive as well as difficult to maintain representation of conditions.

FBQL brings a brackets class, which represents explicit brackets.  Further, what is pretty exciting is that now parentheses are represented by angle brackets of the new fluent .And<> and .Or<> classes. Because of this, there is only one case when you may need to use a brackets class explicitly ‒‒ it is when you start your condition with something enclosed in parentheses. If .And<> or .Or<> contains a single comparison, it does not become wrapped in parentheses.

Some Comparisons between FBQL & BQL

* Joins are not actually a section of a query, in comparison to other its sections such as Where<>, Aggregate<>, and OrderBy<>, that are containers for some query elements.

** All views that contain aggregation are read-only by default.

FBQL is Derived from BQL

The following three simple guidelines will help to greatly increase the readability and the maintainability of FBQL-queries compared to classic BQL-queries.

  1. Use a fluent generic class declaration pattern, instead of a classic function-like generic class declaration pattern.
  2. Use containers (or arrays) of components, rather than chains (or linked lists) of components.
  3. Use of one family of Selects and single Search and Views that are based on the Select-family, instead of three similar families.

Yet it is important to keep in mind that FBQL doesn’t replace BQL – FBQL complements it!  They can be used together in the same file or class, without any naming conflicts. Moreover, FBQL is based on BQL, since FBQL-command delegates all the querying work to a corresponding BQL-command. All that FBQL tries to achieve is a much higher level of convenience for developers.

Summary

We have spent time in this post introducing you to one of our latest developer technology offerings, the Fluent Business Query Language – FBQL and hopefully articulated its value to developers building applications and integrations with Acumatica’s robust Cloud-based ERP platform.  FBQL will help developers maintain a library of queries by making them much more easily readable. This is accomplished fundamentally by changing the inherent structure of the the BQL language itself.  It doesn’t replace BQL, per se, but offers a complementary query language that can be used together, in a convenient fashion, enabling developers to increase their efficiency in writing and maintaining an array of complex queries.

Here’s a summary of the key features:

  • Use of a fluent generic class declaration pattern, instead of classic functional-like generic class declaration pattern;
  • Intuitive segregation of query sections;
  • Improved, simplified, and intuitive condition building;
  • Strongly typed fields/constants/functions and static type-checking in conditions;
  • Use of options/hints instead of numbered overloads of query components;
  • Infix binary operations;
  • Natural balance of angle brackets; and
  • Native support of IntelliSense.

Learn more about FBQL at our Developer Track presented at the upcoming Acumatica Summit in Houston at the end of January.  If you haven’t registered yet, you can do so here.

Mark Franks

As a Platform Evangelist, Mark is responsible for showing people the specifics about what makes Acumatica’s Cloud Development Plaform wonderfully attractive to ISV & Partners. He's also passionate about Running, Latin, and his family. | E-mail: mfranks@acumatica.com | Skype: mfranks |

Subscribe to our bi-weekly newsletter

Subscribe