Lambda Expressions

Write type-safe queries using C# lambda expressions. This is the recommended approach for building DynamoDB expressions, offering compile-time type checking, IntelliSense support, and refactoring safety.

Key Benefits

• Type Safety: Catch property name typos at compile time instead of runtime
• IntelliSense Support: Get autocomplete suggestions for properties and methods
• Refactoring Safety: Rename properties with confidence - expressions update automatically
• Cleaner Code: No manual parameter naming or attribute mapping required
• AOT Compatible: Works seamlessly in Native AOT environments

Query Expressions

Use Where<T>() to specify key conditions for Query operations. Key conditions define which items to retrieve based on partition and sort keys.

Basic Query by Partition Key

// Query all items for a specific user
var users = await table.Query<User>()
    .Where(x => x.UserId == "user123")
    .ToListAsync();

Query with Sort Key Conditions

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

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

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

Complex Query Pattern

var customerId = "CUSTOMER#123";
var thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);

var orders = await table.Query<Order>()
    .Where(x => 
        x.CustomerId == customerId && 
        x.OrderDate.Between(thirtyDaysAgo, DateTime.UtcNow))
    .ToListAsync();

Filter Expressions

Use WithFilter<T>() to apply additional filtering after items are retrieved. Filters can use any attribute, not just key attributes.

Basic Filter

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

Complex Filters with Multiple Conditions

// AND conditions
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.Status == "ACTIVE" && x.Age >= 18)
    .ToListAsync();

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

// Combined logical operators with parentheses
var users = await table.Query<User>()
    .Where(x => x.PartitionKey == userId)
    .WithFilter(x => (x.Active && x.Score > 50) || x.Premium)
    .ToListAsync();

Condition Expressions

Use WithCondition<T>() for conditional writes (Put, Update, Delete operations).

Conditional Put (Prevent Overwrites)

var newUser = new User 
{ 
    Id = "USER#123", 
    Name = "John Doe" 
};

await table.Put(newUser)
    .WithCondition<User>(x => x.Id.AttributeNotExists())
    .PutAsync();

Conditional Update (Optimistic Locking)

await table.Update<User>(userId)
    .Set(x => new { Name = "Jane Doe" })
    .WithCondition(x => x.Version == currentVersion)
    .UpdateAsync();

Conditional Delete

await table.Delete<Order>(orderId)
    .WithCondition(x => x.Status == "cancelled")
    .DeleteAsync();

Supported Operators

Comparison Operators

All standard comparison operators are supported:

// Equality
table.Query.Where<User>(x => x.Id == userId);
// Translates to: #attr0 = :p0

// Inequality
table.Query.WithFilter<User>(x => x.Status != "DELETED");
// Translates to: #attr0 <> :p0

// Less than
table.Query.WithFilter<User>(x => x.Age < 65);
// Translates to: #attr0 < :p0

// Greater than
table.Query.WithFilter<User>(x => x.Score > 100);
// Translates to: #attr0 > :p0

// Less than or equal
table.Query.WithFilter<User>(x => x.Age <= 18);
// Translates to: #attr0 <= :p0

// Greater than or equal
table.Query.WithFilter<User>(x => x.Score >= 50);
// Translates to: #attr0 >= :p0

Logical Operators

Combine conditions with logical operators:

// AND
table.Query.Where<User>(x => x.PartitionKey == userId && x.SortKey == sortKey);
// Translates to: (#attr0 = :p0) AND (#attr1 = :p1)

// OR
table.Query.WithFilter<User>(x => x.Type == "A" || x.Type == "B");
// Translates to: (#attr0 = :p0) OR (#attr0 = :p1)

// NOT
table.Query.WithFilter<User>(x => !x.Deleted);
// Translates to: NOT (#attr0)

// Complex combinations with parentheses
table.Query.WithFilter<User>(x => 
    (x.Active && x.Score > 50) || x.Premium);
// Translates to: ((#attr0) AND (#attr1 > :p0)) OR (#attr2)

DynamoDB Functions

StartsWith (begins_with)

Use string.StartsWith() for prefix matching on sort keys:

table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.OrderId.StartsWith("ORDER#"))
    .ToListAsync();
// Translates to: #attr0 = :p0 AND begins_with(#attr1, :p1)

Contains

Use string.Contains() for substring matching:

table.Query<User>()
    .WithFilter(x => x.Email.Contains("@example.com"))
    .ToListAsync();
// Translates to: contains(#attr0, :p0)

Between

Use the Between() extension method for range queries:

table.Query<User>()
    .Where(x => x.PartitionKey == userId && x.SortKey.Between("2024-01", "2024-12"))
    .ToListAsync();
