Filtering Data

This guide covers filtering query results using WHERE clauses with the fluent query syntax.

Overview

Filtering restricts query results to rows that meet specific conditions using the WHERE clause. Velocity provides a type-safe filtering system using:

Filter - Container for filter conditions
Criterion<T> - Individual comparison operations
ConditionalType - Comparison operators (=, >, <, LIKE, IN, etc.)
BooleanItem - Logical connectors (AND, OR)

Simple Filters

Equality

Filter rows where a column equals a specific value:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

Table users = schema["users"];

Query query = new Query()
    .Select([
        users["username"],
        users["email"]
    ])
    .From(users)
    .Where(new Criterion<string>(
        users["status"],
        ConditionalType.Equals,
        "active"
    ));

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    // Only active users
    foreach (Result row in results)
    {
        Console.WriteLine(row.GetFieldString("username"));
    }
}

Generated SQL:

SELECT username, email
FROM users
WHERE status = 'active'

Not Equals

Query query = new Query()
    .Select(users["username"])
    .From(users)
    .Where(new Criterion<string>(
        users["status"],
        ConditionalType.NotEquals,
        "deleted"
    ));

Generated SQL:

SELECT username
FROM users
WHERE status != 'deleted'

Comparison Operators

Greater Than / Less Than

Table products = schema["products"];

// Products with price > 100
Query expensiveProducts = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .Where(new Criterion<decimal>(
        products["price"],
        ConditionalType.GreaterThan,
        100.00M
    ));

// Products with stock < 10
Query lowStockProducts = new Query()
    .Select([
        products["product_name"],
        products["stock_quantity"]
    ])
    .From(products)
    .Where(new Criterion<int>(
        products["stock_quantity"],
        ConditionalType.LessThan,
        10
    ));

Generated SQL:

-- Expensive products
SELECT product_name, price
FROM products
WHERE price > 100.00

-- Low stock products
SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity < 10

Greater Than or Equal / Less Than or Equal

Table employees = schema["employees"];

// Employees 18 or older
Query query = new Query()
    .Select([
        employees["employee_name"],
        employees["age"]
    ])
    .From(employees)
    .Where(new Criterion<int>(
        employees["age"],
        ConditionalType.GreaterThanOrEqualTo,
        18
    ));

// Scores 100 or less
Table scores = schema["test_scores"];

Query scoreQuery = new Query()
    .Select([
        scores["student_name"],
        scores["score"]
    ])
    .From(scores)
    .Where(new Criterion<int>(
        scores["score"],
        ConditionalType.LessThanOrEqualTo,
        100
    ));

Available Conditional Types

ConditionalType enumeration provides all comparison operators:

ConditionalType	SQL Operator	Example
`Between`	`BETWEEN`	`age BETWEEN 18 AND 65`
`Equals`	`=`	`age = 30`
`Exists`	`EXISTS`	`EXISTS (SELECT ...)`
`GreaterThan`	`>`	`price > 100`
`GreaterThanOrEqualTo`	`>=`	`age >= 18`
`In`	`IN`	`status IN ('active', 'pending')`
`IsNotNull`	`IS NOT NULL`	`email IS NOT NULL`
`IsNull`	`IS NULL`	`deleted_at IS NULL`
`LessThan`	`<`	`quantity < 10`
`LessThanOrEqualTo`	`<=`	`score <= 100`
`Like`	`LIKE`	`name LIKE '%Smith%'`
`NotEquals`	`!=` or `<>`	`status != 'deleted'`
`NotExists`	`NOT EXISTS`	`NOT EXISTS (SELECT ...)`
`NotIn`	`NOT IN`	`category NOT IN ('archived')`
`NotLike`	`NOT LIKE`	`email NOT LIKE '%spam%'`

Pattern Matching with LIKE

Basic Pattern Matching

Use % as a wildcard for any number of characters:

Table customers = schema["customers"];

// Names starting with 'John'
Query query = new Query()
    .Select(customers["customer_name"])
    .From(customers)
    .Where(new Criterion<string>(
        customers["customer_name"],
        ConditionalType.Like,
        "John%"
    ));

Generated SQL:

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'John%'

Common LIKE Patterns

// Contains 'smith' anywhere
.Where(new Criterion<string>(
    users["name"],
    ConditionalType.Like,
    "%smith%"
))

// Starts with 'A'
.Where(new Criterion<string>(
    products["product_name"],
    ConditionalType.Like,
    "A%"
))

