Query Builder

• Sep 09, 2024
• 5 minutes to read

The ASP.NET Core Query Builder control is a graphical tool that allows you to interactively build queries for tables and views of the SqlDataSource, preview SQL statements, and view the results.

View Example: How to Use the Query Builder Control in an ASP.NET Core Application

Supported Databases

The Query Builder control supports all of the data providers that are supported by the SqlDataSource component. For more information, review the following help topic: Supported Data Providers.

Note that the Query Builder control does not support data sources in JSON format.

To bind Query Builder to a desired database server, pass any of the DataConnectionParametersBase descendant instances listed in the Inheritance section to the IQueryBuilderClientSideModelGenerator service. Review the following help topic for a code sample: Add the Query Builder to an Application.

Add the Query Builder to a Page

Use the BuilderFactory extension method, which creates the QueryBuilderHtmlContentBuilder, to add the Query Builder control to your page:

Razor C#

@(Html.DevExpress()
    .QueryBuilder("webQueryBuilder")
    .Height("600px")
    .Bind(Model))

You must add scripts and styles to the page, and register third-party dependencies and DevExpress middleware components to render the Query Builder control. The Query Bulder also requires a model that the controller action supplies. Review the following help topic for instructions on how to integrate the Query Builder into an ASP.NET Core application:

• Add the Query Builder to an Application

Select Tables

Drag a data table (or a view) from the Available Tables and Views list and drop it on the design surface.

Select the check boxes for the table fields that you want to include in the query result set.

You can type in the search box to find a table or a view.

Click a table to invoke the Table Properties panel. This panel allows you to specify a table alias.

You can select a column and define an alias in the Column Properties panel.

Click the Delete button in the Query Builder Toolbar to remove the selected table from the query.

The Undo button cancels the last change made to the query. Click the Redo button to reverse the last undo action.

Join Tables

Drag the key field from one table and drop it onto another table’s field to create a join.

Click the connector line that defines the relationship to invoke the Relation Properties panel. You can define the join type (Inner or Left Outer) and the logical operator (Equal to, Is greater than, etc.).

The Query Builder automatically creates an inner join relationship based on a key column if this relationship is defined at the database level.

Filter Data

You can specify a criteria to filter the resulting data set. Click the ellipsis button next to the Filter… box in the Query Properties panel:.

This action invokes the Filter Editor with a visual interface that allows you to construct filter criteria from a number of filter conditions combined by logical operators.

In advanced mode, you can edit the filter criteria in text form. For more information on criteria operators and functions, review the following help topic:

• Criteria Language Syntax

Filter Groups

The Group Filter option becomes available in the Query Properties panel if data is grouped. Click the ellipsis button next to the Group filter box to invoke the Filter Editor, where you can specify filter conditions for grouped and aggregated data.

Select the Specified Number of Records

Use the Select Top property to return a specific number of rows from a query result. The Offset option specifies how many records to skip within the result.

Select Distinct Values

Set the Select distinct property to Yes to include only non-duplicated values in the query result set.

Sort Data

You can sort data against a specific column. Select the column and set the Sort Type option in the Column Properties panel.

You can also sort data against multiple columns and use the Sort Order setting to define the priority (a lower number indicates a higher priority).

Group and Aggregate Data

Select a column and specify the Group By setting or the Aggregate function in the Column Properties panel.

The Query Builder supports the following aggregate functions:

• Count
• Max
• Min
• Avg
• Sum
• CountDistinct
• AvgDistinct
• SumDistinct

The aggregate function for one column automatically groups other selected columns. When aggregate functions for all columns are set to None, group settings for other columns are reset.

Preview Results

Click the Preview Results toolbar button to invoke the Data Preview dialog. This dialog displays the first 100 data records from the query result set.

The Preview Select Statement toolbar button invokes the Select Statement Preview dialog. This dialog displays the SELECT statement created with the Query Builder.

Pass the Query to the Server

When the user clicks the Save button, the application should pass the resulting query to the server for later use. Review the following topic for a step-by-step tutorial:

• Save the Resulting Query

Customize the Query Builder

Use the QueryBuilderClientSideEventsBuilder to specify the Query Builder event handlers. The client-side API allows you to customize the Query Builder appearance and layout.