Virtual Fields and Objects lets simplify visual building of queries in the case when same derived tables, correlated sub-queries or SQL expressions are frequently used in other queries. Active Query Builder allows adding such entities to the Metadata Container and using them in queries the same way as regular database objects and fields.

Virtual Objects act as user-defined database views. They hold a SELECT query inside, so the Virtual Object name is substituted for containing query and becomes a derived table when it comes for executing such query against the database server.

End-users don't see this substitution, they work with SQL queries containing virtual objects: build them visually, edit SQL query text via a text editor.

Virtual fields can stand for complex SQL expressions (calculated fields), and correlated subqueries.

You can get the SQL query text either with virtual entity names or substitutional SQL expressions at any time: 

• with substitutional SQL expressions to execute against a database server,
• with virtual entity names to display and let edit it by end-users.

For example, one can add the "Customer" virtual field to the "Orders" table containing the following SQL expression:

(SELECT c.Customer_Name FROM Customers c WHERE c.Customer_Id = Orders.Customer_Id)

Mention the "Orders" reference which must be added to establish the correlation with the Orders table to which this field is added to. When the "Orders.Customer" field is used in the query, the user gets the customer name in the query result set.

Notes:

• Sub-queries in expressions of virtual entities must be enclosed in parentheses!
• Any object or field in the Metadata Container becomes virtual when an expression is defined for it, so you can replace real objects with virtual ones to bring in additional functionality to them.
• As opposed to the Alternate Names feature, where real names in the query could be converted into alternate ones, and vice versa, the inverse conversion of SQL expressions into virtual objects and fields is not possible.

Component Setup (.NET Edition)

The ExpandVirtualObjects and ExpandVirtualFields properties exist in two property groups. 

- The QueryBuilder.SQLFormattingOptions group prescribes expanding of virtual objects in the component's UI: database schema view, design pane, query columns list, etc, as well as affects the text returned by the QueryBuilder.FormattedSQL property. 

- The QueryBuilder.SQLGenerrationOptions group determines the expansion of virtual objects in the SQL text returned by the QueryBuilder.SQL property.

If you use virtual entities, you can set all of these properties to false unless you don't want to display expressions of virtual entities to the user for some purposes.

You can also define additional sets of SQL formatting and generation options to get different versions of SQL text by creating new instances of the SQLFormattingOptions class.

Defining virtual objects with names of real objects

One may want to use virtual objects to limit access to the data, for example, in a multi-tenancy application. It is possible to act this way. For instance, having Orders table with the ClientId field in your database, you can add a virtual object Orders and define its Expression property as follows:

SELECT * FROM Orders WHERE ClientId = :ClientId

You can also limit the list of fields explicitly listing them in the SELECT list. This works perfect but requires setting the QueryBuilder.MetadataLoadingOptions.AllowRecursiveVirtualObjects to false, otherwise an exception will be thrown to avoid recursion.

queryBuilder.MetadataContainer.LoadAll(true);
queryBuilder.MetadataLoadingOptions.AllowRecursiveVirtualObjects = false;

var fields = queryBuilder.MetadataContainer.Items.GetItemsRecursive(MetadataType.Field);

foreach (MetadataField field in fields) {

    if (field.Name == "CustomerID")
    {
        MetadataObject metadataObject = (MetadataObject)field.Parent;
        MetadataList fieldList = new MetadataList(queryBuilder.SQLContext);
        fieldList.Assign(metadataObject.Items); // not only fields, but foreign keys as well

        metadataObject.Expression = $"select * from {metadataObject.Name} where CustomerID = :CustomerID";

        metadataObject.Items.Assign(fieldList);
    }
}

Note that setting the AllowRecursiveVirtualObjects property to false drops the possibility of using one reusable query in another. 

Component Setup (VCL Edition)

To work with this feature, you must use two instances of the PlainTextSQLBuilder object: one to get the query text for editing by the end-user and another to get the query for execution against the database server, having set the ExpandVirtualFields and ExpandVirtualObjects properties to True.

Defining virtual objects and fields

Virtual entities can be defined in several ways.

1. Via the "expression" tag of fields and objects in the saved metadata XML file. You can use the Metadata Editor tool (as a standalone utility or by calling it by code) to create and edit metadata XML files.

2. Adding an object or field to the Metadata Container by code by assigning the substitutional SQL expression to the Expression property of MetadataObject and MetadataField objects. There are different approaches to edit the content of Metadata Container, you can read about them here: How to edit the content of Metadata Container?