Query Secondary Index in Dynamo DB with .NET

Question

I am a .NET Developer trying to get started with DynamoDB. I have spent some time trying to query by a global secondary index and I find it harder than expected. I have created a table and an index. I am able to store item and get them back by the primary key which is a guid. I show two ways of querying that do not work. I have tried a couple of variations of these too. I am using .NET Core. My items have a MappingId as the primary key (a guid). But quite often I will look them up by ProductId, so I created a secondary global index for that.

This is the code for creating the table and index:

   private Table CreateTable(string tableName)
    {

        CreateTableRequest createRequest = null;

        if (tableName == "ProductLevelProductPropertyMapping")
        {
            createRequest = new CreateTableRequest
            {
                TableName = "ProductLevelProductPropertyMapping",
                AttributeDefinitions = new List<AttributeDefinition>()
                {
                    new AttributeDefinition
                    {
                        AttributeName = "MappingId",
                        AttributeType = "S"
                    } ,
                    new AttributeDefinition
                    {
                        AttributeName = "ProductId",
                        AttributeType = "N"
                    }

               },
                KeySchema = new List<KeySchemaElement>()
                    {
                        new KeySchemaElement
                        {
                            AttributeName = "MappingId",
                            KeyType = "HASH"
                        }
                    },

                GlobalSecondaryIndexes = new List<GlobalSecondaryIndex>()
                    {
                        new GlobalSecondaryIndex()
                        {
                            IndexName = "ProductIdIdx",

                             KeySchema = new List<KeySchemaElement>()
                            {
                                new KeySchemaElement
                                {
                                    AttributeName = "ProductId",
                                    KeyType = "HASH"
                                }
                            }

                        }
                    },




            };


        }



        if (tableName == "SupplierLevelProductPropertyMapping")
        {
          // Create Something else
          .
          .

        }


        // Build a 'CreateTableRequest' for the new table


        // Provisioned-throughput settings are required even though
        // the local test version of DynamoDB ignores them
        createRequest.ProvisionedThroughput = new ProvisionedThroughput(1, 1);
        createRequest.GlobalSecondaryIndexes[0].ProvisionedThroughput = new ProvisionedThroughput(1, 1);
        createRequest.GlobalSecondaryIndexes[0].Projection = new Projection() { ProjectionType = ProjectionType.ALL };

        // Using the DynamoDB client, make a synchronous CreateTable request

        try
        {
            var task = client.CreateTableAsync(createRequest);
            task.Wait();

            Console.WriteLine("\n\n Created the {0} table successfully!\n    Status of the new table: '{1}'", tableName, task.Result.TableDescription.TableStatus);
        }
        catch (Exception ex)
        {
            Console.WriteLine("\n Error: failed to create the new table; " + ex.Message);

            return null;
        }


        return Table.LoadTable(client, tableName);


    }

I have a handful of items in my db but this way to query gives zero hits:

public ProductLevelProductPropertyMapping GetProductLevelItemByProductId(int productId)
    {

        var request = new QueryRequest
        {
            TableName = "ProductLevelProductPropertyMapping",
            IndexName = "ProductIdIdx",
            KeyConditionExpression = "ProductId = :v_Id",
            ExpressionAttributeValues = new Dictionary<string, AttributeValue> {
             {":v_Id", new AttributeValue { N =  productId.ToString() }}}
        };

        var task = client.QueryAsync(request);

        task.Wait();

        var result = task.Result.Items; //Zero hits 
        .
        .
        //Return some item here
    }

I have seen a similair example in Amazons documentation. But it is not crystal clear to me what they are trying to do or how. http://docs.aws.amazon.com/amazondynamodb/latest/developerguide/GSILowLevelDotNet.html#GSILowLevelDotNet.QueryAnIndex

I also tried this way, but I get an exception:

    public ProductLevelProductPropertyMapping GetProductLevelItemByProductId(int productId)
    {

      var myAttributes = new List<AttributeValue>();

       myAttributes.Add(new AttributeValue($"'N' : '{0}' " + productId.ToString()));


       var myQF = new QueryFilter("ProductId", QueryOperator.Equal, myAttributes);

        var conf = new QueryOperationConfig()
        {
            IndexName = "ProductIdIdx",
            Filter = myQF  
        };


        var someSearchObject = productLevelTable.Query(conf);


        var f = someSearchObject.Count;

        .
        .
        //return some result object.

    }

The second way of querying gives me a AmazonDynamoDBException with the message: One or more parameter values were invalid: Condition parameter type does not match schema type.

Can anyone give me some input on what I am doing wrong?

Answer 1

For the second, more high-level, way of doing it, you should simply send in your productId as your last parameter to your QueryFilter. Like this:

var myQF = new QueryFilter("ProductId", QueryOperator.Equal, productId);

The error message you got means that your object type (List<AttributeValue>) doesn't match the index hash key type (N for number). What is expected is that you send in any kind of number, an integer in this case.

Try that and get back with an updated question if there are more problems.

---

Update

In reply to reasonings in your answer...

DynamoDB is case sensitive, as Pejus @ AWS says in the AWS Forum.

What I believe happened is that you added a field "productid" to your documents/items and then storing that in the table (as you say). Note that you can add any field with any name to your document without specifying it in your table structure. So you can very well add two fields like "productid" and "ProductId" at the same time.

When you query your table DynamoDB will check your query against your table definition, not existing properties in the table. So, you didn't find any items because the had a valid query looking for the field "ProductId", which didn't exist for your added documents/items. So, the query was earlier ok because it matched the table definition and it logically returned no items. It shouldn't have thrown an exception either.

Good that things solved, anyway.

Answer 2

Thanks your suggested codeline was part of the solution.

var myQF = new QueryFilter("ProductId", QueryOperator.Equal, productId);

Let me query without exception, but still zero hits. But there was also a casing problem. When Creating tables and indexes I used "ProductId". When storing the documents I was writing "productid" like this:

 myDoc["productid"] = item.ProductId;

This seemed to cause zero hits but no exception. However if you would try to query an attribute that do not exist you do get an error.

var myQF = new QueryFilter("SoEasyToGetStarted", QueryOperator.Equal, productId); 

Then you will get an exception. So I am a bit suprised I did not get an exception when mixing up "productid" and "ProductId". If dynamo is not case sensitive I expected hits for my query. Anyway, now things are working.