CompositeQuery designer

The CompositeQuery designer enables you to create composite queries from entities.

Select Composite Query Design from the Add New Item menu, choose entities as its data sources, and click Create. The CompositeQuery Designer will open, allowing you to connect, group, sort entities, and display reports. It is often used in scenarios involving multi-table or complex queries.

composite1

Query design

Basic settings

composite2

You can specify the following settings in the Field Design tab of the CompositeQuery designer:

  • Display name: The display name of the composite query model. It will generate the [DisplayName] attribute in the C# code.
  • Description: The description of the composite query model. This will appear as comments in the C# code.

Relationship design panel

composite3

Click Add data source in the upper right of the Relationship design panel to select or modify the required entity data source. The entity data source will be displayed in the panel.

Different data sources are connected by fields. Thus, we need to manually connect entity fields in two ways:

  1. In the Relationship design panel, click the down arrow in the data source title bar to display all fields in the data source, then click and drag to connect fields from different data sources. The default is an inner join (modifiable in the Relationship Settings tab). The panel and tab are synchronized.

    composite4

  2. Add and specify data source fields and connection types (Inner Join, Left Join, Right Join) in the Relationship settings tab on the right.

Relationship settings

In the Relationship settings tab, click Add relationship, then select the data source and connection type. Three types of joins are supported:

  • Inner Join: Joins based on common column values, returning rows that meet the join condition.
  • Left Join: Returns all rows from the left table and matched rows from the right table (or NULL if no match).
  • Right Join: Returns all rows from the right table and matched rows from the left table (or NULL if no match).

Then, click the add icon to choose data source fields or enter expressions for connections. You can also perform operations on fields before associating data sources.

image-20240607111422239

Group settings

In the Group settings tab, you can group data source fields. Rows with the same values are combined, and aggregation functions like sum, count, avg, max, min, etc., can be applied to these groups.

To group fields, click Add Group and select the field or enter an expression to group. Grouped fields are automatically added to the result set and can be modified in the result set field. Dynamic grouping can also be achieved using expressions.

image-20240607111651426

Result-set fields

Each group field is automatically added to the result set fields for synchronized output. You can modify, add, or delete fields.

In the Result-set Fields tab, click Add Field to modify the name, display name, and configure values (mapping). You can also designate result-set fields as a collection. On the right side, you can add custom attribute code.

Values (mapping) can be an existing field from the data source or an expression, which allows secondary processing of the output fields.

image-20240607130558129

Group.Key refers to the field used for grouping, while other entities represent the data sources after grouping. Rows with the same values are grouped together.

Under QueryItems, entities represent connected but non-grouped data sources.

Note: These entities and data sources represent collections, allowing the use of aggregate functions and collection operations.

Custom code

You can apply custom code to the result-set, such as namespaces, inheritance, class attributes, and code snippets.

image-20240618112641756

You can also add custom code to the current result set fields, specifically by applying attribute code. This allows for additional flexibility and customization of how result set fields are handled in your project.

image-20240618112709074

For more information, please refer to Custom code.

Filter Design

In the Filter Design tab, you can filter data based on logical conditions and user input (parameters). After creating a filter, if an API with filters is generated in the Service & API Design tab, corresponding filters (data), services (interfaces and business logic), and controllers (methods) will be generated.

In this tab, you can:

  • Add filter parameters or parameter collections.
  • Define logic conditions (fields, expressions, ranges) connected by "and" or "or".

For detailed configuration, refer to Entity Designer > Filter design.

image-20240626172435993

Service and API design

In the Service & API Design tab, you can choose whether to generate controllers and services such as Get, GetList, and GetPage. You can add APIs and filter APIs by clicking the Add service button. Afterward, you can select the Generate API option next to an API to generate its controller.

image-20240626162325820

Service

Three service types are available for a composite query:

  • Get: Fetch single data,
  • GetList: Fetch list data,
  • GetPage: Fetch paginated data.

You can configure settings such as request parameters, response fields, and sorting options for the returned data. Sorting applies to the final result set, not the underlying database query. Descriptions for each service can also be added. For more details, refer to the Entity Designer > Service section.

API

In the API section of the Service & API Design tab, you can configure API request methods, parameter sources, authorization, and more. After selecting the Generate API option, the API is automatically created. You can also further customize the API to fit your specific needs.

image-20240626163547699

You can configure the following API settings:

  • Name: The name of the API. Each API has a unique name, but changing it will affect the request route.
  • Method type: The default HTTP method (GET, POST, etc.). You can change it as needed.
  • Route template: Sets the API's routing format, either globally or for individual APIs.
  • Parameter settings: Parameters can be bound to different sources like:
    • FromQuery: from query string.
    • FromRoute: from route template.
    • FromHeader: from request headers.
  • Authorization settings: Once authorization is enabled in project properties, the API will use the default policy (ASP.NET Core). Other options include:
    • Use upper-level policy: Uses the parent policy, without authorization by default.
    • Use application default Policy: Uses the configured application-level default policy.
    • Use anonymous policy: Allows anonymous access without authentication.

For detailed configuration, refer to the Entity Designer > API section.

image-20240626170407227

Custom Code

You can add custom code to services (interfaces), service implementations, and controllers, including namespaces, inheritance, class attributes, and code snippets.

image-20240618111210093

You can also add custom code to the current services and APIs, including method attributes and code snippets.

image-20240618114353867

For more information, please refer to Custom code.