Retrieve many records

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.Basics;

// 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 Basic1_RetrieveManyRecords().RunAsync(dbContext);

The sample, as it is in the repository.

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

using static ArxSamples.DynamoDb.SampleOutput;

namespace ArxSamples.DynamoDb.Operations.RetrieveMultiple.Basics;

/// <summary>
/// Read many items with <see cref="ArxQueryExpression"/>, in three scenarios: a customer's orders, newest first and
/// filtered, a range of the sort key, and a query DynamoDB can't answer without a scan.
/// </summary>
/// <remarks>
/// A query needs the partition: an Equal condition on the attributes of the partition key (here the customer). Conditions
/// on the sort key's attribute narrow the range; the others filter what was read. Orders go by the sort key only. Without
/// a partition, ARX refuses (ARX8001) unless you allow a scan (Advanced 1).
/// </remarks>
public sealed class Basic1_RetrieveManyRecords : ISample
{
    public string Name => "retrieve-multiple-basics-1-retrieve-many-records";

    public string Title => "Retrieve many records";

    public async Task RunAsync(SampleContext dbContext)
    {
        var orders = Enumerable.Range(1, 6).Select(i =>
        {
            var order = new ArxEntity("sales_order");
            order["customer_id"] = "C-D411";
            order["order_number"] = $"SO-D41{i}";
            order["status"] = i % 3 == 0 ? "CANCELLED" : "OPEN";
            order["ordered_on"] = $"2026-11-0{i}";
            order["total"] = 10m * i;
            return order;
        }).ToList();
        await dbContext.Service.CreateMultipleAsync(orders);

        await Scenario1_ACustomersOrdersAsync(dbContext.Service);

        await Scenario2_ARangeOfTheSortKeyAsync(dbContext.Service);

        await Scenario3_NoPartitionAsync(dbContext.Service);
    }

    /// <summary>1. The customer's open orders, newest number first.</summary>
    private static async Task Scenario1_ACustomersOrdersAsync(ArxDynamoDbService service)
    {
        Step("1. A customer's orders");

        var query = new ArxQueryExpression("sales_order");
        query.ColumnSet.AddColumns("order_number", "total");
        query.Criteria.AddCondition("customer_id", ArxConditionOperator.Equal, "C-D411");
        query.Criteria.AddCondition("status", ArxConditionOperator.Equal, "OPEN");
        query.AddOrder("order_number", ArxOrderType.Descending);

        var found = await service.RetrieveMultipleAsync(query);

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number", "total" FROM "crm" WHERE "PK" = 'CUSTOMER#C-D411' AND begins_with("SK", 'ORDER#') AND "status" = 'OPEN'
         * -- Expect: 4 items, SO-D411, SO-D412, SO-D414, SO-D415.
         * ===========================================================
         */
    }

    /// <summary>2. Order numbers from SO-D413 on: a range of the sort key, read in the key's order.</summary>
    private static async Task Scenario2_ARangeOfTheSortKeyAsync(ArxDynamoDbService service)
    {
        Step("2. A range of the sort key");

        var query = new ArxQueryExpression("sales_order");
        query.ColumnSet.AddColumns("order_number");
        query.Criteria.AddCondition("customer_id", ArxConditionOperator.Equal, "C-D411");
        query.Criteria.AddCondition("order_number", ArxConditionOperator.GreaterEqual, "SO-D413");

        var found = await service.RetrieveMultipleAsync(query);

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

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number" FROM "crm" WHERE "PK" = 'CUSTOMER#C-D411' AND "SK" >= 'ORDER#SO-D413'
         * -- Expect: 4 items, SO-D413 to SO-D416.
         * ===========================================================
         */
    }

    /// <summary>3. Every order over 30, whoever the customer: no partition, so ARX8001 rather than a hidden scan.</summary>
    private static async Task Scenario3_NoPartitionAsync(ArxDynamoDbService service)
    {
        Step("3. No partition");

        var query = new ArxQueryExpression("sales_order");
        query.Criteria.AddCondition("total", ArxConditionOperator.GreaterThan, 30m);

        try
        {
            await service.RetrieveMultipleAsync(query);
        }
        catch (ArxNotSupportedException exception)
        {
            ShowError(exception);
        }

        /* ===========================================================
         * [DIRECT QUERY CHECK]
         * -----------------------------------------------------------
         * SELECT "order_number" FROM "crm" WHERE "entity_type" = 'sales_order' AND "total" > 30
         * -- Expect: the orders over 30 (PartiQL scans the table for this).
         * ===========================================================
         */
    }
}

  > 1. A customer's orders
      SO-D415 50, SO-D414 40, SO-D412 20, SO-D411 10
  > 2. A range of the sort key
      SO-D413, SO-D414, SO-D415, SO-D416
  > 3. No partition
      caught ArxNotSupportedException: ARX8001 NotSupported: The query on 'sales_order' doesn't give its partition key (customer_id) with Equal, so DynamoDB could only answer it by reading the whole table (a Scan). Give the key, query an index whose key it gives, or pass new ArxDynamoReadOptions { AllowScan = true }.