5 Ways To Query Data From Amazon DynamoDB using .NET

Querying is an essential operation in DynamoDB.

It allows you to filter and select items in your database based on your application and user needs.

When moving over to DynamoDB from more traditional relational databases like SQL Server, you must understand the different ways you can retrieve data in DynamoDB.

In this article, let’s explore the different ways you can query data from Amazon DynamoDB when building applications using .NET. We will learn

• Loading Specific Items

• Querying by Partition Key

• Querying by Partition and Sort Key

• Using Low-level .NET SDK API For Queries

• Filtering Data By Other Properties

Quick Recap of DynamoDB

Before we get into how you can query data, let’s take a quick recap of AWS DynamoDB.

DynamoDB is a cloud-hosted NoSQL database provided by Amazon Web Services (AWS). DynamoDB provides reliable performance, a managed experience, and convenient API access to interact with it.

If you are new to DynamoDB, I highly recommend checking out my AWS DynamoDB For the .NET Developer article below to understand better.

AWS DynamoDB For The .NET Developer: How To Easily Get Started

Learn how to get started with AWS DynamoDB with .NET Core by updating the default ASP NET Web API template to use DynamoDB as it's data store. We will learn to do basic Creat, Read, Update and Delete operations from the API.

rahulpnath.com

One of the reasons why it can be a bit tricky with querying data is that you almost always need the partition key to get any data out of DynamoDB.

While there are ways to get data without it, which we will see towards the end of this article, it’s generally not recommended and not performant.

Let’s learn the different ways to filter and select data from DynamoDB using .NET.

.NET And DynamoDB Setup

The sample application I am using is the default ASP NET Core Web API Template that Visual Studio creates, with the WeatherForecastController.

I have updated the API to add a CityName property to the WeatherForecast class. I have also set up Dependency Injection to inject the IDynamoDBContext context into the Controller.

var dynamoDbClient = new AmazonDynamoDBClient(
    FallbackCredentialsFactory.GetCredentials(), RegionEndpoint.APSoutheast2);
builder.Services.AddSingleton<IAmazonDynamoDB>(dynamoDbClient);
builder.Services.AddSingleton<IDynamoDBContext, DynamoDBContext>();

If any of this is unclear, check the previous blog post on Getting Started With DynamoDB.

1. Load Specific Item

To load a specific item from the DynamoDB, we require both the Hash key and the Range Key, in this case, the CityName and DateTime.

The LoadAsync method, takes in both these as parameters and returns the WeatherForecast object.

[HttpGet("specific-date")]
public async Task<WeatherForecast> GetAsync(string cityName, DateTime date)
{
    return await _dynamoDbContext.LoadAsync<WeatherForecast>(cityName, date);
}

If an item with that Hash and Range Key does not exist, the method returns null. So make sure your application handles the scenario when the item does not exist.

For an API, you can return a 404 NotFound.

2. Query By Partition Key

Often applications and users need to get more data than just one specific item.

This is most commonly required when having a List/Table shown in the application UI.

Let's say we need an API endpoint to return all the WeatherData for a given City.

In this case, all we have is the CityName, which is the Hash key.

We can use the QueryAsync method and pass in the hash key to it, as shown below.

[HttpGet("city-all")]
public async Task<IEnumerable<WeatherForecast>>  GetAsync(string cityName)
{
    return await _dynamoDbContext.QueryAsync<WeatherForecast>(cityName).GetRemainingAsync();
}

The QueryAsync method returns an AsyncSearch<T> as the response, which has additional methods to retrieve data from DynamoDb.

In the sample above, I use the GetRemainingAsync method to fetch all the items.

Note: The GetRemainingAsync method on AsyncSearch, loops through and fetches all the items matching the specified condition. This could result in more than one request to DynamoDB.

3. Query By Partition and Sort Key

When using the QueryAsync method, you can further filter the items that get returned. For a given city, you might have weather data from a long time.

And the application might be interested only in the last month's data or three, for example.

In these scenarios, we can use the QueryOperator and pass in the range key along with it.

[HttpGet("city-date-filter")]
public async Task<IEnumerable<WeatherForecast>> GetAsync(string cityName, DateTime dateTime)
{
    return await _dynamoDbContext.QueryAsync<WeatherForecast>(cityName, QueryOperator.GreaterThan,
            new object[] {dateTime})
        .GetRemainingAsync();
}

