# WHERE Operators

Complete reference for the predicate operators the WHERE clause builder translates to parameterized SQL.

## Overview

Predicates are C# lambda expressions translated to parameterized SQL at build time. Supply a predicate directly to `Query()` or pass it to `.Where()`. The builder holds a single predicate, so `.Where()` replaces any predicate set earlier; combine conditions inside one lambda with `&&` / `||`. Every value is captured as a SQL parameter, never string-interpolated into the query text.

## Operator Reference

### Comparison Operators

| C# expression | Generated SQL |
|---|---|
| `x.Name == "value"` | `"name" = $1` |
| `x.Name != "value"` | `"name" != $1` |
| `x.Count > 5` | `"count" > $1` |
| `x.Count >= 5` | `"count" >= $1` |
| `x.Count < 5` | `"count" < $1` |
| `x.Count <= 5` | `"count" <= $1` |

```csharp
// comparison examples
User.Query(x => x.Score >= 100)
User.Query(x => x.Age < 18)
User.Query(x => x.Name != "system")
```

### NULL Checks

| C# expression | Generated SQL |
|---|---|
| `x.Name == null` | `"name" IS NULL` |
| `x.Name != null` | `"name" IS NOT NULL` |

```csharp
// null checks
User.Query(x => x.DeletedAt == null)       // active (not soft-deleted)
User.Query(x => x.VerifiedAt != null)      // email verified
```

### Logical Operators

| C# expression | Generated SQL |
|---|---|
| `cond1 && cond2` | `... AND ...` |
| `cond1 \|\| cond2` | `... OR ...` |

Logical operators compose freely and nest to any depth.

```csharp
// AND
User.Query(x => x.Status == "active" && x.Score >= 10)

// OR
User.Query(x => x.Role == "admin" || x.Role == "moderator")

// nested AND / OR
User.Query(x => (x.Role == "admin" || x.Role == "moderator") && x.DeletedAt == null)
```

### String Methods

String matching translates to SQL `LIKE` patterns. The method names map directly to their wildcard positions.

| C# expression | Generated SQL |
|---|---|
| `x.Name.Contains("sub")` | `"name" LIKE '%sub%'` |
| `x.Name.StartsWith("pre")` | `"name" LIKE 'pre%'` |
| `x.Name.EndsWith("suf")` | `"name" LIKE '%suf'` |

```csharp
// string matching
User.Query(x => x.Email.EndsWith("@example.com"))
User.Query(x => x.Username.StartsWith("admin_"))
User.Query(x => x.Bio.Contains("developer"))
```

> **NOTE** `LIKE` is case-sensitive in PostgreSQL for non-`citext` columns. For case-insensitive matching, store data normalized, use a `citext` column, or use the `ToLower()` form below.

### HasFlag for FlaggedEnum Properties

`HasFlag` is supported on properties annotated for junction-table flag storage. It translates to an `EXISTS` subquery against the junction table.

| C# expression | Generated SQL |
|---|---|
| `x.Roles.HasFlag(Role.Admin)` | `EXISTS (SELECT 1 FROM junction WHERE owner_id = x.id AND role_id = 8)` |

```csharp
// check for a specific flag/role
User.Query(x => x.Roles.HasFlag(Role.Admin))
```

The junction table name and ID values come from your schema attributes. Multiple `HasFlag` calls combine with `&&` or `||`.

## Closed-Over Variables

Any local variable referenced inside a predicate lambda is captured as a SQL parameter, never embedded as a literal in the query string.

```csharp
// closed-over variable
int minScore = 5;
string status = "active";

List<User> users = await User.Query(x => x.Score > minScore && x.Status == status)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "score" > $1 AND "status" = $2
// Parameters: $1 = 5, $2 = 'active'
```

This applies equally to properties on objects, results of method calls, and any expression that does not reference the lambda parameter itself.

```csharp
// object property captured as parameter
var filter = new FilterOptions { MinScore = 50 };
User.Query(x => x.Score >= filter.MinScore)
```

## Combining conditions

The builder keeps a single WHERE predicate: a predicate passed to `Query()` and any later `.Where()` call do not stack; the last one wins. Combine conditions inside one lambda with `&&` / `||`.

```csharp
// combine in a single predicate
List<User> results = await User.Query(x => x.Status == "active" && x.Score > 50 && x.DeletedAt == null)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "status" = $1 AND "score" > $2 AND "deleted_at" IS NULL
```

## Extended Operators

The translator also handles these patterns.

| Pattern | Translates to |
|---------|---------------|
| `x => x.IsActive` / `x => !x.IsActive` | boolean column predicate |
| `x.Age.HasValue` / `!x.Age.HasValue` | `IS NOT NULL` / `IS NULL` |
| `x.Age.Value > 5` | column comparison |
| `x.Name.ToLower().Contains("ab")` | `"name" ILIKE @p ESCAPE '\'` (case-insensitive) |
| `x.Name.Equals("ab")` | `"name" = @p` |
| `string.IsNullOrEmpty(x.Name)` | `("name" IS NULL OR "name" = '')` |
| `x.Qty + 1 > 10` | arithmetic `+ - * / %` |
| `(x.Age ?? 0) > 5` | `COALESCE("age", @p) > @p` |

**LIKE escaping:** `Contains`, `StartsWith`, and `EndsWith` escape `%`, `_`, and `\` and append `ESCAPE '\'`. A value such as `"50%"` matches literally rather than as a wildcard.

## Limitations

The translator covers the operators above. These SQL features have no direct C# expression equivalent in the current version:

- **BETWEEN**: use `x.Score >= lo && x.Score <= hi`.
- **IN list**: use chained `||` comparisons (`x.Status == "a" || x.Status == "b"`), or a raw SQL procedure for large lists.
- **Sub-selects in WHERE**: use a JOIN or procedure mapping for correlated subqueries.

> **WARNING** Unsupported expressions (nested navigation `x.A.B`, subqueries, aggregates, or unknown methods) throw `NotSupportedException` at translation time rather than silently producing invalid SQL.

> **TIP** For predicates the operators above cannot express, use [Procedure Mapping](/database/0.3.2/advanced/procedure-mapping) with a raw `.sql` file.

## See Also

- [SELECT / Query](/database/0.3.2/querying/reading/select) for using `Query()` and chaining modifiers
- [Sorting and Pagination](/database/0.3.2/querying/reading/sorting-pagination) for ordering and paging patterns