Skip to main content

DynamoDB Multi-Entity Queries in C#

Query multiple related entity types in a single DynamoDB request using FluentDynamoDB's ToCompoundEntityAsync() method. This approach is essential for single-table design, where related entities (like orders with line items) share the same partition key and can be retrieved together efficiently.

Why Use Compound Entities?​

Single-table design stores multiple entity types in one table, differentiated by sort key patterns:

PK: CUSTOMER#123       SK: ORDER#001           → Order header
PK: CUSTOMER#123       SK: ORDER#001#LINE#001  → Line item 1
PK: CUSTOMER#123       SK: ORDER#001#LINE#002  → Line item 2
PK: CUSTOMER#123       SK: ORDER#001#PAYMENT   → Payment info

Benefits of ToCompoundEntityAsync():

  • Single query: Retrieve parent and all related entities in one DynamoDB request
  • Automatic mapping: Related entities are automatically populated based on [RelatedEntity] patterns
  • Type safety: Full IntelliSense support for parent and child entities
  • Reduced latency: No multiple round trips to fetch related data
// Retrieve order with all line items, payment, and shipment
var order = await ecommerceTable.Query()
    .Where<Order>(x => x.CustomerId == "customer123" 
        && x.OrderId.StartsWith("ORDER#order456"))
    .ToCompoundEntityAsync();

// Access populated related entities
Console.WriteLine($"Order: {order.OrderId}");
Console.WriteLine($"Lines: {order.Lines?.Count ?? 0}");
Console.WriteLine($"Payment: {order.Payment?.Method ?? "None"}");

foreach (var line in order.Lines ?? Enumerable.Empty<OrderLine>())
{
    Console.WriteLine($"  - {line.ProductName}: {line.Quantity} x ${line.Price}");
}

Query Invoice with Items and Status History​

// Retrieve invoice with all items and status history
var invoice = await invoiceTable.Query()
    .Where<Invoice>(x => x.InvoiceId == "INVOICE#invoice123")
    .ToCompoundEntityAsync();

// Access nested collections
foreach (var item in invoice.Items ?? Enumerable.Empty<InvoiceItem>())
{
    Console.WriteLine($"Item: {item.Description} - ${item.UnitPrice * item.Quantity}");
}

foreach (var status in invoice.StatusHistory ?? Enumerable.Empty<InvoiceStatus>())
{
    Console.WriteLine($"Status: {status.Status} at {status.Timestamp}");
}

Query with Sort Key Range​

// Get all orders for a customer with their related entities
var orders = await ecommerceTable.Query()
    .Where<Order>(x => x.CustomerId == "customer123" 
        && x.OrderId.StartsWith("ORDER#"))
    .ToListAsync();

// Get a specific order with all related data
var completeOrder = await ecommerceTable.Query()
    .Where<Order>(x => x.CustomerId == "customer123" 
        && x.OrderId.Between("ORDER#order456", "ORDER#order456#~"))
    .ToCompoundEntityAsync();

Entity Definition​

The examples above use these entity definitions with [RelatedEntity] attributes:

// Parent entity with related entity declarations
[DynamoDbTable("ecommerce", IsDefault = true)]
public partial class Order
{
    [PartitionKey]
    [DynamoDbAttribute("pk")]
    public string CustomerId { get; set; } = string.Empty;
    
    [SortKey]
    [DynamoDbAttribute("sk")]
    [Computed("ORDER#{OrderId}")]
    public string OrderId { get; set; } = string.Empty;
    
    [DynamoDbAttribute("total")]
    public decimal Total { get; set; }
    
    [DynamoDbAttribute("status")]
    public string Status { get; set; } = "pending";
    
    // Related entities - automatically populated by ToCompoundEntityAsync()
    [RelatedEntity("ORDER#{OrderId}#LINE#*")]
    public List<OrderLine>? Lines { get; set; }
    
    [RelatedEntity("ORDER#{OrderId}#PAYMENT")]
    public Payment? Payment { get; set; }
}

// Child entity - shares same table
[DynamoDbTable("ecommerce")]
public partial class OrderLine
{
    [PartitionKey]
    [DynamoDbAttribute("pk")]
    public string CustomerId { get; set; } = string.Empty;
    
    [SortKey]
    [DynamoDbAttribute("sk")]
    [Computed("ORDER#{OrderId}#LINE#{LineNumber:D3}")]
    public string OrderId { get; set; } = string.Empty;
    
    public int LineNumber { get; set; }
    
    [DynamoDbAttribute("productName")]
    public string ProductName { get; set; } = string.Empty;
    
    [DynamoDbAttribute("quantity")]
    public int Quantity { get; set; }
    
    [DynamoDbAttribute("price")]
    public decimal Price { get; set; }
}

Key Concepts​

ConceptDescription
[RelatedEntity("PATTERN")]Declares a relationship based on sort key pattern matching
ToCompoundEntityAsync()Retrieves parent entity with all related entities populated
"PREFIX#*"Wildcard pattern matches all sort keys starting with prefix
"EXACT"Exact pattern matches a single sort key value
IsDefault = trueMarks the primary entity for table-level operations

Learn More​