Above the QueryOperator.GreaterThan, returns all the Weather Data items that are about the specified DateTime.

Since the greater than operator requires only one parameter, we need to pass in only one DateTime.

However, if you are using the QueryOperator.Between, which requires two dates, you need to pass them in order (start and end) to the object array, as shown below.

[HttpGet("city-date-filter")]
public async Task<IEnumerable<WeatherForecast>> GetAsync(
    string cityName, DateTime dateTime, DateTime? endDateTime)
{
    return await _dynamoDbContext
        .QueryAsync<WeatherForecast>(
           cityName,
           QueryOperator.Between,
           new object[] { dateTime, endDateTime })
        .GetRemainingAsync();
}

4. Using Low-level .NET SDK API For Queries

Now that we understand how to filter and select data based on the Hash and range keys let’s see how we can further filter the data based on other properties on our DynamoDB Items.

Let’s say we need to get only Weather data for a city since last month where the temperature was above 25°C.

The High level DynamoDBContext API in the .NET SDK does not support this level of data filtering. For this, we need to switch down to the Low-Level APIs in the DynamoDB .NET SDK.

The IAmazonDynamoDB can be used for advanced data filtering scenarios like this.

In Program.cs we have already set up the AmazonDynamoDBClientwhich implements IAmazonDynamoDB, to be dependency injected.

The IAmazonDynamoDB has a similar QueryAsync method, which takes in a more complex QueryRequest class.

Let’s first understand the QueryRequest class and its usage by using it in a simple use case to get all the data for a given city Name (Hash key) before we go into the more advanced scenarios.

Query with Hash using KeyConditions

The QueryRequest expects us to pass the TableName and the KeyConditions. The KeyConditions takes in a dictionary of property names and the associated conditions for those properties.

In our case, since we want to get all the weather forecast items for the city name, let’s pass in the CityName property and the Condition ComparisonOperator.EQ to the expected city name, as shown below.

[HttpGet("city-all-low-level")]
public async Task<IEnumerable<WeatherForecast>> GetLowLevelAsync(string cityName)
{
    var request = new QueryRequest()
    {
        TableName = nameof(WeatherForecast),
        KeyConditions = new Dictionary<string, Condition>()
        {
            {
                nameof(WeatherForecast.CityName),
                new Condition()
                {
                    ComparisonOperator = ComparisonOperator.EQ,
                    AttributeValueList = new List<AttributeValue>() {new(cityName)}
                }
            }
        }
    };

    var response = await _amazonDynamoDbClient.QueryAsync(request);
    return response.Items
        .Select(Document.FromAttributeMap)
        .Select(_dynamoDbContext.FromDocument<WeatherForecast>);
}

The QueryAsync being part of the Low-Level APIs does not return our custom-defined .NET class types (WeatherForecast in this case). It returns the Items as a List<Dictionary<string, AttributeValue>> type.

To convert it into a strongly typed .NET class, use the Document.FromAttributeMap helper method and then use the FromDocument method on the DynamoDBContext as shown above.

With the above code, we are now successfully using the .NET Low-Level APIs to query the DynamoDB directly.

Query with Hash using KeyConditionExpression

Using the KeyConditions adds in a lot more boilerplate code, which can be avoided by using the KeyConditionExpression property.

With this new property, you only need to specify the equality comparison condition as a plain string with a placeholder name for the actual value. This is similar to using a SQL query and passing parameters to it.

Below we have the KeyConditionExpression to get all CityName matching to the placeholder ‘:cityName’. To pass in the actual value for the city name, we use the ExpressionAttributeValues property.

Below we need a key-value pair for the cityName placeholder and its associated value, which is the actual city name we are looking to get the data for.

var request = new QueryRequest()
{
    TableName = nameof(WeatherForecast),
    KeyConditionExpression = "CityName = :cityName",
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
    {
        {":cityName", new AttributeValue(cityName)}
    }
};

This is way less boilerplate code and much more readable.

Reserved Keywords in KeyConditionExpression and Attribute Names

If we want to further filter the data by the date as we did in the earlier examples, we can do that using the same KeyConditionExpression. All we need to do is append the condition on the Sort Key using the ‘and’ keyword and pass in the additional ExpressionAttributeValues.

