18 minlesson

Building Dynamic Queries with IQueryable

Building Dynamic Queries with IQueryable

In this presentation, you will learn how to build a search endpoint that dynamically composes EF Core queries based on the query parameters a client provides. Instead of writing separate queries for every filter combination, you will use IQueryable to build a single, flexible query pipeline.

The Query Parameters DTO

Start by defining a class that represents all possible search parameters. ASP.NET Core model binding maps query string parameters to this object automatically.

csharp
1public class ParcelSearchParams
2{
3    public ParcelStatus? Status { get; set; }
4    public ServiceType? ServiceType { get; set; }
5    public DateTime? CreatedFrom { get; set; }
6    public DateTime? CreatedTo { get; set; }
7    public string? City { get; set; }
8    public string? Country { get; set; }
9    public string? Keyword { get; set; }
10    public string? SortBy { get; set; }
11    public bool SortDescending { get; set; } = true;
12    public int PageSize { get; set; } = 20;
13    public string? Cursor { get; set; }
14}

Each property is nullable (or has a default) so the client can omit any parameter. When a property is null, the corresponding filter is not applied.

Binding Query Parameters

ASP.NET Core binds query string parameters by name. A request like this:

GET /api/parcels?status=InTransit&serviceType=Express&pageSize=10

Automatically populates Status = ParcelStatus.InTransit, ServiceType = ServiceType.Express, and PageSize = 10. All other properties remain null or at their defaults.

For enum properties, ASP.NET Core handles the conversion from string to enum. Adding [JsonConverter(typeof(JsonStringEnumConverter))] to your enum types ensures consistent serialization in both query strings and JSON responses.

Building the Query Pipeline

The core pattern for dynamic filtering is straightforward: start with the full table and conditionally add .Where() clauses.

csharp
1public async Task<PagedResult<ParcelDto>> SearchParcelsAsync(
2    ParcelSearchParams searchParams)
3{
4    IQueryable<Parcel> query = _context.Parcels
5        .Include(p => p.ShipperAddress)
6        .Include(p => p.RecipientAddress);
7

8    // Apply filters conditionally
9    query = ApplyFilters(query, searchParams);
10

11    // Get total count before pagination
12    var totalCount = await query.CountAsync();
13

14    // Apply sorting
15    query = ApplySorting(query, searchParams);
16

17    // Apply cursor-based pagination
18    query = ApplyCursor(query, searchParams);
19

20    // Materialize results
21    var parcels = await query
22        .Take(searchParams.PageSize + 1) // Fetch one extra to detect next page
23        .ToListAsync();
24

25    return BuildPagedResult(parcels, searchParams, totalCount);
26}

Notice the order: filter first, count, sort, paginate, then materialize. This ensures totalCount reflects all matching records, while pagination limits what is returned.

Applying Individual Filters

Each filter checks whether the parameter was provided before adding a .Where() clause. This is the method that makes the query dynamic.

csharp
1private static IQueryable<Parcel> ApplyFilters(
2    IQueryable<Parcel> query,
3    ParcelSearchParams searchParams)
4{
5    if (searchParams.Status.HasValue)
6    {
7        query = query.Where(p => p.Status == searchParams.Status.Value);
8    }
9

10    if (searchParams.ServiceType.HasValue)
11    {
12        query = query.Where(p =>
13            p.ServiceType == searchParams.ServiceType.Value);
14    }
15

16    if (searchParams.CreatedFrom.HasValue)
17    {
18        query = query.Where(p =>
19            p.CreatedAt >= searchParams.CreatedFrom.Value);
20    }
21

22    if (searchParams.CreatedTo.HasValue)
23    {
24        query = query.Where(p =>
25            p.CreatedAt <= searchParams.CreatedTo.Value);
26    }
27

28    if (!string.IsNullOrWhiteSpace(searchParams.City))
29    {
30        query = query.Where(p =>
31            p.ShipperAddress.City == searchParams.City ||
32            p.RecipientAddress.City == searchParams.City);
33    }
34

35    if (!string.IsNullOrWhiteSpace(searchParams.Country))
36    {
37        query = query.Where(p =>
38            p.ShipperAddress.CountryCode == searchParams.Country ||
39            p.RecipientAddress.CountryCode == searchParams.Country);
40    }
41

42    if (!string.IsNullOrWhiteSpace(searchParams.Keyword))
43    {
44        var keyword = searchParams.Keyword.ToLower();
45        query = query.Where(p =>
46            p.TrackingNumber.ToLower().Contains(keyword) ||
47            p.Description.ToLower().Contains(keyword));
48    }
49

50    return query;
51}

