Skip to content

Query Builder Support for API Projects

Overview

This guide outlines how to implement Query Builder Models and Utilities in your API Project to enable user-defined queries. The implementation involves creating a controller, defining Query Builder models, and adding utilities for executing and generating SQL statements from these queries. The resulting customization will allow users to construct complex queries with subqueries support, filtering, logical operators, and field selection, executed efficiently against a data source.

Implementation Options

There are two approaches to implement Query Builder support in your API project:

ApproachBest ForEffort Level
NuGet Package (Infragistics.QueryBuilder. Executor)Quick integration, standard use casesLow
Manual ImplementationFull customization, complex requirementsMedium-High

The Infragistics.QueryBuilder. Executor package provides ready-to-use classes, conditions, and methods that you can plug directly into your existing API project with minimal configuration.

NuGet

Prerequisites

  • .NET 8 or .NET 9
  • Microsoft.EntityFrameworkCore
  • AutoMapper
  • An existing ASP.NET Core API project

Step 1: Install the Package

dotnet add package Infragistics.QueryBuilder.Executor

Step 2: Configure Services

In your Program.cs or Startup.cs, register the QueryBuilder service with your DbContext and result DTO:

// Program.cs
builder.Services.AddQueryBuilder<MyDbContext, MyResultDto>();

// Required for SwaggerUI compatibility
builder.Services.AddControllers().AddNewtonsoftJson(o => 
    o.SerializerSettings.ReferenceLoopHandling = ReferenceLoopHandling.Ignore);

Step 3: Expose the Query Endpoint

Configure the endpoint routing to expose the query builder functionality:

app.UseEndpoints(endpoints => 
{ 
    endpoints.UseQueryBuilder<MyDbContext, MyResultDto>("/api/query"); 
});

Step 4: Send Queries

Your API is now ready to accept query payloads. Here’s an example request:

{
    "Entity": "Users",
    "ReturnFields": ["Id", "Name", "Email"],
    "Operator": "And",
    "FilteringOperands": [
        {
            "FieldName": "IsActive",
            "Condition": { "Name": "equals" },
            "SearchVal": true
        },
        {
            "FieldName": "CreatedDate",
            "Condition": { "Name": "after" },
            "SearchVal": "2024-01-01"
        }
    ]
}

Step 5: SQL Generation (Optional)

For diagnostics or analysis, you can generate SQL from the query model:

var sql = SqlGenerator.GenerateSql(query);
Console.WriteLine(sql);
// Output: SELECT Id, Name, Email FROM Users WHERE IsActive = 1 AND CreatedDate > '2024-01-01'

Option 2: Manual Implementation

For projects requiring full customization or complex entity mappings, you can implement the Query Builder manually. The implementation is demonstrated in the NorthwindAPI REST API project.

Prerequisites

  • A . NET Core or .NET API project
  • Familiarity with LINQ, Entity Framework, or another ORM for data querying
  • Basic knowledge of C# and ASP.NET Core controllers

Step 1: Create the QueryBuilderController

The QueryBuilderController serves as the entry point for handling query requests. It receives a Query object from the client, processes it using the QueryExecutor, and returns the results.

Create a file named QueryBuilderController.cs in your Controllers directory:

[ApiController]
[Route("[controller]")]
public class QueryBuilderController : ControllerBase
{
    private readonly DataContext dataContext;
    private readonly IMapper mapper;
    private readonly ILogger<QueryBuilderController> logger;

    public QueryBuilderController(DataContext dataContext, IMapper mapper, ILogger<QueryBuilderController> logger)
    {
        this.dataContext = dataContext;
        this.mapper = mapper;
        this.logger = logger;
    }

