Programmatically Create TCQL Queries With TCQL AST API

With Version 1.10 time cockpit introduced an API for constructing Abstract Syntax Trees (AST) for TCQL queries. The AST API can be used to create all queries that could be written using the TCQL grammar. All of the classes representing the Nodes in a TCQL AST can be found in the TimeCockpit.Data.QueryLanguage.Syntax namespace. The main entry point for producing TCQL AST nodes is the TCQLExpression class holding static functions taking parameters for the nodes. The samples used in this document are designed against the time cockpit standard data model.

Parts of a Query

Semantically, a query consists of five parts:

The query source describes the pool of entity objects that is queried and can either be a EntitySource, a BackReferenceSource (for sub-selects) or a DeadOfSource (used internally for sync, may be deprecated later).

The optional where condition describes a filter expression that indicates whether a specific entity should be part of the result set or not. The Expression is a base class for all expression with its main representative being BinaryExpression.

Note

Note that while an Expression can have any result type, semantically it is necessary for an expression to produce a boolean result. The result of a query using a where condition with a result type other than boolean is undefined.

The order by clause describes the ordering of the query result and is described by an instance of the OrderBy class node. An OrderBy node consists of one or more OrderByExpressions, with every expression holding a reference to an Expression and a sort order (SortDirection).

The select clause describes the structure of the returned items and optional limitations. The most basic representation of a select clause is the SelectAlias node type, representing selection of the entity as defined in an alias. In the following statement, the Select P part is described by such a SelectAlias node with an alias value of P.

From P In Project Select P

Basic Examples

The most basic query is a query selecting all entities of a given entity such as:

From P In Project Select P

The TCQL AST equivalent can be created in C# code using the following code snippet:

var q = TCQLExpression.Query(TCQLExpression.From("Project"));

In the example above, a query is created passing only a QuerySource, created using the From() method.

Note

Note that no alias was given. If the alias is redundant because only a single query is involved, it can be omitted by setting it to null (the default value). If the alias is null, it is automatically derived from the query source of the parenting query by choosing the first letter of the entity or back reference being selected. If the alias is already taken by a parenting query an incrementing number is appended.

In order to filter the projects returned, a where condition can be added, such as in the following TCQL statement:

From P In Project Where P.Code = 'time cockpit' Select P

var q = TCQLExpression.Query("Project",
            TCQLExpression.MakeBinary(
                BinaryOperator.Equals,
                TCQLExpression.Property("Code"),
                TCQLExpression.Constant("time cockpit")));

In this example, a binary expression is constructed using the MakeBinary() function. The function takes a BinaryOperator and two sub expressions. In this example, the left-hand expression is a member access to a property which is created using the Property() method. The second, right hand operand of the binary expression is a constant string literal, created using the Constant() function.

Expressions

The expression tree in TCQL is a tree with BinaryExpression being the node class for all binary operators, no matter if boolean or not. The constructor to a binary expression takes one of the enum values defined in BinaryOperator, the left hand expression and the right hand expression. The leaf nodes of a TCQL expressions can be one of the following types:

  • Constant (String, Boolean, Decimal, Date, DateTime, Guid, Null)
  • Parameter Access
  • Set
  • NamedSetReference

The constant values can be created by calling the corresponding overload to Constant(). Function calls can be created using the FunctionCall() function. Consider following TCQL statement:

From T In Timesheet Where T.BeginTime < :Now() Select T

The TCQL AST equivalent can be found in the snippet below:

var q = TCQLExpression.Query(
            TCQLExpression.From("Timesheet"),
            TCQLExpression.MakeBinary(BinaryOperator.Less,
                TCQLExpression.Property("BeginTime"),
                TCQLExpression.FunctionCall("Now")),
            TCQLExpression.SelectAlias());

Additional parameter expressions can be passed to the [TCQLExpression.FunctionCall()]((xref:TimeCockpit.Data.QueryLanguage.Syntax.TCQLExpression#TimeCockpit_Data_QueryLanguage_Syntax_TCQLExpression_FunctionCall_System_String_TimeCockpit_Data_QueryLanguage_Syntax_Expression___) method as additional parameters.

Select New With

In previous examples, the simplistic SelectAlias() was used to define the structure of the returned EntityObjects. With TCQL Select New queries can be expressed in the following way:

From P In Project Select New With { P.Code, .TheTitle = P.ProjectName }

This would correspond to the following TCQL AST code snippet:

var q = TCQLExpression.Query(
            "P",
            TCQLExpression.From("Project"),
            TCQLExpression.SelectNewWith(
                TCQLExpression.Member(TCQLExpression.Property("Code")),
                TCQLExpression.Member("TheTitle", TCQLExpression.Property("ProjectName")
            )
        )
    );

Include Statements

TCQL allows fine grained selection of which relations should be loaded using so called include clauses. The most basic include clause is the IncludeAll clause, causing the the full tree of relations to be selected. As an example, consider the TCQL statement:

From P In Project.Include(*) Select P

Using the TCQL API, this statement would be constructed using the following code snippet:

var q = TCQLExpression.Query(TCQLExpression.From("Project", TCQLExpression.IncludeAll));

The sample above constructs a query for a EntitySource with a single include all clause. More specific includes can be constructed using the IncludeRelation() method. The following TCQL statement includes a specific relation:

From P In Project.Include('Customer') Select P

The statement above is equivalent to the following TCQL AST code:

var q = TCQLExpression.Query(TCQLExpression.From("Project", TCQLExpression.IncludeRelation("Customer")));

Another type of include is a conditional include, which includes a back reference with a condition. An example in time cockpit is evaluation of the current hours of work per week for all users. The query produces a table showing the current work hours per week per user.

From U In UserDetail.Include(W, WeeklyHoursOfWork, ((U.W.ValidFrom <= :Now()) And (U.W.ValidUntil > :Now()))) Select New With { U.Username, U.W.SumHours }

The equivalent TCQL AST code is shown below:

var q = TCQLExpression.Query(
            TCQLExpression.From("UserDetail",
                TCQLExpression.IncludeConditional("WeeklyHoursOfWork",
                    TCQLExpression.MakeBinary(
                        BinaryOperator.AndAlso,
                        TCQLExpression.MakeBinary(
                            BinaryOperator.LessEqual,
                            TCQLExpression.Property("W", "ValidFrom"),
                            TCQLExpression.FunctionCall("Now")),
                        TCQLExpression.MakeBinary(
                            BinaryOperator.Greater,
                            TCQLExpression.Property("W", "ValidUntil"),
                            TCQLExpression.FunctionCall("Now"))
                    ),
                    "W"
                )
                ),
                TCQLExpression.SelectNewWith(
                    TCQLExpression.Member(TCQLExpression.Property("Username")),
                    TCQLExpression.Member(TCQLExpression.Property("W", "SumHours"))
                )
);