// Ends with '.com'
.Where(new Criterion<string>(
    users["email"],
    ConditionalType.Like,
    "%.com"
))

// Exactly 5 characters (using _ wildcard)
.Where(new Criterion<string>(
    products["product_code"],
    ConditionalType.Like,
    "_____"
))

Case-Insensitive Matching

// Find emails from Gmail (case-insensitive)
Query query = new Query()
    .Select(users["email"])
    .From(users)
    .Where(new Criterion<string>(
        users["email"],
        ConditionalType.Like,
        "%@gmail.com"
    ));

NOT LIKE

// Exclude spam emails
Query query = new Query()
    .Select(users["email"])
    .From(users)
    .Where(new Criterion<string>(
        users["email"],
        ConditionalType.NotLike,
        "%spam%"
    ));

IN and NOT IN

IN with Multiple Values

Filter rows where a column matches any value in a list:

Table products = schema["products"];

// Products in specific categories
List<string> categories = new List<string> { "Electronics", "Books", "Toys" };

Query query = new Query()
    .Select([
        products["product_name"],
        products["category"]
    ])
    .From(products)
    .Where(new Criterion<string>(
        products["category"],
        ConditionalType.In,
        categories
    ));

Generated SQL:

SELECT product_name, category
FROM products
WHERE category IN ('Electronics', 'Books', 'Toys')

NOT IN

Exclude specific values:

List<string> excludedStatuses = new List<string> { "Discontinued", "Obsolete" };

Query query = new Query()
    .Select(products["product_name"])
    .From(products)
    .Where(new Criterion<string>(
        products["status"],
        ConditionalType.NotIn,
        excludedStatuses
    ));

Generated SQL:

SELECT product_name
FROM products
WHERE status NOT IN ('Discontinued', 'Obsolete')

IN with Numeric Values

List<int> customerIds = new List<int> { 1, 5, 10, 15 };

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["customer_id"]
    ])
    .From(orders)
    .Where(new Criterion<int>(
        orders["customer_id"],
        ConditionalType.In,
        customerIds
    ));

BETWEEN

Filter rows where a value falls within a range:

Numeric Range

Table products = schema["products"];

// Products priced between $10 and $50
List<decimal> priceRange = new List<decimal> { 10.00M, 50.00M };

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .Where(new Criterion<decimal>(
        products["price"],
        ConditionalType.Between,
        priceRange
    ));

Generated SQL:

SELECT product_name, price
FROM products
WHERE price BETWEEN 10.00 AND 50.00

Date Range

Table orders = schema["orders"];

// Orders from 2024
List<DateTime> dateRange = new List<DateTime>
{
    new DateTime(2024, 1, 1),
    new DateTime(2024, 12, 31, 23, 59, 59)
};

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["order_date"]
    ])
    .From(orders)
    .Where(new Criterion<DateTime>(
        orders["order_date"],
        ConditionalType.Between,
        dateRange
    ));

Generated SQL:

SELECT order_id, order_date
FROM orders
WHERE order_date BETWEEN '2024-01-01' AND '2024-12-31 23:59:59'

NULL Checks

IS NULL

Find rows where a column is NULL:

Table orders = schema["orders"];

// Orders not yet shipped
Query query = new Query()
    .Select([
        orders["order_id"],
        orders["order_date"]
    ])
    .From(orders)
    .Where(new Criterion<object>(
        orders["shipped_date"],
        ConditionalType.IsNull,
        null
    ));

Generated SQL:

SELECT order_id, order_date
FROM orders
WHERE shipped_date IS NULL

IS NOT NULL

Find rows where a column has a value:

Table users = schema["users"];

// Users with email addresses
Query query = new Query()
    .Select([
        users["username"],
        users["email"]
    ])
    .From(users)
    .Where(new Criterion<object>(
        users["email"],
        ConditionalType.IsNotNull,
        null
    ));

Generated SQL:

SELECT username, email
FROM users
WHERE email IS NOT NULL

Combining Multiple Conditions

AND Conditions

Combine conditions where all must be true:

using YndigoBlue.Velocity.Model;
using YndigoBlue.Velocity.Enums;

Table products = schema["products"];

// Products that are cheap AND in stock
Filter filter = new Filter([
    new Criterion<decimal>(products["price"], ConditionalType.LessThan, 100.00M),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(products["stock_quantity"], ConditionalType.GreaterThan, 0)
]);

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"],
        products["stock_quantity"]
    ])
    .From(products)
    .Where(filter);