    [HttpPost("ExecuteQuery")]
    [Consumes("application/json")]
    [Produces("application/json")]
    public ActionResult<QueryBuilderResult> ExecuteQuery(Query query)
    {
        var sanitizedEntity = query.Entity.Replace("\r", string.Empty).Replace("\n", string.Empty);
        logger.LogInformation("Executing query for entity: {Entity}", sanitizedEntity);
        var t = query.Entity.ToLower(CultureInfo.InvariantCulture);
        return Ok(new Dictionary<string, object[]?>
        {
            {
                t,
                t switch
                {
                    "addresses" => dataContext.Addresses.Run<AddressDb, AddressDto>(query, mapper),
                    "categories" => dataContext.Categories.Run<CategoryDb, CategoryDto>(query, mapper),
                    "products" => dataContext.Products.Run<ProductDb, ProductDto>(query, mapper),
                    "regions" => dataContext.Regions. Run<RegionDb, RegionDto>(query, mapper),
                    "territories" => dataContext.Territories. Run<TerritoryDb, TerritoryDto>(query, mapper),
                    "employees" => dataContext.Employees. Run<EmployeeDb, EmployeeDto>(query, mapper),
                    "customers" => dataContext.Customers.Run<CustomerDb, CustomerDto>(query, mapper),
                    "orders" => dataContext.Orders.Run<OrderDb, OrderDto>(query, mapper),
                    "orderdetails" => dataContext.OrderDetails.Run<OrderDetailDb, OrderDetailDto>(query, mapper),
                    "shippers" => dataContext.Shippers.Run<ShipperDb, ShipperDto>(query, mapper),
                    "suppliers" => dataContext.Suppliers.Run<SupplierDb, SupplierDto>(query, mapper),
                    _ => throw new InvalidOperationException($"Unknown entity {t}"),
                }
            },
        });
    }
}

For a complete example, see NorthwindAPI QueryBuilderController.

Step 2: Define Query Builder Models

The Query Builder relies on a set of models to represent the query structure. Place these in a QueryBuilder directory or namespace.

FilterType Enum

Defines logical operators for combining filters:

public enum FilterType
{
    And = 0,
    Or = 1,
}

Query Class

Represents the overall query structure:

public class Query
{
    public string Entity { get; set; }
    public string[] ReturnFields { get; set; }
    public FilterType Operator { get; set; }
    public QueryFilter[] FilteringOperands { get; set; }
}
PropertyDescription
EntityThe target entity/table being queried (e.g., “Products”)
ReturnFieldsFields to include in the result (e.g., ["Name", "Price"] or ["*"] for all)
OperatorLogical operator (And or Or) for combining filters
FilteringOperandsArray of QueryFilter objects defining conditions

QueryFilter Class

Represents an individual filtering condition:

public class QueryFilter
{
    // Basic condition
    public string?  FieldName { get; set; }
    public bool? IgnoreCase { get; set; }
    public QueryFilterCondition? Condition { get; set; }
    public object? SearchVal { get; set; }
    public Query? SearchTree { get; set; }
    // And/Or
    public FilterType?  Operator { get; set; }
    public QueryFilter[] FilteringOperands { get; set; }
}

QueryFilterCondition Class

Defines available filtering conditions:

public class QueryFilterCondition
{
    public string Name { get; set; }
    public bool IsUnary { get; set; }
    public string IconName { get; set; }
}

For reference, see NorthwindAPI QueryBuilder Models.

Step 3: Implement QueryExecutor

The QueryExecutor class processes Query objects and translates them into LINQ expressions for execution against IQueryable data sources.

private static Expression BuildConditionExpression<TEntity>(
    DataContext db, 
    IQueryable<TEntity> source, 
    QueryFilter filter, 
    ParameterExpression parameter)
{
    if (filter. FieldName is not null && filter.Condition is not null)
    {
        var property = source.ElementType.GetProperty(
            filter.FieldName, 
            BindingFlags. IgnoreCase | BindingFlags.Public | BindingFlags. Instance)
            ?? throw new InvalidOperationException($"Property '{filter.FieldName}' not found");
        
        var field = Expression.Property(parameter, property);
        var targetType = property.PropertyType;
        var searchValue = GetSearchValue(filter.SearchVal, targetType);
        
        Expression condition = filter.Condition.Name switch
        {
            "null"                  => Expression.Equal(field, Expression.Constant(null)),
            "notNull"               => Expression.NotEqual(field, Expression.Constant(null)),
            "empty"                 => Expression.Equal(field, Expression.Constant(string.Empty)),
            "notEmpty"              => Expression.NotEqual(field, Expression.Constant(string.Empty)),
            "equals"                => Expression.Equal(field, searchValue),
            "doesNotEqual"          => Expression.NotEqual(field, searchValue),
            "inQuery"               => BuildInExpression(db, filter.SearchTree, field),
            "notInQuery"            => Expression.Not(BuildInExpression(db, filter.SearchTree, field)),
            "contains"              => CallContains(field, searchValue),
            "doesNotContain"        => Expression.Not(CallContains(field, searchValue)),
            "startsWith"            => CallStartsWith(field, searchValue),
            "endsWith"              => CallEndsWith(field, searchValue),
            "greaterThan"           => Expression.GreaterThan(field, searchValue),
            "lessThan"              => Expression.LessThan(field, searchValue),
            "greaterThanOrEqualTo"  => Expression.GreaterThanOrEqual(field, searchValue),
            "lessThanOrEqualTo"     => Expression.LessThanOrEqual(field, searchValue),
            "before"                => Expression.LessThan(field, searchValue),
            "after"                 => Expression.GreaterThan(field, searchValue),
            "today"                 => BuildTodayExpression(field),
            "yesterday"             => BuildYesterdayExpression(field),
            "thisMonth"             => BuildThisMonthExpression(field),
            "lastMonth"             => BuildLastMonthExpression(field),
            "nextMonth"             => BuildNextMonthExpression(field),
            "thisYear"              => BuildThisYearExpression(field),
            "lastYear"              => BuildLastYearExpression(field),
            "nextYear"              => BuildNextYearExpression(field),
            "true"                  => Expression.Equal(field, Expression.Constant(true)),
            "false"                 => Expression.Equal(field, Expression.Constant(false)),
            _                       => throw new NotImplementedException($"Condition not implemented: {filter.Condition. Name}"),
        };

        return condition;
    }
    else
    {
        // Handle nested filter groups
        var subexpressions = filter.FilteringOperands? 
            .Select(f => BuildConditionExpression(db, source, f, parameter))
            .ToArray();
        
        if (subexpressions == null || ! subexpressions.Any())
            return Expression. Constant(true);

        return filter.Operator == FilterType.And
            ? subexpressions. Aggregate(Expression.AndAlso)
            : subexpressions.Aggregate(Expression.OrElse);
    }
}