// Translates to: #attr0 = :p0 AND #attr1 BETWEEN :p1 AND :p2

AttributeExists

Check if an attribute exists:

table.Query<User>()
    .WithFilter(x => x.PhoneNumber.AttributeExists())
    .ToListAsync();
// Translates to: attribute_exists(#attr0)

AttributeNotExists

Check if an attribute does not exist:

// Note: Scan requires [Scannable] attribute on the entity
table.Users.Scan()
    .WithFilter(x => x.DeletedAt.AttributeNotExists())
    .ToListAsync();
// Translates to: attribute_not_exists(#attr0)

Size

Get the size of a collection or string attribute:

table.Query<User>()
    .WithFilter(x => x.Items.Size() > 5)
    .ToListAsync();
// Translates to: size(#attr0) > :p0

String Comparison Operators

C# doesn't support <, >, <=, >= operators on strings directly. Use CompareTo() for string range comparisons in DynamoDB expressions:

String Greater Than or Equal

// Sort key >= "2024-01-01"
table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.SortKey.CompareTo("2024-01-01") >= 0)
    .ToListAsync();
// Translates to: #pk = :p0 AND #sk >= :p1

String Less Than

// Sort key < "2024-12-31"
table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.SortKey.CompareTo("2024-12-31") < 0)
    .ToListAsync();
// Translates to: #pk = :p0 AND #sk < :p1

String Range Query

// Range query on sort key
table.Query<Order>()
    .Where(x => x.CustomerId == customerId 
        && x.SortKey.CompareTo("2024-01-01") >= 0 
        && x.SortKey.CompareTo("2024-12-31") <= 0)
    .ToListAsync();
// Translates to: #pk = :p0 AND #sk >= :p1 AND #sk <= :p2

CompareTo Pattern Reference

Pattern	DynamoDB	Notes
.CompareTo(val) > 0	attr > val	Greater than
.CompareTo(val) >= 0	attr >= val	Greater than or equal
.CompareTo(val) < 0	attr < val	Less than
.CompareTo(val) <= 0	attr <= val	Less than or equal
.CompareTo(val) == 0	attr = val	Equal (prefer == directly)
.CompareTo(val) != 0	attr <> val	Not equal (prefer != directly)

tip

For inclusive ranges, prefer using Between() which is more readable:

.Where(x => x.Pk == pk && x.Sk.Between("2024-01-01", "2024-12-31"))

Conditional Filter Patterns

In addition to ternary expressions, you can use natural || and && operators with local boolean conditions to conditionally include or skip filter clauses. This provides a more intuitive syntax for optional filters.

Pattern Overview

Pattern	Local Value	Behavior
localCondition || x.Prop == val	true	Skip filter (return all)
localCondition || x.Prop == val	false	Apply filter
localCondition && x.Prop == val	true	Apply filter
localCondition && x.Prop == val	false	Skip filter (return all)

OR Pattern - Skip Filter When Condition is True

Use || when you want to skip a filter when a local condition is true:

var status = "ACTIVE";

// When status is null/empty, skip the filter entirely
// When status has a value, apply the filter
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => string.IsNullOrWhiteSpace(status) || x.Status == status)
    .ToListAsync();

// If status is empty: No filter applied (returns all orders for customer)
// If status is "ACTIVE": Filter: #status = :p0

AND Pattern - Include Filter When Condition is True

Use && when you want to include a filter only when a local condition is true:

var enableDateFilter = true;
var minDate = DateTime.UtcNow.AddDays(-30);

// Only apply date filter when enableDateFilter is true
var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => enableDateFilter && x.OrderDate > minDate)
    .ToListAsync();

// If enableDateFilter is false: No filter applied
// If enableDateFilter is true: Filter: #orderDate > :p0

Multiple Optional Filters

Combine multiple conditional filters in a single expression:

var skipStatusFilter = false;
var skipDateFilter = true;
var status = "SHIPPED";
var minDate = DateTime.UtcNow.AddDays(-7);

var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => 
        (skipStatusFilter || x.Status == status) && 
        (skipDateFilter || x.OrderDate > minDate))
    .ToListAsync();

// skipStatusFilter=false, skipDateFilter=true:
// Filter: #status = :p0
// (Only status filter applied, date filter skipped)

Negated Conditions

Use negation for more readable expressions:

var excludeArchived = true;

var orders = await table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => !excludeArchived || x.Archived == false)
    .ToListAsync();

// If excludeArchived is true: Filter: #archived = :p0 (false)
// If excludeArchived is false: No filter applied

Method Calls in Local Conditions