Generated SQL:

SELECT product_name, price, stock_quantity
FROM products
WHERE price < 100.00 AND stock_quantity > 0

OR Conditions

Combine conditions where any can be true:

Table orders = schema["orders"];

// Orders that are pending OR processing
Filter filter = new Filter([
    new Criterion<string>(orders["status"], ConditionalType.Equals, "pending"),
    new BooleanItem(BooleanType.Or),
    new Criterion<string>(orders["status"], ConditionalType.Equals, "processing")
]);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["status"]
    ])
    .From(orders)
    .Where(filter);

Generated SQL:

SELECT order_id, status
FROM orders
WHERE status = 'pending' OR status = 'processing'

Multiple AND Conditions

Table users = schema["users"];

// Active users over 18 with verified email
Filter filter = new Filter([
    new Criterion<bool>(users["is_active"], ConditionalType.Equals, true),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(users["age"], ConditionalType.GreaterThanOrEqualTo, 18),
    new BooleanItem(BooleanType.And),
    new Criterion<bool>(users["email_verified"], ConditionalType.Equals, true)
]);

Query query = new Query()
    .Select(users["username"])
    .From(users)
    .Where(filter);

Generated SQL:

SELECT username
FROM users
WHERE is_active = true AND age >= 18 AND email_verified = true

Complex Nested Filters

Parenthesized Groups

Create complex logic with nested filters:

Table products = schema["products"];

// (price < 50) AND ((category = 'Electronics') OR (on_sale = true))
Filter innerFilter = new Filter([
    new Criterion<string>(products["category"], ConditionalType.Equals, "Electronics"),
    new BooleanItem(BooleanType.Or),
    new Criterion<bool>(products["on_sale"], ConditionalType.Equals, true)
]);

Filter outerFilter = new Filter([
    new Criterion<decimal>(products["price"], ConditionalType.LessThan, 50.00M),
    new BooleanItem(BooleanType.And),
    innerFilter
]);

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"]
    ])
    .From(products)
    .Where(outerFilter);

Generated SQL:

SELECT product_name, price
FROM products
WHERE price < 50 AND (category = 'Electronics' OR on_sale = true)

Multi-Level Nesting

Table orders = schema["orders"];

// ((status = 'pending') OR (status = 'processing')) AND
// ((total > 100) OR (priority = 'high'))

Filter statusFilter = new Filter([
    new Criterion<string>(orders["status"], ConditionalType.Equals, "pending"),
    new BooleanItem(BooleanType.Or),
    new Criterion<string>(orders["status"], ConditionalType.Equals, "processing")
]);

Filter valueFilter = new Filter([
    new Criterion<decimal>(orders["total"], ConditionalType.GreaterThan, 100.00M),
    new BooleanItem(BooleanType.Or),
    new Criterion<string>(orders["priority"], ConditionalType.Equals, "high")
]);

Filter mainFilter = new Filter([
    statusFilter,
    new BooleanItem(BooleanType.And),
    valueFilter
]);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["status"],
        orders["total"]
    ])
    .From(orders)
    .Where(mainFilter);

Generated SQL:

SELECT order_id, status, total
FROM orders
WHERE (status = 'pending' OR status = 'processing')
  AND (total > 100 OR priority = 'high')

Complex Business Logic

Table users = schema["users"];

// (role = 'admin') OR
// ((role = 'user') AND (account_age > 365) AND (posts > 50))

Filter advancedUserFilter = new Filter([
    new Criterion<string>(users["role"], ConditionalType.Equals, "user"),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(users["account_age_days"], ConditionalType.GreaterThan, 365),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(users["post_count"], ConditionalType.GreaterThan, 50)
]);

Filter mainFilter = new Filter([
    new Criterion<string>(users["role"], ConditionalType.Equals, "admin"),
    new BooleanItem(BooleanType.Or),
    advancedUserFilter
]);

Query query = new Query()
    .Select([
        users["username"],
        users["role"]
    ])
    .From(users)
    .Where(mainFilter);

Complete Examples

Example 1: Product Search

Table products = schema["products"];

// Find affordable electronics in stock
Filter filter = new Filter([
    new Criterion<string>(products["category"], ConditionalType.Equals, "Electronics"),
    new BooleanItem(BooleanType.And),
    new Criterion<decimal>(products["price"], ConditionalType.LessThanOrEqualTo, 500.00M),
    new BooleanItem(BooleanType.And),
    new Criterion<int>(products["stock_quantity"], ConditionalType.GreaterThan, 0)
]);

