One or more parameter values were invalid: ComparisonOperator IN is not valid for SS AttributeValue type

Question

I have a hashkey (DeptId) and rangekey(OrgId) in my dynamodb table. I can have my one department assigned to multiple Orgs. I want to get first 1000 records filtering the hashkey (DeptId) and another column called Status which can have values like A, C, M, I, X etc. Below is how the scenario can be recreated.

docker steps for creating tables

docker run -d -p 8000:8000 --name "sam-dynamodb" amazon/dynamodb-local 
aws configure set region us-east-1
aws dynamodb create-table --table-name OrgDepts --attribute-definitions AttributeName=DeptId,AttributeType=S AttributeName=OrgId,AttributeType=S --key-schema AttributeName=DeptId,KeyType=HASH AttributeName=OrgId,KeyType=RANGE --billing-mode PAY_PER_REQUEST --endpoint-url http://localhost:8000
aws dynamodb list-tables --endpoint-url http://localhost:8000

Example records

[
  {
    "DeptId": {
      "S": "Dept-1"
    },
    "OrgId": {
      "S": "Org-1"
    },
    "Status": {
      "S": "A"
    }
  },
  {
    "DeptId": {
      "S": "Dept-1"
    },
    "OrgId": {
      "S": "Org-2"
    },
    "Status": {
      "S": "C"
    }
  },
  {
    "DeptId": {
      "S": "Dept-1"
    },
    "OrgId": {
      "S": "Org-3"
    },
    "Status": {
      "S": "M"
    }
  },
  {
    "DeptId": {
      "S": "Dept-1"
    },
    "OrgId": {
      "S": "Org-4"
    },
    "Status": {
      "S": "I"
    }
  },
  {
    "DeptId": {
      "S": "Dept-1"
    },
    "OrgId": {
      "S": "Org-5"
    },
    "Status": {
      "S": "X"
    }
  }
]

My C# code is as below:

const int limit = 1000;
var result = new List<OrgDepts>();
var filter = new ScanFilter();

filter.AddCondition("Status", ScanOperator.In, new List<string> {"A", "C", "M"});
filter.AddCondition("DeptId", ScanOperator.Equal, "Dept-1");            

var scanConfig = new ScanOperationConfig
{
    Filter = filter,
    Limit = limit
};

var response = _context.FromScanAsync<OrgDepts>(scanConfig, _config);

do
{
    result.AddRange(await response.GetNextSetAsync(cancellationToken));
} while (response.IsDone == false && result.Count < limit);

On result.AddRange(await response.GetNextSetAsync(cancellationToken)); line I am getting One or more parameter values were invalid: ComparisonOperator IN is not valid for SS AttributeValue type error. I believe this error is thrown because the Status column is not a Set. Any suggestions on how can I get the first 1000 records from the DynamoDb by only passing the hashkey and filtering for multiple values in another string column. I am afraid I will not be able to Query since I am not using the rangekey.

Edit: I see that if I use filter.AddCondition("Status", ScanOperator.Equal, "A"); instead of filter.AddCondition("Status", ScanOperator.In, new List<string> {"A", "C", "M"});, it would return the first record. Is there anyways to get "C" & "M" also included like doing an ORing?

Answer 1

I was able to proceed with Query. My code is as below:

const int limit = 1000;
var result = new List<OrgDepts>();

var filterExpression = new Expression
{
    ExpressionAttributeNames = new Dictionary<string, string>
    {
        {"#status", "Status"}
    },
    ExpressionAttributeValues = new Dictionary<string, DynamoDBEntry>
    {
        { ":a", "A" },
        { ":c", "C" },
        { ":m", "M" }
    },
    ExpressionStatement = "#status In (:a, :c, :m)"
};

var config = new QueryOperationConfig
{
    Limit = limit,
    Filter = new QueryFilter("DeptId", QueryOperator.Equal, "Dept-1"),
    FilterExpression = filterExpression
};

var queryResult = _context.FromQueryAsync<OrgDepts>(config, _config);
do
{
    result.AddRange(await queryResult.GetNextSetAsync(cancellationToken));
} while (queryResult.IsDone == false && result.Count < limit);

Just in case if anyone wondering, its equivalent aws cli powershell command is as below:

aws dynamodb query `
    --table-name OrgDepts `
    --key-condition-expression "DeptId = :deptId" `
    --filter-expression "#status In (:a, :c, :m)" `
    --expression-attribute-names '{\"#status\": \"Status\"}' `
    --expression-attribute-values '{\":deptId\": { \"S\": \"Dept-1\" }, \":a\": { \"S\": \"A\"}, \":c\": { \"S\": \"C\" }, \":m\": { \"S\": \"M\" } }' `
    --endpoint-url http://localhost:8000