# WHERE Operators

Complete reference for predicate operators supported by the Socigy.OpenSource.DB v0.2.0 WHERE clause builder.

## Overview

Predicates are expressed as C# lambda expressions and are translated to parameterized SQL at build time. You can supply a predicate directly to `Query()`, pass it to `.Where()`, or chain both together. All values are captured as SQL parameters — 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
// C# — 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
// C# — 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 can be nested to any depth.

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

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

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

### String Methods

String matching is translated to SQL `LIKE` patterns. The method names map directly to their LIKE 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
// C# — 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. If you need case-insensitive matching, store your data normalized or use a case-insensitive column type.

### HasFlag — FlaggedEnum Properties

`HasFlag` is supported on properties annotated for junction-table flag storage. It translates to an `EXISTS` subquery that checks 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
// C# — check for a specific flag/role
User.Query(x => x.Roles.HasFlag(Role.Admin))
```

The exact junction table name and ID values are determined by your schema attributes. Multiple `HasFlag` calls can be combined with `&&` or `||`.

## Closed-Over Variables

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

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

var 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 other expression that does not reference the lambda parameter itself.

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

## Chainable WHERE Calls

You can pass a predicate to `Query()` and then add further conditions with `.Where()`. All conditions are combined with `AND`.

```csharp
// C# — stacked conditions
var results = await User.Query(x => x.Status == "active")
    .Where(x => x.Score > 50)
    .Where(x => x.DeletedAt == null)
    .WithConnection(conn)
    .ExecuteAsync()
    .ToListAsync();
// SQL: WHERE "status" = $1 AND "score" > $2 AND "deleted_at" IS NULL
```

## Limitations

The predicate translator covers the operators listed above. The following SQL features do not have direct C# expression equivalents in the current version:

- **BETWEEN** — use `x.Score >= lo && x.Score <= hi` instead.
- **IN list** — use chained `||` comparisons (`x.Status == "a" || x.Status == "b"`) or use a raw SQL procedure for large lists.
- **ILIKE (case-insensitive LIKE)** — normalize data or use a `citext` column type.
- **Sub-selects in WHERE** — use JOIN or procedure mapping for correlated subqueries.

> **TIP** For complex predicates that cannot be expressed with the above operators, consider [Procedure Mapping](/database/0.2.0/advanced/procedure-mapping) with a raw `.sql` file.

## See Also

- [SELECT / Query](select) — how to use `Query()` and chain modifiers
- [Sorting and Pagination](sorting-pagination) — ordering and paging patterns

## New in v0.2.0

The WHERE translator gained several operators and now **fails fast** on anything it cannot translate.

| 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` / `EndsWith` now escape `%`, `_` and `\` and append `ESCAPE '\'`, so a value such as `"50%"` matches literally rather than as a wildcard.

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