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

Nested Property Filters

Filter on properties within nested objects (maps) using dot notation in lambda expressions.

Single-Level Nested Property

// Filter by nested property
var customers = await table.Query<Customer>()
    .Where(x => x.TenantId == tenantId)
    .WithFilter(x => x.ShippingAddress.City == "Seattle")
    .ToListAsync();

// Generated filter expression: #address.#city = :v0

Multi-Level Nested Property

// Filter by deeply nested property
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.ShippingAddress.Country.Code == "US")
    .ToListAsync();

// Generated: #shippingAddress.#country.#code = :v0

Nested Property with Comparison Operators

// Greater than on nested property
var highScoreItems = await table.Query<Item>()
    .Where(x => x.Category == category)
    .WithFilter(x => x.Metrics.Score > 90)
    .ToListAsync();

// String prefix on nested property
var westCoastCustomers = await table.Query<Customer>()
    .Where(x => x.TenantId == tenantId)
    .WithFilter(x => x.ShippingAddress.ZipCode.StartsWith("98"))
    .ToListAsync();

Nested Property with Logical Operators

// AND with nested properties
var seattleWaCustomers = await table.Query<Customer>()
    .Where(x => x.TenantId == tenantId)
    .WithFilter(x => x.ShippingAddress.City == "Seattle" && x.ShippingAddress.State == "WA")
    .ToListAsync();

// OR with nested properties
var pacificNwCustomers = await table.Query<Customer>()
    .Where(x => x.TenantId == tenantId)
    .WithFilter(x => x.ShippingAddress.City == "Seattle" || x.ShippingAddress.City == "Portland")
    .ToListAsync();

Nested Properties in Key Conditions

Nested property access is NOT supported in key condition expressions (Where()). DynamoDB key conditions only support partition key and sort key attributes. Use nested properties only in filter expressions (WithFilter()).

List Index Filters

Filter on specific elements within list attributes using index notation.

Filter by List Element Index

// Filter by first element in list
var featuredItems = await table.Query<Item>()
    .Where(x => x.Category == "electronics")
    .WithFilter(x => x.Tags[0] == "featured")
    .ToListAsync();

// Generated: #tags[0] = :v0

Nested List Access

// Access list inside nested object
var saleItems = await table.Query<Product>()
    .Where(x => x.Category == category)
    .WithFilter(x => x.Metadata.Keywords[0] == "sale")
    .ToListAsync();

// Generated: #metadata.#keywords[0] = :v0

Object Property in List

// Access property of object at list index
var ordersWithProduct = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.LineItems[0].ProductId == productId)
    .ToListAsync();

// Generated: #lineItems[0].#productId = :v0

List Index Limitation

List index must be a constant integer value. Variable indices are not supported in DynamoDB expressions.

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

Use builder.Response to access response metadata after ToListAsync():

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

var items = await query.ToListAsync();

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

Or use ToDynamoDbResponseAsync() for direct SDK response access:

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