Mise Filtering

Filtering

Where adds a predicate to a SELECT statement. Repeated calls combine with AND. Runtime values in predicates remain database parameters.

Combine conditions

var query = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CourseIdCol:raw}")
    .Select($"{CourseTbl.TitleCol:raw}")
    .Select($"{CourseTbl.CapacityCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {minimumCapacity}")
    .Where(
        $"({CourseTbl.TitleCol:raw} LIKE {prefix + "%"} OR {CourseTbl.CampusIdCol:raw} IS NULL)"
    );

Parentheses keep the OR condition together when the builder joins predicates with AND. Write the grouping you intend; the builder does not infer boolean grouping from your application logic.

Use SQL IS NULL and IS NOT NULL for missing values. Normal parameter interpolation does not change SQL null semantics. See SQL interpolation.

Match a list

int[] selectedIds = [10, 20, 30];

query.WhereIn($"{CourseTbl.CourseIdCol:raw}", selectedIds);

WhereIn binds each list item separately. It accepts an IReadOnlyCollection<T>. An empty list generates a false predicate, 1 = 0, so it returns no matches rather than emitting invalid IN () syntax.

For a very large list, account for your database's parameter limit. The builder does not turn the collection into a temporary table or provider-specific array automatically. A subquery can be a better source when those IDs already live in the database.

Match a query

var largeCampuses = new PostgreSqlQueryBuilder()
    .Select($"{CourseTbl.CampusIdCol:raw}")
    .From($"{CourseTbl.Table:raw}")
    .Where($"{CourseTbl.CapacityCol:raw} >= {100}");

query.WhereIn($"{CourseTbl.CampusIdCol:raw}", largeCampuses);

The child must project a value compatible with the left expression. It compiles into the parent SQL and shares its parameter numbering; it is not executed first as a separate database call.

Check related rows

var matchingCampus = new PostgreSqlQueryBuilder()
    .Select($"1")
    .From($"{CampusTbl.Table:raw}")
    .Where($"{CampusTbl.CampusIdCol:raw} = {CourseTbl.CampusIdCol:raw}")
    .Where($"{CampusTbl.NameCol:raw} = {campusName}");

query.WhereExists(matchingCampus);

This correlated subquery retains courses with a campus matching campusName. EXISTS checks whether the child produces any row, so Select($"1") is sufficient.

See Subqueries for other child-query forms and Aggregation for HAVING predicates after grouping.