DynamoDB Query and Scan in C#

Query and scan DynamoDB tables efficiently using FluentDynamoDB. This guide demonstrates partition key queries, sort key conditions, and filter expressions across all three API patterns.

Lambda/Fluent

Query by Partition Key

// Get all orders for a customer
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == "customer123")
    .ToListAsync();

Sort Key Conditions

// Orders after a specific date
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.OrderId > "ORDER#2024-01-01")
    .ToListAsync();

// Orders within a date range
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId && 
                x.OrderId.Between("ORDER#2024-01", "ORDER#2024-12"))
    .ToListAsync();

// Orders with a prefix
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.OrderId.StartsWith("ORDER#2024-03"))
    .ToListAsync();

Filter Expressions

// Filter by status
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "pending")
    .ToListAsync();

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

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

Query on GSI

// Query orders by status using a GSI
var orders = await table.Query<Order>()
    .UsingIndex("StatusIndex")
    .Where(x => x.Status == "pending")
    .ToListAsync();

Scan with Filter

// Scan requires [Scannable] attribute on entity
var users = await table.Users.Scan()
    .WithFilter(x => x.Premium || x.Score >= 1000)
    .ToListAsync();

String Formatted

Query by Partition Key

// Get all orders for a customer
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}}", OrderKeys.Pk("customer123"))
    .ToListAsync();

Sort Key Conditions

// Orders after a specific date
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}} AND {Order.Fields.OrderId} > {{1}}", 
           OrderKeys.Pk(customerId), "ORDER#2024-01-01")
    .ToListAsync();

// Orders within a date range
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}} AND {Order.Fields.OrderId} BETWEEN {{1}} AND {{2}}", 
           OrderKeys.Pk(customerId), "ORDER#2024-01", "ORDER#2024-12")
    .ToListAsync();

// Orders with a prefix
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}} AND begins_with({Order.Fields.OrderId}, {{1}})", 
           OrderKeys.Pk(customerId), "ORDER#2024-03")
    .ToListAsync();

Filter Expressions

// Filter by status
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}}", OrderKeys.Pk(customerId))
    .WithFilter($"{Order.Fields.Status} = {{0}}", "pending")
    .ToListAsync();

// Multiple filter conditions
var orders = await table.Query<Order>()
    .Where($"{Order.Fields.CustomerId} = {{0}}", OrderKeys.Pk(customerId))
    .WithFilter($"{Order.Fields.Status} = {{0}} AND {Order.Fields.Total} > {{1:F2}}", 
                "pending", 100.00m)
    .ToListAsync();

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

Query on GSI

// Query orders by status using a GSI
var orders = await table.Query<Order>()
    .UsingIndex("StatusIndex")
    .Where($"{Order.Fields.Status} = {{0}}", "pending")
    .ToListAsync();

Scan with Filter

// Scan requires [Scannable] attribute on entity
var users = await table.Users.Scan()
    .WithFilter($"{User.Fields.Premium} OR {User.Fields.Score} >= {{0}}", 1000)
    .ToListAsync();

Manual Builder

Query by Partition Key

// Get all orders for a customer
var orders = await table.Query<Order>()
    .Where("#pk = :pk")
    .WithAttribute("#pk", "pk")
    .WithValue(":pk", OrderKeys.Pk("customer123"))
    .ToListAsync();

Sort Key Conditions

// Orders after a specific date
var orders = await table.Query<Order>()
    .Where("#pk = :pk AND #sk > :sk")
    .WithAttribute("#pk", "pk")
    .WithAttribute("#sk", "sk")
    .WithValue(":pk", OrderKeys.Pk(customerId))
    .WithValue(":sk", "ORDER#2024-01-01")
    .ToListAsync();

// Orders within a date range
var orders = await table.Query<Order>()
    .Where("#pk = :pk AND #sk BETWEEN :start AND :end")
    .WithAttribute("#pk", "pk")
    .WithAttribute("#sk", "sk")
    .WithValue(":pk", OrderKeys.Pk(customerId))
    .WithValue(":start", "ORDER#2024-01")
    .WithValue(":end", "ORDER#2024-12")
    .ToListAsync();

// Orders with a prefix
var orders = await table.Query<Order>()
    .Where("#pk = :pk AND begins_with(#sk, :prefix)")
    .WithAttribute("#pk", "pk")
    .WithAttribute("#sk", "sk")
    .WithValue(":pk", OrderKeys.Pk(customerId))
    .WithValue(":prefix", "ORDER#2024-03")
    .ToListAsync();

Filter Expressions

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

// Multiple filter conditions
var orders = await table.Query<Order>()
    .Where("#pk = :pk")
    .WithAttribute("#pk", "pk")
    .WithValue(":pk", OrderKeys.Pk(customerId))
    .WithFilter("#status = :status AND #total > :total")
    .WithAttribute("#status", "status")
    .WithAttribute("#total", "total")
    .WithValue(":status", "pending")
    .WithValue(":total", 100.00m)
    .ToListAsync();

// OR conditions
var orders = await table.Query<Order>()
    .Where("#pk = :pk")
    .WithAttribute("#pk", "pk")
    .WithValue(":pk", OrderKeys.Pk(customerId))
    .WithFilter("#status = :s1 OR #status = :s2")
    .WithAttribute("#status", "status")
    .WithValue(":s1", "pending")
    .WithValue(":s2", "processing")
    .ToListAsync();

Query on GSI

// Query orders by status using a GSI
var orders = await table.Query<Order>()
    .UsingIndex("StatusIndex")
    .Where("#status = :status")
    .WithAttribute("#status", "status")
    .WithValue(":status", "pending")
    .ToListAsync();

Scan with Filter

// Scan requires [Scannable] attribute on entity
var users = await table.Users.Scan()
    .WithFilter("#premium OR #score >= :minScore")
    .WithAttribute("#premium", "premium")
    .WithAttribute("#score", "score")
    .WithValue(":minScore", 1000)
    .ToListAsync();

Entity Definition

All examples above use this entity definition:

[DynamoDbTable("orders")]
public partial class Order
{
    [PartitionKey]
    [DynamoDbAttribute("pk")]
    public string CustomerId { get; set; } = string.Empty;
    
    [SortKey]
    [DynamoDbAttribute("sk")]
    public string OrderId { get; set; } = string.Empty;
    
    [GsiPartitionKey("StatusIndex")]
    [DynamoDbAttribute("status")]
    public string Status { get; set; } = "pending";
    
    [DynamoDbAttribute("total")]
    public decimal Total { get; set; }
}

Key Concepts

Concept	Description
Where()	Key condition for partition key and sort key (efficient, reduces read capacity)
WithFilter()	Filter expression applied after items are read (reduces data transfer, not read capacity)
UsingIndex()	Query on a Global Secondary Index (GSI)
Scan()	Read all items in a table (requires [Scannable] attribute, use sparingly)

Learn More

• Lambda Expressions Guide - Complete type-safe query reference
• Filter Expressions - Deep dive into filter patterns
• Request Builders - Comparison of all three API styles
• Basic Operations - CRUD operations guide