Amazon DynamoDB
Developer Guide (API Version 2012-08-10)
« PreviousNext »
View the PDF for this guide.Go to the AWS Discussion Forum for this product.Go to the Kindle Store to download this guide in Kindle format.Did this page help you?  Yes | No |  Tell us about it...

Querying Tables Using the AWS SDK for .NET Low-Level API

The Query function enables you to query a table or a secondary index. You must provide a hash key value and an equality condition. If the table or index has a range key, you can refine the results by providing a range key value and a condition.

Note

This section explains the .NET SDK low-level API. The .NET SDK also provides a set of document model classes (see .NET: Document Model) that wrap some of the low-level API to simplify your coding tasks. In addition, the .NET SDK also provides a high-level object persistence model (see .NET: Object Persistence Model), enabling you to map your client-side classes to DynamoDB tables.

The following are the steps to query a table using low-level .NET SDK API.

  1. Create an instance of the AmazonDynamoDBClient class.

  2. Create an instance of the QueryRequest class and provide query operation parameters.

  3. Execute the Query method and provide the QueryRequest object that you created in the preceding step.

    The response includes the QueryResult object that provides all items returned by the query.

The following C# code snippet demonstrates the preceding tasks. The snippet assumes you have a Reply table stores replies for forum threads. For more information, see Example Tables and Data.

Reply ( Id, ReplyDateTime, ... )

Each forum thread has a unique ID and can have zero or more replies. Therefore, the primary key is composed of both the Id (hash attribute) and ReplyDateTime (range attribute).

The following query retrieves all replies for a specific thread subject. The query requires both the table name and the Subject value.

AmazonDynamoDBClient client = new AmazonDynamoDBClient();

var request = new QueryRequest
{
  TableName = "Reply",
  KeyConditions = new Dictionary<string,Condition>()
  {
    {
      "Id",
      new Condition()
      {
        ComparisonOperator = "EQ",
        AttributeValueList = new List<AttributeValue>()
        {
          new AttributeValue { S = "DynamoDB#DynamoDB Thread 1" }
        }
      }
    }
  }
};

var response = client.Query(request);
var result = response.QueryResult;

foreach (Dictionary<string, AttributeValue> item in response.QueryResult.Items)
{
  // Process the result.
  PrintItem(item);
} 

Specifying Optional Parameters

The Query method supports several optional parameters. For example, you can optionally narrow the query result in the preceding query to return replies in the past two weeks by specifying a condition. The condition is called a range condition because Amazon DynamoDB evaluates the query condition that you specify against the range attribute of the primary key. You can specify other optional parameters to retrieve only a specific list of attributes from items in the query result. For more information about the parameters and the API, see Query.

The following C# code snippet retrieves forum thread replies posted in the past 15 days. The snippet specifies the following optional parameters:

  • A RangeKeyCondition parameter to retrieve only the replies in the past 15 days.

    The condition specifies a ReplyDateTime value and a comparison operator to use for comparing dates.

  • A ProjectionExpression parameter to specify a list of attributes to retrieve for items in the query result.

  • A ConsistentRead parameter to perform a strongly consistent read. To learn more about read consistency, see DynamoDB Data Model.

DateTime twoWeeksAgoDate = DateTime.UtcNow - TimeSpan.FromDays(15);
string twoWeeksAgoString = twoWeeksAgoDate.ToString(AWSSDKUtils.ISO8601DateFormat);

var request = new QueryRequest
{
  TableName = "Reply",
  KeyConditions = new Dictionary<string,Condition>()
  {
    {
      "Id",
      new Condition()
      {
        ComparisonOperator = "EQ",
        AttributeValueList = new List<AttributeValue>()
        {
          new AttributeValue { S = "DynamoDB#DynamoDB Thread 2" }
        }
      }
    }
  },
  {
    {
      "ReplyDateTime",
      new Condition()
      {
        ComparisonOperator = "GT",
        AttributeValueList = new List<AttributeValue>()
        {
          new AttributeValue { S = twoWeeksAgoString }
        }
      }
    }
  },
  ProjectionExpression = "Subject, ReplyDateTime, PostedBy",
  ConsistentRead = true
};

var response = client.Query(request);
var result = response.QueryResult;

foreach (Dictionary<string, AttributeValue> item 
  in response.QueryResult.Items)
{
  // Process the result.
  PrintItem(item);
}

You can also optionally limit the page size, or the number of items per page, by adding the optional Limit parameter. Each time you execute the Query method, you get one page of results that has the specified number of items. To fetch the next page, you execute the Query method again by providing the primary key value of the last item in the previous page so that the method can return the next set of items. You provide this information in the request by setting the ExclusiveStartKey property. Initially, this property can be null. To retrieve subsequent pages, you must update this property value to the primary key of the last item in the preceding page.

The following C# code snippet queries the Reply table. In the request, it specifies the Limit and ExclusiveStartKey optional parameters. The do/while loop continues to scan one page at time until the LastEvaluatedKey returns a null value.