Local conditions can include method calls that don't reference the entity:

var searchTerm = "   ";  // Whitespace only

var products = await table.Query<Product>()
    .Where(x => x.CategoryId == categoryId)
    .WithFilter(x => string.IsNullOrWhiteSpace(searchTerm) || x.Name.Contains(searchTerm.Trim()))
    .ToListAsync();

// string.IsNullOrWhiteSpace("   ") returns true
// Filter is skipped entirely

Rules for Local Conditions

1. Local condition must not reference the entity parameter

   // ✗ Invalid: Condition references entity property
   x => x.IsActive || x.Status == "PENDING"
   
   // ✓ Valid: Condition uses captured variable
   var skipFilter = true;
   x => skipFilter || x.Status == "PENDING"

2. Local conditions are evaluated at translation time

   • The condition is evaluated once when the expression is translated
   • The result determines whether the filter is included or omitted
   • No runtime evaluation occurs in DynamoDB

3. Comparison: Ternary vs OR/AND Patterns

   Both approaches achieve the same result. Choose based on readability:

   // Ternary pattern
   x => hasFilter ? x.Status == status : true
   
   // OR pattern - more natural for "skip when true"
   x => !hasFilter || x.Status == status
   
   // AND pattern - more natural for "include when true"
   x => hasFilter && x.Status == status

Empty Expression Handling

When all conditional clauses evaluate to skip (e.g., all local conditions are true in OR patterns), the filter is gracefully omitted and the operation executes without filtering. This eliminates the need to wrap .WithFilter() in conditional checks.

// Safe to use even when all conditions might skip
var orders = await table.Orders.Query(x => x.CustomerId == customerId)
    .WithFilter(x => 
        (string.IsNullOrWhiteSpace(status) || x.Status == status) &&
        (string.IsNullOrWhiteSpace(category) || x.Category == category))
    .ToListAsync();
// If both status and category are null/empty, query executes without filter

Value Capture

Lambda expressions can capture values from the surrounding scope in several ways.

Constants

Direct constant values are automatically captured:

// String constant
table.Query.Where<User>(x => x.Id == "USER#123");

// Numeric constant
table.Query.WithFilter<User>(x => x.Age >= 18);

// Boolean constant
table.Query.WithFilter<User>(x => x.Active == true);

// Enum constant
table.Query.WithFilter<Order>(x => x.Status == OrderStatus.Pending);

Local Variables

Variables from the surrounding scope are captured:

var userId = "USER#123";
var minAge = 18;
var status = "ACTIVE";

table.Query<User>()
    .Where(x => x.PartitionKey == userId)
    .WithFilter(x => x.Age >= minAge && x.Status == status)
    .ToListAsync();

Closure Captures

Properties from captured objects are evaluated:

var user = GetCurrentUser();
var config = GetConfiguration();

table.Query<Order>()
    .Where(x => x.CustomerId == user.Id)
    .WithFilter(x => x.Total > config.MinOrderAmount)
    .ToListAsync();

Method Calls on Captured Values

You can call methods on captured values (but not on entity properties):

// ✓ Valid: Method call on captured value
var userId = GetUserId();
table.Query<User>()
    .Where(x => x.PartitionKey == userId.ToString())
    .ToListAsync();

// ✓ Valid: Complex expression on captured value
var date = DateTime.Now;
table.Query<Order>()
    .WithFilter(x => x.CreatedDate > date.AddDays(-30))
    .ToListAsync();

// ✗ Invalid: Method call on entity property
table.Query<User>()
    .WithFilter(x => x.Name.ToUpper() == "JOHN") // Error!
    .ToListAsync();

Where() vs WithFilter()

Understanding the difference between Where() and WithFilter() is crucial for efficient queries.

Where() - Key Condition Expression

Use Where() for partition key and sort key conditions. These are applied before reading items from DynamoDB:

// Efficient: Only reads matching items
table.Query<User>()
    .Where(x => x.PartitionKey == userId && x.SortKey.StartsWith("ORDER#"))
    .ToListAsync();

Characteristics:

• Only partition key and sort key properties allowed
• Reduces consumed read capacity
• Most efficient way to query

WithFilter() - Filter Expression

Use WithFilter() for non-key attributes. These are applied after reading items:

// Less efficient: Reads all items for userId, then filters
table.Query<User>()
    .Where(x => x.PartitionKey == userId)
    .WithFilter(x => x.Status == "ACTIVE")
    .ToListAsync();

Characteristics:

• Any property allowed
• Applied after items are read
• Reduces data transfer but not read capacity
• Still more efficient than filtering in application code

Common Mistake

A frequent error is using non-key attributes in Where():

