All docs
V20.2
20.2
20.1
The page you are viewing does not exist in version 20.1. This link will take you to the root page.
19.2
The page you are viewing does not exist in version 19.2. This link will take you to the root page.
19.1
The page you are viewing does not exist in version 19.1. This link will take you to the root page.
18.2
The page you are viewing does not exist in version 18.2. This link will take you to the root page.

Query Builder

  • 4 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.

Query Builder Overview

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

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

@(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:

Select Tables

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

Query Builder Add Table

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

Query Builder Select Fields

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

Query Builder Search List

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

Query Builder 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.

Query Builder Delete Button

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:

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 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:

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.