Skip to main content

Formatted Expressions

Build DynamoDB expressions using string.Format-style syntax. This approach provides a concise middle ground between lambda expressions and manual parameter binding.

Key Benefits​

  • Less Boilerplate: No need to manually create parameter names
  • Inline Formatting: Format values directly in the expression
  • Readability: Expression and values are co-located
  • Flexibility: Works well for dynamic query building

Basic Usage​

Expression formatting uses placeholders like {0}, {1} in your expressions. The library automatically:

  1. Generates unique parameter names
  2. Formats values according to the specifier
  3. Adds parameters to the request
  4. Replaces placeholders with parameter names

Simple Value Substitution​

// Single parameter
await table.Query<User>()
    .Where("pk = {0}", "USER#123")
    .ToListAsync();

// Multiple parameters
await table.Query<Order>()
    .Where("pk = {0} AND sk > {1}", "CUSTOMER#123", "ORDER#2024-01-01")
    .ToListAsync();

Using with String Interpolation​

When using C# string interpolation, use double braces to escape the placeholders:

// With string interpolation (double braces)
await table.Query<User>()
    .Where($"pk = {{0}} AND status = {{1}}", userId, "active")
    .ToListAsync();

// Without string interpolation (single braces)
await table.Query<User>()
    .Where("pk = {0} AND status = {1}", userId, "active")
    .ToListAsync();

Automatic Parameter Generation​

The library automatically generates unique parameter names for each placeholder:

// Your code
await table.Query<User>()
    .Where("pk = {0} AND created > {1}", "USER#123", DateTime.UtcNow)
    .ToListAsync();

// Generated DynamoDB expression:
// KeyConditionExpression: "pk = :p0 AND created > :p1"
// ExpressionAttributeValues: { ":p0": "USER#123", ":p1": "2024-01-15T10:30:00Z" }

Format Specifiers​

Add format specifiers after a colon to control value formatting:

// DateTime with ISO 8601 format
.Where("created > {0:o}", DateTime.UtcNow.AddDays(-30))

// Decimal with 2 decimal places
.Where("price > {0:F2}", 19.99m)

// Integer with zero-padding
.Set("SET sequence = {0:D10}", 42)

DateTime Formatting​

DateTime formatting is crucial for sortable date comparisons in DynamoDB.

// Round-trip format (most precise)
await table.Query<User>()
    .Where("pk = {0} AND timestamp > {1:o}", userId, DateTime.UtcNow.AddHours(-1))
    .ToListAsync();
// Result: "2024-01-15T10:30:00.0000000Z"

// Sortable format (no fractional seconds)
await table.Query<User>()
    .Where("pk = {0} AND timestamp > {1:s}", userId, DateTime.UtcNow.AddHours(-1))
    .ToListAsync();
// Result: "2024-01-15T10:30:00"

// Universal sortable
await table.Query<User>()
    .Where("pk = {0} AND timestamp > {1:u}", userId, DateTime.UtcNow.AddHours(-1))
    .ToListAsync();
// Result: "2024-01-15 10:30:00Z"

Custom Date Formats​

// Date only (for partitioning by day)
await table.Update<Event>()
    .WithKey("pk", "event123")
    .Set("SET dateKey = {0:yyyy-MM-dd}", DateTime.UtcNow)
    .UpdateAsync();
// Result: "2024-01-15"

// Year-month (for partitioning by month)
await table.Update<Event>()
    .WithKey("pk", "event123")
    .Set("SET monthKey = {0:yyyy-MM}", DateTime.UtcNow)
    .UpdateAsync();
// Result: "2024-01"

Date Range Queries​

var startDate = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc);
var endDate = new DateTime(2024, 12, 31, 23, 59, 59, DateTimeKind.Utc);

await table.Query<Order>()
    .Where("pk = {0} AND created BETWEEN {1:o} AND {2:o}", 
           "CUSTOMER#123",
           startDate,
           endDate)
    .ToListAsync();

DateTime Best Practices​

// ✅ Good - ISO 8601 for sortable comparisons
.Where("created > {0:o}", DateTime.UtcNow)

// ✅ Good - custom format for display/partitioning
.Set("SET displayDate = {0:yyyy-MM-dd}", DateTime.UtcNow)

// ❌ Avoid - locale-dependent format (not sortable)
.Where("created > {0:d}", DateTime.UtcNow)  // "1/15/2024"

// ❌ Avoid - ambiguous format
.Where("created > {0:MM/dd/yyyy}", DateTime.UtcNow)

Numeric Formatting​

Decimal Precision​

