5 Ways To Query Data From Amazon DynamoDB using .NET
Table of Contents
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.
This article is sponsored by AWS and is part of my AWS Series.
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.
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 AmazonDynamoDBClient
which 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.
Rahul Nath Newsletter
Join the newsletter to receive the latest updates in your inbox.