For example: To filter the data by items after a given date, we can use the below expression.

    KeyConditionExpression = "CityName = :cityName and Date > :startDate",

However, in this particular case, this query will fail since the property name ‘Date’ conflicts with a DynamoDB Reserved Keyword.

In these scenarios, where the property names conflict with DynamoDB Reserved Keywords, you need to pass the property name also as placeholder properties and send the corresponding values using the ExpressionAttributeNames property.

As shown below, the KeyConditionExpression now specifies a placeholder ‘#Date’ for the Date property and adds the appropriate mapping for the placeholder to the actual property name in the ExpressionAttributeNames property.

var request = new QueryRequest()
{
    TableName = nameof(WeatherForecast),
    KeyConditionExpression = "CityName = :cityName and #Date > :startDate",
    ExpressionAttributeNames = new Dictionary<string, string>()
    {
        {"#Date", "Date"}
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
    {
        {":cityName", new AttributeValue(cityName)},
        {":startDate", new AttributeValue(startDate.ToString(AWSSDKUtils.ISO8601DateFormat))}
    }
};

This helps us to query the data using the Low-Level .NET DynamoDB APIs and filter data based on both the Hash and sort keys.

5. Filtering Data By Non-Key Properties

Finally, let’s filter the data based on the Temperature property, which is why we set down the path of exploring the Low-Level APIs.

Using the FilterExpression property, we can further filter down the data filtered by the KeyConditionExpression.

Since in this case, we need to get all the weather data items with a temperature greater than a specific value, let’s add that condition to FIlterExpression property as shown below.

This again uses the same placeholder name for the value, and it passes the actual value as part of the ExpressionAttributeValues as shown below.

var request = new QueryRequest()
{
    TableName = nameof(WeatherForecast),
    KeyConditionExpression = "CityName = :cityName and #Date > :startDate",
    FilterExpression = "TemperatureC >= :minTemp",
    ExpressionAttributeNames = new Dictionary<string, string>()
    {
        {"#Date", "Date"}
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
    {
        {":cityName", new AttributeValue(cityName)},
        {":startDate", new AttributeValue(startDate.ToString(AWSSDKUtils.ISO8601DateFormat))},
        {":minTemp", new AttributeValue() {N = minTemp.ToString()}}
    },
};

This filters the records that are greater than or equal to the minimum temperature passed in.

Filter Expression Using Between

Both the KeyConditionExpression and the FilterExpression supports using the between operator as well.

All we need to do is pass in multiple parameters for the between operator using the and operator.

Below is an example that uses the between operator in FilterExpression to filter all the items within a given temperature range.

var request = new QueryRequest()
{
    TableName = nameof(WeatherForecast),
    KeyConditionExpression = "CityName = :cityName and #Date > :startDate",
    FilterExpression = "TemperatureC between :minTemp and :maxTemp",
    ExpressionAttributeNames = new Dictionary<string, string>()
    {
        {"#Date", "Date"}
    },
    ExpressionAttributeValues = new Dictionary<string, AttributeValue>()
    {
        {":cityName", new AttributeValue(cityName)},
        {":startDate", new AttributeValue(startDate.ToString(AWSSDKUtils.ISO8601DateFormat))},
        {":minTemp", new AttributeValue() {N = minTemp.ToString()}},
        {":maxTemp", new AttributeValue() {N = maxTemp.ToString()}}
    },
};

6. Scan The Whole Table (Avoid Using This)

A Scan Operation does exactly as its name; it scans the entire table, looking for each item that matches the specified criteria.

I’ve kept this to the last because I was hoping you could try and avoid using this as much as possible.

Scans can also be costlier for the same reason. Since you are billed based on the data scanned and not on the date returned.

Below is an example of using Scan to find items where the temperature is greater than 30 degrees Celsius.

var scanItems = await _dynamoDbContext.ScanAsync<WeatherForecast>(
    new[]
    {
        new ScanCondition(nameof(WeatherForecast.TemperatureC), ScanOperator.GreaterThan, 30)
    }).GetRemainingAsync();

When running this, it loops through all the items in the DynamoDB and checks if the TemperatureC property is greater than the given value. If you have a database with a large number of items, this is going to be a slow and costly operation.

I hope you now can query data from DynamoDB without having to scan through it. If not, we will see how you could use Indexes to solve and make your use case faster in a future article.