Query DynamoDB in .NET using DynamoDBContext.Query<T> with QueryOperator vs using QueryFilter with List<ScanCondition>

Question

I'm trying to query a DynamoDB table using a Global Secondary Index with an HashKey and a RangeKey.

I want to QUERY the IndexCustomerId by CustomerId and DateTime range (from-to). There's no option to set the Index RangeKey as a DateTime. The only choices are: String, Number and Binary.

I'm using C# Object Persistence Model Amazon.DynamoDBv2.DataModel API on .NET Framework v3.5.

Table:

+-------------------+----------+---------------------------+----------------+------------+
| InputFlowCode     |  Counter |  OrderIssueDate           |  OrderTypeCode | CustomerId |
+-------------------+----------+---------------------------+----------------+------------+
| bac9-35df6ac533fc |  000004  |  2016-07-19T22:00:00.000Z | 220            | 123        |
+-------------------+----------+---------------------------+----------------+------------+
| a3db-9d6f56a5c611 |  000006  |  2016-06-30T22:00:00.000Z | 220            | 456        |
+-------------------+----------+---------------------------+----------------+------------+
| af1c-db5b089c1e32 |  000010  |  2016-07-02T22:00:00.000Z | 220            | 789        |
+-------------------+----------+---------------------------+----------------+------------+
| ...               | ...      | ...                       | ...            | ...        |
+-------------------+----------+---------------------------+----------------+------------+

Global Secondary Index definition:

IndexCustomerId:
- CustomerId (integer), 
- OrderIssueDate (DateTime on model, but string on Index definition)

Code:

try
{
    DateTime dateFrom = new DateTime(2016, 08, 01);
    DateTime dateTo = new DateTime(2016, 08, 15);

    List<MyDynamoDBItem> items = new List<MyDynamoDBItem>();

    DynamoDBOperationConfig operationConfig = new DynamoDBOperationConfig();
    operationConfig.OverrideTableName = "Transactions";
    operationConfig.IndexName = "IndexCustomerId";

    DynamoDBContext context = new DynamoDBContext(DynamoDBClient);

    // 1) Works
    items = context.Query<MyDynamoDBItem>(customerId, operationConfig).OrderByDescending(o => o.OrderIssueDate).ToList();

    // 2) Doesn't work
    items = context.Query<MyDynamoDBItem>(customerId, QueryOperator.Between, dateFrom, dateTo, operationConfig).OrderByDescending(o => o.OrderIssueDate).ToList();

    // 3) Works, but I don't know if it is the right choice...
    List<ScanCondition> conditions = new List<ScanCondition>();
    conditions.Add(new ScanCondition(PeppolOrdineDynamoDBTableAttributes.OrderIssueDate, ScanOperator.Between, dateFrom, dateTo));
    operationConfig.QueryFilter = conditions;
    items = context.Query<MyDynamoDBItem>(customerId, operationConfig).OrderByDescending(o => o.OrderIssueDate).ToList();
}
catch (Exception)
{
    throw;
}

The first query without parameters works.

The second query with the Between operator and the 2 DateTime throws an Exception:

{"Cannot cast objects of type 'System.Int32' to type 'System.String'."}

The third query that uses QueryFilter that is a List and ScanOperator works too, but I don't know what's the difference with QueryOperator and also if it is the right choice, since I want to make a QUERY and not a SCAN.

richardk richardk · Accepted Answer · 2017-06-05T11:10:22

I had the same problem but I was using greaterThan, I found that adding the parameter values into an object array fixed it.

So maybe something like:

items = context.Query<MyDynamoDBItem>(customerId, QueryOperator.Between, new object[] {dateFrom, dateTo}, operationConfig).OrderByDescending(o => o.OrderIssueDate).ToList();

Query DynamoDB in .NET using DynamoDBContext.Query<T> with QueryOperator vs using QueryFilter with List<ScanCondition>

1 Answers