// Fixed-point with 2 decimal places (for prices)
await table.Update<Product>()
    .WithKey("pk", "prod123")
    .Set("SET price = {0:F2}", 19.99m)
    .UpdateAsync();
// Result: "19.99"

// Fixed-point with 4 decimal places (for precise measurements)
await table.Update<Sensor>()
    .WithKey("pk", "sensor123")
    .Set("SET reading = {0:F4}", 98.7654m)
    .UpdateAsync();
// Result: "98.7654"

// No decimal places
await table.Update<Product>()
    .WithKey("pk", "prod123")
    .Set("SET quantity = {0:F0}", 42.7)
    .UpdateAsync();
// Result: "43" (rounded)

Zero-Padding for Sortable Numbers​

DynamoDB sorts strings lexicographically. Without padding, "10" comes before "2". With padding, "0002" correctly comes before "0010".

// Pad to 10 digits (for sequence numbers)
await table.Update<Order>()
    .WithKey("pk", "order123")
    .Set("SET sequenceKey = {0:D10}", 42)
    .UpdateAsync();
// Result: "0000000042"

// Pad to 5 digits
await table.Update<Invoice>()
    .WithKey("pk", "inv123")
    .Set("SET invoiceNumber = {0:D5}", 123)
    .UpdateAsync();
// Result: "00123"

Number Formatting with Separators​

// Thousands separator
await table.Update<Account>()
    .WithKey("pk", "acct123")
    .Set("SET balanceDisplay = {0:N0}", 1234567)
    .UpdateAsync();
// Result: "1,234,567"

// With decimal places
await table.Update<Account>()
    .WithKey("pk", "acct123")
    .Set("SET balanceDisplay = {0:N2}", 1234567.89m)
    .UpdateAsync();
// Result: "1,234,567.89"

Enum Handling​

Enums are converted to their string representation:

public enum OrderStatus
{
    Pending,
    Processing,
    Shipped,
    Delivered,
    Cancelled
}

// Enum to string
await table.Query<Order>()
    .Where("pk = {0} AND status = {1}", customerId, OrderStatus.Shipped)
    .ToListAsync();
// Result: status = "Shipped"

// Multiple enum values
await table.Query<Order>()
    .Where("pk = {0}", customerId)
    .WithFilter("status IN ({0}, {1}, {2})", 
                OrderStatus.Processing,
                OrderStatus.Shipped,
                OrderStatus.Delivered)
    .ToListAsync();

To use numeric values, cast to int first:

// Cast to int for numeric value
.Where("statusCode = {0}", (int)OrderStatus.Shipped)
// Result: statusCode = 2

Boolean Values​

Booleans are converted to lowercase strings:

// Boolean to string
await table.Query<User>()
    .Where("pk = {0}", userId)
    .WithFilter("isActive = {0}", true)
    .ToListAsync();
// Result: isActive = "true"

await table.Update<User>()
    .WithKey("pk", "user123")
    .Set("SET isVerified = {0}, isActive = {1}", true, false)
    .UpdateAsync();
// Results: "true", "false"

Reserved Word Handling​

DynamoDB has many reserved words that cannot be used directly in expressions. Combine expression formatting with WithAttributeName() to handle these cases.

Using WithAttributeName()​

// "status" is a reserved word in DynamoDB
await table.Query<User>()
    .Where("pk = {0} AND #status = {1}", userId, "active")
    .WithAttributeName("#status", "status")
    .ToListAsync();

// Multiple reserved words
await table.Query<User>()
    .Where("#status = {0} AND #name = {1} AND #data = {2}", 
           "active", "John", "metadata")
    .WithAttributeName("#status", "status")
    .WithAttributeName("#name", "name")
    .WithAttributeName("#data", "data")
    .ToListAsync();

Combining with DateTime Formatting​

await table.Query<User>()
    .Where("#status = {0} AND created > {1:o}", 
           "active",
           DateTime.UtcNow.AddDays(-30))
    .WithAttributeName("#status", "status")
    .ToListAsync();

Common Reserved Words​

The following are commonly used attribute names that are reserved in DynamoDB:

Reserved WordUse Case
statusItem state tracking
nameUser/entity names
dataGeneric data fields
timestampTime tracking
dateDate fields
year, month, dayDate components
valueGeneric values
sizeSize attributes
typeType discriminators
orderOrdering fields
commentComment fields
groupGrouping fields
userUser references

See DynamoDB Reserved Words for the complete list.

Complex Expressions​

Multiple Parameters with Different Types​