// ✗ Error: Non-key property in Where()
table.Query<User>()
    .Where(x => x.PartitionKey == userId && x.Status == "ACTIVE")
    .ToListAsync();
// Throws: InvalidKeyExpressionException
// "Property 'Status' is not a key attribute and cannot be used in Query().Where(). 
//  Use WithFilter() instead."

// ✓ Correct: Move non-key condition to WithFilter()
table.Query<User>()
    .Where(x => x.PartitionKey == userId)
    .WithFilter(x => x.Status == "ACTIVE")
    .ToListAsync();

Best Practice

Always use key conditions for primary filtering, then apply filters for additional criteria:

// ✅ Good - use key condition for primary filtering
table.Query<Order>()
    .Where(x => x.CustomerId == customerId && x.OrderId > "ORDER#2024-01-01")
    .WithFilter(x => x.Status == "pending")
    .ToListAsync();

// ❌ Avoid - using filter for what should be a key condition
table.Query<Order>()
    .Where(x => x.CustomerId == customerId)
    .WithFilter(x => x.OrderId > "ORDER#2024-01-01")  // Should be in key condition
    .ToListAsync();

Valid vs Invalid Patterns

✓ Valid Patterns

// Property access
x => x.PropertyName

// Constant values
x => x.Id == "USER#123"

// Local variables
x => x.Id == userId

// Closure captures
x => x.Id == user.Id

// Method calls on captured values
x => x.Id == userId.ToString()
x => x.CreatedDate > date.AddDays(-30)

// Complex conditions
x => (x.Active && x.Score > 50) || x.Premium

// DynamoDB functions
x => x.Name.StartsWith("John")
x => x.Age.Between(18, 65)
x => x.Email.AttributeExists()
x => x.Items.Size() > 0

✗ Invalid Patterns

// Assignment (use == for comparison)
x => x.Id = "123" // ✗ Error
x => x.Id == "123" // ✓ Correct

// Method calls on entity properties
x => x.Name.ToUpper() == "JOHN" // ✗ Error
var upperName = "JOHN";
x => x.Name == upperName // ✓ Correct

// Methods referencing entity parameter
x => x.Id == MyFunction(x) // ✗ Error
var computedId = MyFunction(someValue);
x => x.Id == computedId // ✓ Correct

// LINQ operations on entity properties
x => x.Items.Select(i => i.Name).Contains("test") // ✗ Error
x => x.Items.Contains("test") // ✓ Correct (if Items is a collection)

// Unsupported operators
x => x.Age % 2 == 0 // ✗ Error (modulo not supported)
// Filter in application code after retrieval instead

Troubleshooting

InvalidKeyExpressionException

Error:

Property 'Status' is not a key attribute and cannot be used in Query().Where(). 
Use WithFilter() instead.

Solution: Move non-key properties to WithFilter():

// ✗ Wrong
table.Query<User>().Where(x => x.PartitionKey == userId && x.Status == "ACTIVE");

// ✓ Correct
table.Query<User>()
    .Where(x => x.PartitionKey == userId)
    .WithFilter(x => x.Status == "ACTIVE");

UnsupportedExpressionException

Error:

Method 'ToUpper' cannot be used on entity properties in DynamoDB expressions.

Solution: Transform values before the query:

// ✗ Wrong
table.Query<User>().WithFilter(x => x.Name.ToUpper() == "JOHN");

// ✓ Correct
var upperName = "JOHN";
table.Query<User>().WithFilter(x => x.Name == upperName);

Complete Examples

E-commerce Order Query

var customerId = "CUSTOMER#123";
var thirtyDaysAgo = DateTime.UtcNow.AddDays(-30);
var minAmount = 50.00m;

var orders = await table.Query<Order>()
    .Where(x => 
        x.CustomerId == customerId && 
        x.OrderDate.Between(thirtyDaysAgo, DateTime.UtcNow))
    .WithFilter(x => 
        x.Status == "SHIPPED" && 
        x.Total >= minAmount && 
        x.Items.Size() > 0)
    .ToListAsync();

User Search with Multiple Criteria

var region = "US-WEST";
var minAge = 18;

var users = await table.Query<User>()
    .OnIndex("RegionIndex")
    .Where(x => x.Region == region)
    .WithFilter(x => 
        x.Active && 
        x.Age >= minAge && 
        x.Email.AttributeExists() && 
        x.EmailVerified)
    .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))
    .ToListAsync();

Next Steps

• Formatted Expressions - Learn the format string syntax alternative
• String Expressions - Manual expression building for explicit control
• Filters - Deep dive into filter expressions
• Updates - Update expressions using lambda syntax