Orders by status, through an index

Runs the sample. Generated for this page; it reads the connection string from the environment and first remakes the sample database from sample-schema.json.

using ArxSamples.DynamoDb;
using ArxSamples.DynamoDb.Operations.RetrieveMultiple.RealCases;

// The connection string comes from the environment, never from the code (see "Configure the connection").
var connectionString = Environment.GetEnvironmentVariable("ARX_SAMPLES_DYNAMODB")
    ?? throw new InvalidOperationException("Set the ARX_SAMPLES_DYNAMODB environment variable to your Amazon DynamoDB connection string.");

var dbContext = new SampleContext(connectionString);

// Every run starts from the sample data: this deletes and makes again what sample-schema.json lists
// (save the file in the project folder, see Get started).
await dbContext.Direct.ResetAsync();

await new RealCase2_OrdersByStatus().RunAsync(dbContext);

The sample, as it is in the repository.

using ARX.SDK.DynamoDb.Services;
using ARX.SDK.Query;
using ARX.SDK.Services;

using static ArxSamples.DynamoDb.SampleOutput;

namespace ArxSamples.DynamoDb.Operations.RetrieveMultiple.RealCases;

/// <summary>
/// The operations screen: every open order, across customers, through the GSI1 index, in three scenarios: by status, by
/// status and a date range, and the count of open orders.
/// </summary>
/// <remarks>
/// The model gives orders a second key, <c>GSI1</c>: <c>STATUS#{status}</c> and the order date. A query whose Equal
/// conditions give the index's partition (the status) reads the index; ARX picks it, or <c>IndexName</c> names it. An
/// index is eventually consistent: a write shows there a moment later. A Count aggregate is allowed; totals of pages aren't.
/// </remarks>
public sealed class RealCase2_OrdersByStatus : ISample
{
    public string Name => "retrieve-multiple-real-2-orders-by-status";

    public string Title => "Orders by status, through an index";

    public async Task RunAsync(SampleContext dbContext)
    {
        var orders = new List<ArxEntity>();

        foreach (var (customerId, number, status, on) in new[]
        {
            ("C-D441", "SO-D441", "SHIPPED", "2026-09-01"), ("C-D441", "SO-D442", "SHIPPED", "2026-09-15"),
            ("C-D442", "SO-D443", "SHIPPED", "2026-10-01"), ("C-D443", "SO-D444", "SHIPPED", "2026-10-20"),
            ("C-D443", "SO-D445", "CANCELLED", "2026-10-21")
        })
        {
            var order = new ArxEntity("sales_order");
            order["customer_id"] = customerId;
            order["order_number"] = number;
            order["status"] = status;
            order["ordered_on"] = on;
            orders.Add(order);
        }

        await dbContext.Service.CreateMultipleAsync(orders);

        await Scenario1_ByStatusAsync(dbContext.Service);

        await Scenario2_ByStatusAndDateAsync(dbContext.Service);

        await Scenario3_HowManyAsync(dbContext.Service);
    }

    private static ArxQueryExpression Shipped()
    {
        var query = new ArxQueryExpression("sales_order");
        query.ColumnSet.AddColumns("order_number", "customer_id", "ordered_on");
        query.Criteria.AddCondition("status", ArxConditionOperator.Equal, "SHIPPED");
        return query;
    }

    /// <summary>1. Every shipped order, oldest first (the index's sort key is the date).</summary>
    private static async Task Scenario1_ByStatusAsync(ArxDynamoDbService service)
    {
        Step("1. By status");

        var query = Shipped();
        query.AddOrder("ordered_on", ArxOrderType.Ascending);

        var found = await service.RetrieveMultipleAsync(query, new ArxDynamoReadOptions { IndexName = "GSI1", ConsistentRead = false });

        Show(string.Join(", ", found.Entities.Select(x => $"{x["order_number"]} {x["ordered_on"]}")));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number", "ordered_on" FROM "crm"."GSI1" WHERE "GSI1PK" = 'STATUS#SHIPPED'
         * -- Expect: 4 items, SO-D441 to SO-D444 (among any other shipped orders).
         * ===========================================================
         */
    }

    /// <summary>2. Shipped in October: the date range is the index's sort key range.</summary>
    private static async Task Scenario2_ByStatusAndDateAsync(ArxDynamoDbService service)
    {
        Step("2. By status and date");

        var query = Shipped();
        query.Criteria.AddCondition("ordered_on", ArxConditionOperator.Between, "2026-10-01", "2026-10-31");

        var found = await service.RetrieveMultipleAsync(query, new ArxDynamoReadOptions { ConsistentRead = false });

        Show(string.Join(", ", found.Entities.Select(x => x["order_number"])));

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number" FROM "crm"."GSI1" WHERE "GSI1PK" = 'STATUS#SHIPPED' AND "GSI1SK" BETWEEN '2026-10-01' AND '2026-10-31'
         * -- Expect: SO-D443 and SO-D444.
         * ===========================================================
         */
    }

    /// <summary>3. How many orders are shipped: a Count aggregate, counted by DynamoDB (Select COUNT).</summary>
    private static async Task Scenario3_HowManyAsync(ArxDynamoDbService service)
    {
        Step("3. How many");

        var query = new ArxQueryExpression("sales_order");
        query.AggregateInfo = new ArxAggregateInfo();
        query.AggregateInfo.AddAggregateColumn("shipped", "", ArxAggregateOperator.Count);
        query.Criteria.AddCondition("status", ArxConditionOperator.Equal, "SHIPPED");

        var found = await service.RetrieveMultipleAsync(query, new ArxDynamoReadOptions { ConsistentRead = false });

        Show($"shipped orders: {found.Entities[0]["shipped"]}");

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number" FROM "crm"."GSI1" WHERE "GSI1PK" = 'STATUS#SHIPPED'
         * -- Expect: as many items as the count.
         * ===========================================================
         */
    }
}

  > 1. By status
      SO-D441 2026-09-01, SO-D442 2026-09-15, SO-D443 2026-10-01, SO-D444 2026-10-20
  > 2. By status and date
      SO-D443, SO-D444
  > 3. How many
      shipped orders: 4