Supported Filter Conditions

Both implementation options support the following filter conditions, which align with the Angular Query Builder component (IgxQueryBuilderComponent):

String Conditions

ConditionDescription
containsField contains the search value
doesNotContainField does not contain the search value
startsWithField starts with the search value
endsWithField ends with the search value
equalsField equals the search value
doesNotEqualField does not equal the search value
emptyField is empty
notEmptyField is not empty
nullField is null
notNullField is not null

Numeric Conditions

ConditionDescription
equalsField equals the search value
doesNotEqualField does not equal the search value
greaterThanField is greater than the search value
lessThanField is less than the search value
greaterThanOrEqualToField is greater than or equal to the search value
lessThanOrEqualToField is less than or equal to the search value
emptyField is empty/zero
notEmptyField is not empty/zero
nullField is null
notNullField is not null

Date/Time Conditions

ConditionDescription
equalsField equals the search value
doesNotEqualField does not equal the search value
beforeField is before the search value
afterField is after the search value
todayField matches today’s date
yesterdayField matches yesterday’s date
thisMonthField is within the current month
lastMonthField is within the previous month
nextMonthField is within the next month
thisYearField is within the current year
lastYearField is within the previous year
nextYearField is within the next year
nullField is null
notNullField is not null

Boolean Conditions

ConditionDescription
trueField is true
falseField is false
nullField is null
notNullField is not null

Subquery Conditions

ConditionDescription
inQueryField value exists in the subquery results
notInQueryField value does not exist in the subquery results

Angular Frontend Integration

The Query Builder API is designed to work seamlessly with the Ignite UI for Angular Query Builder component (IgxQueryBuilderComponent). The component generates query structures that match the expected API payload format.

Key Angular Components

From the IgniteUI/igniteui-angular repository:

import {
    FilteringExpressionsTree,
    FilteringLogic,
    IgxQueryBuilderComponent,
    IExpressionTree,
    IgxNumberFilteringOperand,
    IgxStringFilteringOperand,
    IgxBooleanFilteringOperand,
    IgxDateFilteringOperand,
    IgxTimeFilteringOperand,
    IgxDateTimeFilteringOperand
} from 'igniteui-angular';

Example: Creating a Query Expression Tree

// Define entities and fields
const entities = [
    {
        name: 'Products',
        fields: [
            { field: 'productId', dataType: 'number' },
            { field: 'productName', dataType: 'string' },
            { field: 'unitPrice', dataType: 'number' },
            { field: 'discontinued', dataType: 'boolean' },
            { field: 'categoryId', dataType: 'number' }
        ]
    },
    {
        name: 'OrderDetails',
        fields: [
            { field: 'productId', dataType: 'number' },
            { field: 'quantity', dataType: 'number' },
            { field: 'discount', dataType: 'number' }
        ]
    }
];

// Build a complex query with subquery
const innerTree = new FilteringExpressionsTree(
    FilteringLogic. And, 
    undefined, 
    'OrderDetails', 
    ['productId']
);

