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