Dictionary<string,AttributeValue> lastKeyEvaluated = null;
do
{
  var request = new QueryRequest
  {
    TableName = "Reply",
    KeyConditions = new Dictionary<string,Condition>()
    {
      {
        "Id",
        new Condition()
        {
          ComparisonOperator = "EQ",
          AttributeValueList = new List<AttributeValue>()
          {
            new AttributeValue { S = "DynamoDB#DynamoDB Thread 2" }
          }
        }
      }
    },
    // Optional parameters.
    Limit = 10,
    ExclusiveStartKey = lastKeyEvaluated
  };

  var response = client.Query(request);
  // Process the query result.
  foreach (Dictionary<string, AttributeValue> item in response.QueryResult.Items)
  {
    PrintItem(item);
  }
  lastKeyEvaluated = response.QueryResult.LastEvaluatedKey;

} while (lastKeyEvaluated != null);

Example - Querying Using the AWS SDK for .NET

The following tables store information about a collection of forums. For more information about table schemas, see Example Tables and Data.

Forum ( Name, ... ) 
Thread ( ForumName, Subject, Message, LastPostedBy, LastPostDateTime, ...) 
Reply ( Id, ReplyDateTime, Message, PostedBy, ...)

In this C# code example, you execute variations of "Find replies for a thread "DynamoDB Thread 1" in forum "DynamoDB".

  • Find replies for a thread.

  • Find replies for a thread. Specify the Limit query parameter to set page size.

    This function illustrate the use of pagination to process multipage result. Amazon DynamoDB has a page size limit and if your result exceeds the page size, you get only the first page of results. This coding pattern ensures your code processes all the pages in the query result.

  • Find replies in the last 15 days.

  • Find replies in a specific date range.

    Both of the preceding two queries shows how you can specify range key conditions to narrow query results and use other optional query parameters.

Note

This section explains the .NET SDK low-level API. The .NET SDK also provides a set of document model classes (see .NET: Document Model) that wrap some of the low-level API to simplify your coding tasks. In addition, the .NET SDK also provides a high-level object persistence model (see .NET: Object Persistence Model), enabling you to map your client-side classes to DynamoDB tables. The individual object instances then map to items in a table.

For a code example that demonstrates query operations using the document model, see Table.Query Method in the AWS SDK for .NET. For a code example that demonstrates query operations using the object persistence model, see Example: Query and Scan in DynamoDB Using the AWS SDK for .NET Object Persistence Model.

For step-by-step instructions to test the following sample, see Using the AWS SDK for .NET.

using System;
using System.Collections.Generic;
using Amazon.DynamoDBv2;
using Amazon.DynamoDBv2.Model;
using Amazon.Runtime;
using Amazon.Util;

namespace com.amazonaws.codesamples
{
    class LowLevelQuery
    {
        private static AmazonDynamoDBClient client = new AmazonDynamoDBClient();

        static void Main(string[] args)
        {
            try
            {
                // Query a specific forum and thread.
                string forumName = "Amazon DynamoDB";
                string threadSubject = "DynamoDB Thread 1";

                FindRepliesForAThread(forumName, threadSubject);
                FindRepliesForAThreadSpecifyOptionalLimit(forumName, threadSubject);
                FindRepliesInLast15DaysWithConfig(forumName, threadSubject);
                FindRepliesPostedWithinTimePeriod(forumName, threadSubject);

                Console.WriteLine("Example complete. To continue, press Enter");
                Console.ReadLine();
            }
            catch (AmazonDynamoDBException e) { Console.WriteLine(e.Message); }
            catch (AmazonServiceException e) { Console.WriteLine(e.Message); }
            catch (Exception e) { Console.WriteLine(e.Message); }
        }

        private static void FindRepliesPostedWithinTimePeriod(string forumName, string threadSubject)
        {
            Console.WriteLine("*** Executing FindRepliesPostedWithinTimePeriod() ***");
            string replyId = forumName + "#" + threadSubject;
            // You must provide date value based on your test data.
            DateTime startDate = DateTime.UtcNow - TimeSpan.FromDays(21);
            string start = startDate.ToString(AWSSDKUtils.ISO8601DateFormat);

            // You provide date value based on your test data.
            DateTime endDate = DateTime.UtcNow - TimeSpan.FromDays(5);
            string end = endDate.ToString(AWSSDKUtils.ISO8601DateFormat);

            var request = new QueryRequest
            {
                TableName = "Reply",
                ReturnConsumedCapacity = "TOTAL",
                KeyConditions = new Dictionary<string, Condition>()
        {
          {
            "Id",
            new Condition()
            {
              ComparisonOperator = "EQ",
              AttributeValueList = new List<AttributeValue>()
              {
                new AttributeValue { S = replyId }
              }
            }
          },
          {
            "ReplyDateTime",
            new Condition
            {
              ComparisonOperator = "BETWEEN",
              AttributeValueList = new List<AttributeValue>()
              {
                new AttributeValue { S = start },
                new AttributeValue { S = end }
              }
            }
          }
        }
            };

            var response = client.Query(request);

            Console.WriteLine("\nNo. of reads used (by query in FindRepliesPostedWithinTimePeriod) {0}",
                              response.ConsumedCapacity.CapacityUnits);
            foreach (Dictionary<string, AttributeValue> item
              in response.Items)
            {
                PrintItem(item);
            }
            Console.WriteLine("To continue, press Enter");
            Console.ReadLine();
        }

