5 Ways To Query Data From Amazon DynamoDB using .NET
Tags: aws, dotnet, dynamodb
Published: 2024-02-09 05:19:52

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.

{% embed https://youtu.be/iv6OKueqBd4 %}

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.

{% embed https://www.rahulpnath.com/blog/aws-dynamodb-net-core/ %}


> 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.

```csharp
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](https://www.rahulpnath.com/blog/aws-dynamodb-net-core/).

## 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.

```csharp
[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.

```csharp
[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.

```csharp
[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.

```csharp
[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.

```csharp
[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.

```csharp
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.

```csharp
    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](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ReservedWords.html).

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.

```csharp
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.

```csharp
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.

```csharp
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.

```csharp
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.