How EF Core Handles This

When you chain .Where() calls, EF Core combines them with AND in the generated SQL. If the client only provides status=InTransit and city=Berlin, the SQL looks like:

sql
1SELECT p.*, sa.*, ra.*
2FROM Parcels p
3JOIN Addresses sa ON p.ShipperAddressId = sa.Id
4JOIN Addresses ra ON p.RecipientAddressId = ra.Id
5WHERE p.Status = 2
6  AND (sa.City = 'Berlin' OR ra.City = 'Berlin')

Filters for ServiceType, date range, country, and keyword are absent because those parameters were not provided. No unnecessary conditions clutter the SQL.

Keyword Search Across Multiple Columns

Keyword search is the most flexible filter. It matches a single search term against multiple columns using OR logic inside the Where().

csharp
1if (!string.IsNullOrWhiteSpace(searchParams.Keyword))
2{
3    var keyword = searchParams.Keyword.ToLower();
4    query = query.Where(p =>
5        p.TrackingNumber.ToLower().Contains(keyword) ||
6        p.Description.ToLower().Contains(keyword));
7}

EF Core translates ToLower().Contains() to a case-insensitive LIKE operation in most database providers:

sql
1WHERE LOWER(p.TrackingNumber) LIKE '%pkg-2025%'
2   OR LOWER(p.Description) LIKE '%pkg-2025%'

Performance Considerations

LIKE '%term%' with a leading wildcard cannot use standard B-tree indexes. For small to medium datasets (under 100,000 rows), this is acceptable. For larger datasets, consider:

  • Full-text search indexes (SQL Server or PostgreSQL) for better performance
  • Trigram indexes (PostgreSQL pg_trgm) for efficient substring matching
  • Limiting keyword search to indexed prefix matches (StartsWith instead of Contains)

For our parcel tracking API, Contains is sufficient. The tracking number field is relatively short, and the description field is optional.

Date Range Filtering

Date range filters use separate CreatedFrom and CreatedTo parameters. This allows the client to specify an open-ended range (only a start date or only an end date) or a closed range (both).

csharp
1if (searchParams.CreatedFrom.HasValue)
2{
3    query = query.Where(p =>
4        p.CreatedAt >= searchParams.CreatedFrom.Value);
5}
6

7if (searchParams.CreatedTo.HasValue)
8{
9    query = query.Where(p =>
10        p.CreatedAt <= searchParams.CreatedTo.Value);
11}

A request with both parameters:

GET /api/parcels?createdFrom=2025-02-01&createdTo=2025-02-15

Produces:

sql
1WHERE p.CreatedAt >= '2025-02-01' AND p.CreatedAt <= '2025-02-15'

A request with only createdFrom produces a single condition, and the second WHERE clause is not added.

City and Country Filtering

Address-based filters search across both the shipper and recipient addresses using OR logic. This lets a client find all parcels going to or coming from a specific city.

csharp
1if (!string.IsNullOrWhiteSpace(searchParams.City))
2{
3    query = query.Where(p =>
4        p.ShipperAddress.City == searchParams.City ||
5        p.RecipientAddress.City == searchParams.City);
6}

EF Core navigates the relationships and generates the appropriate JOIN and WHERE clauses:

sql
1WHERE sa.City = 'Berlin' OR ra.City = 'Berlin'

This works because we included the navigation properties with .Include() at the start of the query. Even without .Include(), EF Core can still generate the JOIN for filtering purposes, but including them ensures the address data is loaded in the result.

Registering the Endpoint

Wire the search method into your controller or minimal API endpoint:

csharp
1[HttpGet]
2public async Task<ActionResult<PagedResult<ParcelDto>>> SearchParcels(
3    [FromQuery] ParcelSearchParams searchParams)
4{
5    var result = await _parcelService.SearchParcelsAsync(searchParams);
6    return Ok(result);
7}

The [FromQuery] attribute tells ASP.NET Core to bind all properties from the query string. Each property name matches a query parameter name (case-insensitive).

Testing with Different Filter Combinations

You can test various combinations to verify the dynamic behavior:

1# No filters - returns all parcels (paginated)
2GET /api/parcels
3

4# Single filter
5GET /api/parcels?status=InTransit
6

7# Multiple filters combined
8GET /api/parcels?status=InTransit&serviceType=Express&city=Berlin
9

10# Date range with keyword
11GET /api/parcels?createdFrom=2025-02-01&createdTo=2025-02-15&keyword=PKG
12

13# Full search with all parameters
14GET /api/parcels?status=InTransit&serviceType=Express&createdFrom=2025-02-01&createdTo=2025-02-15&city=Berlin&country=DE&keyword=PKG&sortBy=createdAt&sortDescending=true&pageSize=10

Each combination produces a different SQL query with only the relevant WHERE clauses.

Extracting a Reusable Filter Pattern

As your API grows, you may have multiple entities that need dynamic filtering. Extract the pattern into a common approach:

csharp
1private static IQueryable<T> ApplyFilter<T>(
2    IQueryable<T> query,
3    bool shouldApply,
4    Expression<Func<T, bool>> predicate)
5{
6    return shouldApply ? query.Where(predicate) : query;
7}

Usage:

csharp
1query = ApplyFilter(query,
2    searchParams.Status.HasValue,
3    p => p.Status == searchParams.Status!.Value);
4

5query = ApplyFilter(query,
6    !string.IsNullOrWhiteSpace(searchParams.City),
7    p => p.ShipperAddress.City == searchParams.City ||
8         p.RecipientAddress.City == searchParams.City);

This reduces repetitive if statements while keeping the logic explicit. Each call reads as "apply this filter if this condition is true."

Validating Search Parameters

Add basic validation to prevent invalid queries:

csharp
1public class ParcelSearchParamsValidator
2{
3    public static void Validate(ParcelSearchParams searchParams)
4    {
5        if (searchParams.PageSize < 1)
6            searchParams.PageSize = 1;
7

8        if (searchParams.PageSize > 100)
9            searchParams.PageSize = 100;
10

11        if (searchParams.CreatedFrom.HasValue &&
12            searchParams.CreatedTo.HasValue &&
13            searchParams.CreatedFrom > searchParams.CreatedTo)
14        {
15            throw new ValidationException(
16                "CreatedFrom must be before CreatedTo");
17        }
18    }
19}

Clamp the page size to valid bounds rather than returning an error. This is more ergonomic for API clients. However, do validate logical constraints like the date range order.

Debugging Generated SQL

During development, verify that EF Core generates the SQL you expect. Enable query logging in your DbContext configuration:

csharp
1optionsBuilder
2    .UseNpgsql(connectionString)
3    .LogTo(Console.WriteLine, LogLevel.Information)
4    .EnableSensitiveDataLogging();

This prints every SQL query to the console. Check that:

  • Only the expected WHERE clauses appear
  • JOINs are added only when address filters are used
  • No unnecessary subqueries or client evaluation warnings appear

In production, disable EnableSensitiveDataLogging() to avoid logging parameter values.

Summary

In this presentation, you learned:

  • Define a DTO with nullable properties to represent optional search parameters
  • Use IQueryable and conditional .Where() calls to build dynamic SQL queries
  • Chain filters with AND logic; use OR within a single .Where() for multi-column search
  • Keyword search uses Contains() which translates to LIKE '%term%' in SQL
  • Date range filtering supports open-ended and closed ranges with separate from/to parameters
  • City and country filters search across both shipper and recipient addresses
  • Clamp page size to valid bounds rather than rejecting invalid values
  • Log generated SQL during development to verify query correctness

Next, you will implement cursor-based pagination and sorting on top of these dynamic queries.