        private static void FindRepliesInLast15DaysWithConfig(string forumName, string threadSubject)
        {
            Console.WriteLine("*** Executing FindRepliesInLast15DaysWithConfig() ***");
            string replyId = forumName + "#" + threadSubject;

            DateTime twoWeeksAgoDate = DateTime.UtcNow - TimeSpan.FromDays(15);
            string twoWeeksAgoString =
              twoWeeksAgoDate.ToString(AWSSDKUtils.ISO8601DateFormat);

            var request = new QueryRequest
            {
                TableName = "Reply",
                ReturnConsumedCapacity = "TOTAL",
                KeyConditions = new Dictionary<string, Condition>()
        {
          {
            "Id",
            new Condition
            {
              ComparisonOperator = "EQ",
              AttributeValueList = new List<AttributeValue>()
              {
                new AttributeValue { S = replyId }
              }
            }
          },
          {
            "ReplyDateTime",
            new Condition
            {
              ComparisonOperator = "GT",
              AttributeValueList = new List<AttributeValue>()
              {
                new AttributeValue { S = twoWeeksAgoString }
              }
            }
          }
        },
                // Optional parameter.
                ProjectionExpression = "Id, ReplyDateTime, PostedBy",
                // Optional parameter.
                ConsistentRead = true
            };

            var response = client.Query(request);

            Console.WriteLine("No. of reads used (by query in FindRepliesInLast15DaysWithConfig) {0}",
                              response.ConsumedCapacity.CapacityUnits);
            foreach (Dictionary<string, AttributeValue> item
              in response.Items)
            {
                PrintItem(item);
            }
            Console.WriteLine("To continue, press Enter");
            Console.ReadLine();
        }

        private static void FindRepliesForAThreadSpecifyOptionalLimit(string forumName, string threadSubject)
        {
            Console.WriteLine("*** Executing FindRepliesForAThreadSpecifyOptionalLimit() ***");
            string replyId = forumName + "#" + threadSubject;

            Dictionary<string, AttributeValue> lastKeyEvaluated = null;
            do
            {
                var request = new QueryRequest
                {
                    TableName = "Reply",
                    ReturnConsumedCapacity = "TOTAL",
                    KeyConditions = new Dictionary<string, Condition>()
          {
            {
              "Id",
              new Condition
              {
                ComparisonOperator = "EQ",
                AttributeValueList = new List<AttributeValue>()
                {
                  new AttributeValue { S = replyId }
                }
              }
            }
          },
                    Limit = 2, // The Reply table has only a few sample items. So the page size is smaller.
                    ExclusiveStartKey = lastKeyEvaluated
                };

                var response = client.Query(request);

                Console.WriteLine("No. of reads used (by query in FindRepliesForAThreadSpecifyLimit) {0}\n",
                                  response.ConsumedCapacity.CapacityUnits);
                foreach (Dictionary<string, AttributeValue> item
                  in response.Items)
                {
                    PrintItem(item);
                }
                lastKeyEvaluated = response.LastEvaluatedKey;

            }  while (lastKeyEvaluated != null && lastKeyEvaluated.Count != 0) ;

            Console.WriteLine("To continue, press Enter");


            Console.ReadLine();
        }

        private static void FindRepliesForAThread(string forumName, string threadSubject)
        {
            Console.WriteLine("*** Executing FindRepliesForAThread() ***");
            string replyId = forumName + "#" + threadSubject;

            var request = new QueryRequest
            {
                TableName = "Reply",
                ReturnConsumedCapacity = "TOTAL",
                KeyConditions = new Dictionary<string, Condition>()
        {
          {
            "Id",
            new Condition
            {
              ComparisonOperator = "EQ",
              AttributeValueList = new List<AttributeValue>()
              {
                new AttributeValue { S = replyId }
              }
            }
          }
        }
            };

            var response = client.Query(request);
            Console.WriteLine("No. of reads used (by query in FindRepliesForAThread) {0}\n",
                              response.ConsumedCapacity.CapacityUnits);
            foreach (Dictionary<string, AttributeValue> item
              in response.Items)
            {
                PrintItem(item);
            }
            Console.WriteLine("To continue, press Enter");
            Console.ReadLine();
        }

        private static void PrintItem(
         Dictionary<string, AttributeValue> attributeList)
        {
            foreach (KeyValuePair<string, AttributeValue> kvp in attributeList)
            {
                string attributeName = kvp.Key;
                AttributeValue value = kvp.Value;

                Console.WriteLine(
                    attributeName + " " +
                    (value.S == null ? "" : "S=[" + value.S + "]") +
                    (value.N == null ? "" : "N=[" + value.N + "]") +
                    (value.SS == null ? "" : "SS=[" + string.Join(",", value.SS.ToArray()) + "]") +
                    (value.NS == null ? "" : "NS=[" + string.Join(",", value.NS.ToArray()) + "]")
                );
            }
            Console.WriteLine("************************************************");
        }

    }
}