innerTree.filteringOperands. push({
    fieldName: 'quantity',
    condition: IgxNumberFilteringOperand. instance().condition('greaterThanOrEqualTo'),
    conditionName: 'greaterThanOrEqualTo',
    searchVal: 10
});

const mainTree = new FilteringExpressionsTree(
    FilteringLogic.And, 
    undefined, 
    'Products', 
    ['*']
);

mainTree.filteringOperands.push({
    fieldName: 'productId',
    condition: IgxStringFilteringOperand.instance().condition('inQuery'),
    conditionName:  'inQuery',
    searchTree: innerTree
});

mainTree.filteringOperands.push({
    fieldName: 'discontinued',
    condition: IgxBooleanFilteringOperand.instance().condition('false'),
    conditionName: 'false'
});

Using the Query Builder Component

<igx-query-builder 
    #queryBuilder
    [entities]="entities"
    [expressionTree]="expressionTree"
    (expressionTreeChange)="onQueryChange($event)">
    <igx-query-builder-header [title]="'Product Query Builder'">
    </igx-query-builder-header>
</igx-query-builder>

Example: Complex Query with Subquery

Query Payload

{
    "filteringOperands": [
        {
            "fieldName": "categoryId",
            "condition": {
                "name": "equals",
                "isUnary": false,
                "iconName": "filter_equal"
            },
            "conditionName": "equals",
            "searchVal": 1,
            "searchTree": null,
            "ignoreCase": true
        },
        {
            "fieldName": "productId",
            "condition": {
                "name": "inQuery",
                "isUnary": false,
                "isNestedQuery": true,
                "iconName": "in"
            },
            "conditionName": "inQuery",
            "searchVal": null,
            "searchTree": {
                "filteringOperands": [
                    {
                        "fieldName": "quantity",
                        "condition": {
                            "name": "greaterThanOrEqualTo",
                            "isUnary": false,
                            "iconName": "filter_greater_than_or_equal"
                        },
                        "conditionName": "greaterThanOrEqualTo",
                        "searchVal": 10,
                        "searchTree": null,
                        "ignoreCase": true
                    },
                    {
                        "fieldName": "quantity",
                        "condition": {
                            "name": "lessThanOrEqualTo",
                            "isUnary": false,
                            "iconName": "filter_less_than_or_equal"
                        },
                        "conditionName": "lessThanOrEqualTo",
                        "searchVal": 15,
                        "searchTree": null,
                        "ignoreCase": true
                    }
                ],
                "operator": 0,
                "entity": "orderDetails",
                "returnFields": [
                    "productId"
                ]
            },
            "ignoreCase": true
        }
    ],
    "operator": 0,
    "entity": "products",
    "returnFields": [
        "productId",
        "productName",
        "supplierId",
        "categoryId",
        "quantityPerUnit",
        "unitPrice",
        "unitsInStock",
        "unitsOnOrder",
        "reorderLevel",
        "discontinued"
    ]
}

Generated SQL Output

SELECT * 
FROM products 
WHERE categoryId = {{selectedCategory}} 
AND productId IN ( 
    SELECT productId 
    FROM orderDetails 
    WHERE quantity >= {{quantityGreaterThan}} 
    AND quantity <= {{quantityLessThan}} 
)

API Endpoint

POST https://data-northwind.appbuilder.dev/QueryBuilder/ExecuteQuery
Content-Type: application/json

Localization Support

The Angular Query Builder component includes built-in localization support for filter condition labels. The igniteui-angular repository provides resource strings in multiple languages including:

  • English (EN) - Default
  • French (FR)
  • German (DE)
  • Spanish (ES)
  • Italian (IT)
  • Portuguese (PT)
  • Polish (PL)
  • Swedish (SV)
  • And more…

Example resource strings:

export const QueryBuilderResourceStringsEN: IQueryBuilderResourceStrings = {
    igx_query_builder_filter_contains: 'Contains',
    igx_query_builder_filter_doesNotContain: 'Does Not Contain',
    igx_query_builder_filter_startsWith: 'Starts With',
    igx_query_builder_filter_endsWith: 'Ends With',
    igx_query_builder_filter_equals:  'Equals',
    igx_query_builder_filter_doesNotEqual: 'Does Not Equal',
    igx_query_builder_filter_greaterThan: 'Greater Than',
    igx_query_builder_filter_lessThan: 'Less Than',
    // ... more strings
};

Additional References