Skip to main content
All docs
V25.1
  • Query Builder

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

    Query Builder Overview

    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:

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

    Join Tables

    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.).

    Relation Properties

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

    Filter Editor

    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.

    Filter Editor Window

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

    Sort Data

    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.

    Group Aggregate

    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 Preview toolbar button to invoke the Data Preview dialog. This dialog displays the first 100 data records from the query result set.

    Data Preview

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

    Preview Statement Dialog

    Pass the Query to the Server

    When the user clicks the Save Toolbar Save Button 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.