Filter Expressions

Filter expressions apply additional filtering to query and scan results after items are retrieved from DynamoDB. They reduce data transfer to your application but do not reduce consumed read capacity.

Understanding Filter Behavior

Filter expressions are applied after DynamoDB reads items from the table:

1. DynamoDB reads items matching the key condition (for Query) or all items (for Scan)
2. Filter expressions are applied to the retrieved items
3. Only matching items are returned to your application

Important: You still pay for the read capacity of all items examined, even those filtered out. Design your key schema to minimize filtering when possible.

Basic Filters

Single Condition Filter

// Lambda expression (recommended)
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending")
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"{OrderFields.Status} = {{0}}", "pending")
    .ToListAsync();

// Manual parameters
var orders = await table.Query<Order>()
    .Where("#pk = :pk")
    .WithAttribute("#pk", "pk")
    .WithValue(":pk", OrderKeys.Pk("customer123"))
    .WithFilter("#status = :status")
    .WithAttribute("#status", "status")
    .WithValue(":status", "pending")
    .ToListAsync();

Numeric Comparison Filter

// Filter orders with total greater than $100
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Total > 100.00m)
    .ToListAsync();

// Filter users by age range
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => x.Age >= 18 && x.Age <= 65)
    .ToListAsync();

Complex Filters with Multiple Conditions

AND Conditions

All conditions must be true:

// Lambda expression
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending" && x.Total > 100.00m)
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"{OrderFields.Status} = {{0}} AND {OrderFields.Total} > {{1}}", 
                "pending", 
                100.00m)
    .ToListAsync();

OR Conditions

At least one condition must be true:

// Lambda expression
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending" || x.Status == "processing")
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"{OrderFields.Status} = {{0}} OR {OrderFields.Status} = {{1}}", 
                "pending", 
                "processing")
    .ToListAsync();

Combined Logical Operators

Use parentheses to group conditions:

// Complex filter with AND and OR
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => 
        (x.Active && x.Score > 50) || x.Premium)
    .ToListAsync();

// Multiple grouped conditions
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => 
        (x.Status == "pending" || x.Status == "processing") && 
        x.Total >= 50.00m)
    .ToListAsync();

NOT Conditions

Negate a condition:

// Filter out deleted items
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => !x.Deleted)
    .ToListAsync();

// Filter items not in a specific status
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status != "cancelled")
    .ToListAsync();

Filter Functions

DynamoDB provides several built-in functions for filter expressions.

attribute_exists

Check if an attribute exists on an item:

// Lambda expression
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Discount.AttributeExists())
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"attribute_exists({OrderFields.Discount})")
    .ToListAsync();

attribute_not_exists

Check if an attribute does not exist:

// Lambda expression - find items without a cancellation reason
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.CancellationReason.AttributeNotExists())
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"attribute_not_exists({OrderFields.CancellationReason})")
    .ToListAsync();

attribute_type

Check the data type of an attribute:

// Filter by attribute type
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"attribute_type({OrderFields.Metadata}, {{0}})", "M")  // Map
    .ToListAsync();

// Common type codes:
// "S" - String
// "N" - Number
// "B" - Binary
// "BOOL" - Boolean
// "NULL" - Null
// "L" - List
// "M" - Map
// "SS" - String Set
// "NS" - Number Set
// "BS" - Binary Set

begins_with

Check if a string attribute starts with a specific prefix:

// Lambda expression
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => x.Email.StartsWith("admin@"))
    .ToListAsync();

// Format string
var users = await table.Query<User>()
    .Where($"{UserFields.PartitionKey} = {{0}}", UserKeys.Pk("user123"))
    .WithFilter($"begins_with({UserFields.Email}, {{0}})", "admin@")
    .ToListAsync();

contains

Check if a string contains a substring or if a set/list contains a value:

// Lambda expression - string contains
var products = await table.Query<Product>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => x.Description.Contains("premium"))
    .ToListAsync();

// Lambda expression - list/set contains
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Tags.Contains("priority"))
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"contains({OrderFields.Tags}, {{0}})", "priority")
    .ToListAsync();

size

Get the size of a string, list, map, or set attribute:

// Lambda expression - filter by list size
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Items.Size() > 5)
    .ToListAsync();

// Lambda expression - filter by string length range
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => x.Name.Size().Between(3, 50))
    .ToListAsync();

// Format string
var orders = await table.Query<Order>()
    .Where($"{OrderFields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .WithFilter($"size({OrderFields.Items}) > {{0}}", 5)
    .ToListAsync();

// Format string - size with BETWEEN
var users = await table.Query<User>()
    .Where($"{UserFields.PartitionKey} = {{0}}", UserKeys.Pk("user123"))
    .WithFilter($"size({UserFields.Name}) BETWEEN {{0}} AND {{1}}", 3, 50)
    .ToListAsync();

Capacity Consumption and Best Practices

Understanding Capacity Impact

Filter expressions reduce the amount of data transferred to your application, but they do not reduce the read capacity consumed:

// This query reads ALL items for the customer, then filters
var response = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending")
    .ReturnTotalConsumedCapacity()
    .ToDynamoDbResponseAsync();

// Example output:
// Items returned: 10
// Items scanned: 500
// Capacity consumed: 125 RCUs (based on all 500 items)

Best Practices

1. Prefer Key Conditions Over Filters

// ✅ Good - uses sort key condition (efficient)
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.OrderDate > startDate)
    .WithFilter(x => x.Status == "pending")
    .ToListAsync();

// ❌ Avoid - uses filter for what could be a key condition
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.OrderDate > startDate)  // Should be in key condition
    .ToListAsync();

2. Design Keys for Common Query Patterns

If you frequently filter by a specific attribute, consider creating a Global Secondary Index (GSI) with that attribute as the partition or sort key.

3. Use Filters for Infrequent Conditions

Filters are appropriate for conditions that:

• Apply to a small subset of items
• Vary frequently between queries
• Cannot be efficiently indexed

4. Monitor Consumed Capacity

var response = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending")
    .ReturnTotalConsumedCapacity()
    .ToDynamoDbResponseAsync();

Console.WriteLine($"Items returned: {response.Items.Count}");
Console.WriteLine($"Items scanned: {response.ScannedCount}");
Console.WriteLine($"Capacity consumed: {response.ConsumedCapacity?.CapacityUnits} RCUs");

5. Combine Multiple Filters Efficiently

// ✅ Good - single filter with multiple conditions
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => 
        x.Status == "pending" && 
        x.Total > 100.00m && 
        x.Items.Size() > 0)
    .ToListAsync();

Complete Examples

E-commerce Order Filtering

var customerId = "CUSTOMER#123";
var minAmount = 50.00m;

var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => 
        x.Status == "SHIPPED" && 
        x.Total >= minAmount && 
        x.Items.Size() > 0 &&
        x.TrackingNumber.AttributeExists())
    .ToListAsync();

User Search with Multiple Criteria

var partitionKey = "REGION#US-WEST";
var minAge = 18;

var users = await table.Query<User>()
    .Where(x => x.PartitionKey == partitionKey)
    .WithFilter(x => 
        x.Active && 
        x.Age >= minAge && 
        x.Email.AttributeExists() && 
        x.EmailVerified &&
        !x.Suspended)
    .ToListAsync();

Scan with Complex Filter

Scan Requires Opt-In

Scan operations read every item in a table and are expensive in terms of read capacity. They are not recommended as a primary access pattern. To use Scan, you must explicitly add the [Scannable] attribute to your entity.

// Entity must have [Scannable] attribute to enable Scan operations
[DynamoDbTable("Users")]
[Scannable]  // Required for Scan operations
public partial class User
{
    [PartitionKey]
    [DynamoDbAttribute("pk")]
    public string Pk { get; set; } = string.Empty;
    
    // ... other properties
}

var minScore = 1000;

// Scan is available because User entity has [Scannable] attribute
var users = await table.Users.Scan()
    .WithFilter(x => 
        x.Premium || 
        (x.Score >= minScore && x.Active && x.Email.Contains("@company.com")))
    .ToListAsync();

Next Steps

• Lambda Expressions - Type-safe query building (recommended)
• Formatted Expressions - Format string syntax
• Updates - Update expressions using lambda syntax
• Request Builders - Overview of all three API styles