Query query = new Query()
    .Select([
        products["product_name"],
        products["price"],
        products["stock_quantity"]
    ])
    .From(products)
    .Where(filter);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine("Affordable Electronics in Stock:");
    foreach (Result row in results)
    {
        string name = row.GetFieldString("product_name");
        decimal price = row.GetFieldDecimal("price");
        int stock = row.GetFieldInteger("stock_quantity");

        Console.WriteLine($"{name} - ${price:F2} ({stock} available)");
    }
}

Example 2: User Activity Report

Table users = schema["users"];

// Active users who logged in within last 30 days or have recent posts
Filter recentLoginFilter = new Filter(
    new Criterion<DateTime>(
        users["last_login"],
        ConditionalType.GreaterThan,
        DateTime.Now.AddDays(-30)
    )
);

Filter recentPostsFilter = new Filter(
    new Criterion<int>(
        users["recent_posts"],
        ConditionalType.GreaterThan,
        0
    )
);

Filter activityFilter = new Filter([
    recentLoginFilter,
    new BooleanItem(BooleanType.Or),
    recentPostsFilter
]);

Filter mainFilter = new Filter([
    new Criterion<bool>(users["is_active"], ConditionalType.Equals, true),
    new BooleanItem(BooleanType.And),
    activityFilter
]);

Query query = new Query()
    .Select([
        users["username"],
        users["last_login"],
        users["recent_posts"]
    ])
    .From(users)
    .Where(mainFilter);

using (Manager manager = new Manager(connection))
using (ResultSet results = manager.Retrieve(query))
{
    Console.WriteLine($"Active Users: {results.Count}");
}

Example 3: Order Filtering

Table orders = schema["orders"];

// High-value or urgent orders
List<decimal> highValueRange = new List<decimal> { 1000.00M, decimal.MaxValue };

Filter highValueFilter = new Filter(
    new Criterion<decimal>(
        orders["total"],
        ConditionalType.GreaterThanOrEqualTo,
        1000.00M
    )
);

Filter urgentFilter = new Filter(
    new Criterion<string>(
        orders["priority"],
        ConditionalType.Equals,
        "urgent"
    )
);

Filter unshippedFilter = new Filter(
    new Criterion<object>(
        orders["shipped_date"],
        ConditionalType.IsNull,
        null
    )
);

Filter mainFilter = new Filter([
    highValueFilter,
    new BooleanItem(BooleanType.Or),
    urgentFilter,
    new BooleanItem(BooleanType.And),
    unshippedFilter
]);

Query query = new Query()
    .Select([
        orders["order_id"],
        orders["total"],
        orders["priority"],
        orders["order_date"]
    ])
    .From(orders)
    .Where(mainFilter);

Best Practices

Use typed Criterion
- Always use Criterion<T> with the appropriate type
- Prevents type conversion errors at runtime
Build complex filters incrementally
- Create nested filters as separate variables
- Makes code more readable and maintainable
Filter in the database
- Use WHERE clauses instead of filtering in memory
- Leverages database indexes and optimization
Be careful with OR conditions
- OR conditions can prevent index usage
- Consider using IN for multiple equality checks
Test NULL handling
- Always use IsNull / IsNotNull for NULL comparisons
- Never use = NULL or != NULL
Use BETWEEN for ranges
- More readable than separate >= and <= conditions
- Better optimization in some databases

Next Steps

Sorting Results - Order filtered results
Expressions - Use calculated values in filters
Grouping and Aggregation - Filter aggregated data with HAVING
Subqueries - Use subqueries in WHERE clauses

Table of Contents

Filtering Data

Overview

Simple Filters

Equality

Not Equals

Comparison Operators

Greater Than / Less Than

Greater Than or Equal / Less Than or Equal

Available Conditional Types

Pattern Matching with LIKE

Basic Pattern Matching

Common LIKE Patterns

Case-Insensitive Matching

NOT LIKE

IN and NOT IN

IN with Multiple Values

NOT IN

IN with Numeric Values

BETWEEN

Numeric Range

Date Range

NULL Checks

IS NULL

IS NOT NULL

Combining Multiple Conditions

AND Conditions

OR Conditions

Multiple AND Conditions

Complex Nested Filters

Parenthesized Groups

Multi-Level Nesting

Complex Business Logic

Complete Examples

Example 1: Product Search

Example 2: User Activity Report

Example 3: Order Filtering

Best Practices

Next Steps