// Mix of string, DateTime, and numeric parameters
await table.Query<Order>()
    .Where("pk = {0} AND created BETWEEN {1:o} AND {2:o}", 
           "CUSTOMER#123",
           startDate,
           endDate)
    .WithFilter("total > {0:F2}", 100.00m)
    .ToListAsync();

Filter Expressions​

// Complex filter with multiple conditions
await table.Query<Order>()
    .Where("pk = {0}", "CUSTOMER#123")
    .WithFilter("(#status = {0} OR #status = {1}) AND " +
                "total BETWEEN {2:F2} AND {3:F2} AND " +
                "created > {4:o}", 
                "pending",
                "processing",
                50.00m,
                500.00m,
                DateTime.UtcNow.AddDays(-30))
    .WithAttributeName("#status", "status")
    .ToListAsync();

DynamoDB Functions​

// begins_with function
await table.Query<Order>()
    .Where("pk = {0} AND begins_with(sk, {1})", 
           "CUSTOMER#123",
           "ORDER#2024")
    .ToListAsync();

// contains function
await table.Query<Product>()
    .Where("pk = {0}", "prod123")
    .WithFilter("contains(tags, {0})", "premium")
    .ToListAsync();

// attribute_exists function
await table.Put(user)
    .Where("attribute_not_exists(pk)")
    .PutAsync();

// size function
await table.Query<Order>()
    .Where("pk = {0}", "CUSTOMER#123")
    .WithFilter("size(items) > {0}", 5)
    .ToListAsync();

Update Expressions​

Expression formatting works in update expressions too:

SET Operations​

// Simple SET
await table.Update<User>()
    .WithKey("pk", "user123")
    .Set("SET #name = {0}, updatedAt = {1:o}", 
         "Jane Doe",
         DateTime.UtcNow)
    .WithAttributeName("#name", "name")
    .UpdateAsync();

// SET with if_not_exists
await table.Update<User>()
    .WithKey("pk", "user123")
    .Set("SET #name = if_not_exists(#name, {0})", "Default Name")
    .WithAttributeName("#name", "name")
    .UpdateAsync();

ADD Operations​

// Increment counter
await table.Update<User>()
    .WithKey("pk", "user123")
    .Set("ADD loginCount {0}", 1)
    .UpdateAsync();

Combined Operations​

// SET, ADD, and REMOVE in one expression
await table.Update<User>()
    .WithKey("pk", "user123")
    .Set("SET #name = {0}, updatedAt = {1:o} " +
         "ADD loginCount {2} " +
         "REMOVE tempData",
         "Jane Doe",
         DateTime.UtcNow,
         1)
    .WithAttributeName("#name", "name")
    .UpdateAsync();

Comparison with Other Approaches​

// Type-safe with IntelliSense
await table.Query<User>()
    .Where(x => x.UserId == userId && x.CreatedAt > thirtyDaysAgo)
    .ToListAsync();

Format Strings (This Page)​

// Concise with inline formatting
await table.Query<User>()
    .Where("pk = {0} AND created > {1:o}", userId, thirtyDaysAgo)
    .ToListAsync();

Manual Parameters​

// Explicit control over parameter names
await table.Query<User>()
    .Where("pk = :pk AND created > :date")
    .WithValue(":pk", userId)
    .WithValue(":date", thirtyDaysAgo)
    .ToListAsync();

Best Practices​

1. Use ISO 8601 for Dates​

// ✅ Recommended
.Where("created > {0:o}", DateTime.UtcNow)

// ❌ Avoid
.Where("created > {0:d}", DateTime.UtcNow)

2. Specify Decimal Precision for Money​

// ✅ Recommended
.Set("SET total = {0:F2}", 19.99m)

// ❌ Avoid
.Set("SET total = {0}", 19.99m)

3. Use Zero-Padding for Sortable Numbers​

// ✅ Recommended
.Set("SET sequenceKey = {0:D10}", sequenceNumber)

// ❌ Avoid
.Set("SET sequenceKey = {0}", sequenceNumber)

4. Handle Reserved Words​

// ✅ Recommended
.Where("#status = {0}", "active")
.WithAttributeName("#status", "status")

// ❌ Avoid (may fail if "status" is reserved)
.Where("status = {0}", "active")

5. Keep Expressions Readable​

// ✅ Recommended - split long expressions
.Where("pk = {0} AND " +
       "created BETWEEN {1:o} AND {2:o} AND " +
       "total > {3:F2}", 
       customerId, startDate, endDate, minTotal)

// ❌ Avoid - hard to read
.Where("pk = {0} AND created BETWEEN {1:o} AND {2:o} AND total > {3:F2}", customerId, startDate, endDate, minTotal)

Next Steps​