DynamoDB is a cloud-hosted NoSQL database from Amazon. It offers dependable performance, a well-managed environment, and easy API access to interact with it.
This article will help you perform various queries in DynamoDB with C#.
If you're looking for similar guide but for Node.js, you can find it here, for Rust, and for Python / boto3 here.
List of DynamoDB C# Query Examples
Setup
To set up the DynamoDB in the .NET environment, you need the AWSSDK.DynamoDBv2 package. It provides a .NET API that facilitates the interactions with DynamoDB to execute different query operations.
For this article's examples, I'm using the Localstack framework. And, the syntax will remain the same for the actual DynamoDB instance by removing the Service URL parameter.
Let's see how to insert a new record and retrieve it back using scanning the table or using a hash key.
Setup the DynamoDB client
public class DynamoClientSetup {
private readonly AmazonDynamoDBClient _amazonDynamoDBClient;
private readonly DynamoDBContext _context;
public DynamoClient() {
_amazonDynamoDBClient = new AmazonDynamoDBClient("acessId", "awsSecretAccessKey",
new AmazonDynamoDBConfig {
ServiceURL = "http://localhost:4569",
UseHttp = true,
});
_context = new DynamoDBContext(_amazonDynamoDBClient, new DynamoDBContextConfig {
TableNamePrefix = "test_"
});
}
}
You need to pass the accessId and the accessKey to AmazonDynamoDBClient constructor.
Create Table and Model Class
To construct the table, we're building the create table request. We must specify all of the table's declared keys to do so. If you have many tables, this may appear to be a bit of a formality; however, I will demonstrate several alternatives to make this process easier and faster in the future.
[DynamoDBTable("employee")]
public class Company: IEquatable < Employee > {
[DynamoDBHashKey]
public int empId {
get;
set;
}
public string FirstName {
get;
set;
}
public string LastName {
get;
set;
}
public bool Equals(Employee other) {
if (ReferenceEquals(null, other)) return false;
if (ReferenceEquals(this, other)) return true;
return Id == other.Id && string.Equals(FirstName, other.FirstName) && string.Equals(LastName, other.LastName);
}
public override bool Equals(object obj) {
if (ReferenceEquals(null, obj)) return false;
if (ReferenceEquals(this, obj)) return true;
if (obj.GetType() != this.GetType()) return false;
return Equals((Student) obj);
}
public override int GetHashCode() {
unchecked {
var hashCode = empId;
hashCode = (hashCode * 397) ^ (FirstName != null ? FirstName.GetHashCode() : 0);
hashCode = (hashCode * 397) ^ (LastName != null ? LastName.GetHashCode() : 0);
return hashCode;
}
}
}
There are two unique attributes we can use.
- DynamoDBTable--- Map DynamoDB equivalent table.
- DynamoDBHashKey--- Map the table hash key.
public async Task < CreateTableResponse > SetupAsync() {
var createTableRequest = new CreateTableRequest {
TableName = "test_employee",
AttributeDefinitions = new List < AttributeDefinition > (),
KeySchema = new List < KeySchemaElement > (),
GlobalSecondaryIndexes = new List < GlobalSecondaryIndex > (),
LocalSecondaryIndexes = new List < LocalSecondaryIndex > (),
ProvisionedThroughput = new ProvisionedThroughput {
ReadCapacityUnits = 1,
WriteCapacityUnits = 1
}
};
createTableRequest.KeySchema = new [] {
new KeySchemaElement {
AttributeName = "empId",
KeyType = KeyType.HASH,
},
}.ToList();
createTableRequest.AttributeDefinitions = new [] {
new AttributeDefinition {
AttributeName = "empId",
AttributeType = ScalarAttributeType.N,
}
}.ToList();
}
Query
DynamoDB allows querying not only on the main table index, but also on LSIs (Local Secondary Indexes) and GSIs (Global Secondary Indexes).
public async Task SaveOrUpdateStudent(Employee employee) {
await _context.SaveAsync(employee);
}
public async Task < Employee > GetEmployeeUsingHashKey(int id) {
return await _context.LoadAsync < Employee > (empId);
}
public async Task < Employee > ScanForEmployeeUsingFirstName(string firstName) {
var search = _context.ScanAsync < Employee >
(
new [] {
new ScanCondition
(
nameof(Employee.FirstName),
ScanOperator.Equal,
firstName
)
}
);
var result = await search.GetRemainingAsync();
return result.FirstOrDefault();
}
- SaveOrUpdateEmployee ---To save a new entity, execute SaveAsync. Keep in mind that SaveAsync will create or update the record; if the form already exists, the method execution will override the data on the matching record.
- GetEmployeeUsingHashKey ---This method will retrieve the records using the hash key.
- ScanForEmployeeUsingFirstName will scan the whole table and search for the first name.
Delete Table
[HttpDelete]
public async Task Delete(string FirstName) {
await _dynamoDbContext
.DeleteAsync < Employee > (empId, FirstName, LastName);
}
The DeleteAsync method deletes an item by specifying the partition and range key or using the Item itself.
Delete Item
Low-level Model
public async Task DeleteItem(string tableName) {
var request = new DeleteItemRequest {
TableName = tableName,
Key = new Dictionary < string, AttributeValue > {
{
"empId",
new AttributeValue {
N = "999"
}
}
},
};
await _dynamoDbClient.DeleteItemAsync(request);
}
Object Persistence Model
You Can map your client-side classes to Amazon DynamoDB tables using the AWS SDK for .Net object persistence mechanism. The Item in the appropriate tables is assigned to each object instance. The DynamoDBContext class, an entry point to DynamoDB, is provided by the object persistence model to save client-side objects to the tables. This class establishes a connection to DynamoDB, allowing you to view tables, execute CRUD actions, and run queries.
public async Task DeleteItem(ItemRequest itemRequest) {
await _context.DeleteAsync(itemRequest);
}
We can do the above using the same model, including the table name and partition key.
Prevent Overwriting Existing Records
public async Task SaveOnlyEmployee(Employee employee) {
var identityEventTable = Table.LoadTable(_amazonDynamoDBClient, "test_employee");
var expression = new Expression {
ExpressionAttributeNames = new Dictionary < string, string > {
{
"#key",
nameof(employee.empId)
},
},
ExpressionAttributeValues = {
{
":key",
employee.empId
},
},
ExpressionStatement = "attribute_not_exists(#key) OR #key <> :key",
};
var document = _context.ToDocument(employee);
await identityEventTable.PutItemAsync(document, new PutItemOperationConfig {
ConditionalExpression = expression,
ReturnValues = ReturnValues.None
});
}
We need to use conditional expressions in DynamoDB to prevent overrides. If we try to insert a record that already has a hash key in the table, a ConditionalCheckFailedException will be raised.
Scan
By specifying scan criteria, you can filter scan results. Any attribute in the table is used to evaluate the condition. For example, assume you have a client-side class called EmployeeCategory mapped to the DynamoDB employee table. The C# example below searches the table and returns only the employee IDs bigger than 10.
IEnumerable < Employee > itemsWithWrongId = context.Scan < Employee > (
new ScanCondition("empId", ScanOperator.LessThan, empId),
new ScanCondition("employeeCategory", ScanOperator.Equal, "Internal")
);
The Scan method returns an IEnumerable collection that has been "lazy-loaded." It returns only one page of results at first and then, if necessary, makes a service request for the following page. You only need to iterate through the IEnumerable to get all matching entries.
Get Items using Query Filter
Object Persistence Model
public async Task < IEnumerable < ItemRequest >> GetUsersItemsByName(int empId, string empname) {
var config = new DynamoDBOperationConfig {
QueryFilter = new List < ScanCondition > {
new ScanCondition("EmpName", ScanOperator.BeginsWith, empname)
}
};
return await _context.QueryAsync < ItemRequest > (empId, config).GetRemainingAsync();
}
Low-Level Model
public async Task < QueryResponse > GetItemsByName(int empId, string empname) {
var request = new QueryRequest {
TableName = TableName,
KeyConditionExpression = "Id = :empId and begins_with (Name,:empname)",
ExpressionAttributeValues = new Dictionary < string, AttributeValue > {
{
":id",
new AttributeValue {
N = id.ToString()
}
},
{
":name",
new AttributeValue {
S = empname
}
}
}
};
return await _dynamoDbClient.QueryAsync(request);
}
Put Item
Replaces an old item with a new item or creates a new item. If an item with the same primary key already exists in the provided database, the new Item entirely replaces it. You can insert a new item if the provided primary key doesn't exist or return an existing item if it contains particular attribute values.
You can use the returnValues option to return the Item's attribute values in the same action as inserting the Item.
The primary key attribute(s) are the only attributes required when adding an item. Null values are not allowed for attribute values. The length of string and binary type characteristics must be more significant than zero. There can't be any empty set type attributes. ValidationException will be thrown for requests with open values.
PutItem can either return a copy of the old Item (before the update) or a copy of the new Item (after the update).
public virtual void CreateAccountItem(AmazonDynamoDBClient ddbClient, string tableName, Account account) {
var putItemRequest = new PutItemRequest {
TableName = tableName,
Item = new Dictionary < string, AttributeValue > {
{
"Company",
new AttributeValue {
S = account.Company
}
},
{
"Email",
new AttributeValue {
S = account.Email
}
}
}
};
if (!String.IsNullOrEmpty(account.First)) {
putItemRequest.Item.Add("First", new AttributeValue {
S = account.First
});
}
if (!String.IsNullOrEmpty(account.Last)) {
putItemRequest.Item.Add("Last", new AttributeValue {
S = account.Last
});
}
if (!String.IsNullOrEmpty(account.Age)) {
putItemRequest.Item.Add("Age", new AttributeValue {
N = account.Age
});
}
ddbClient.PutItem(putItemRequest);
}
Get All Items
Let's find all the items on the table.
var employees = await GetAllEmployees();
Console.WriteLine(employees.PaginationToken);
Console.WriteLine(employees.ResultsType);
foreach(var emp in employees.Employees) {
Console.WriteLine($"{emp.EmailAddress} - {emp.empId} - {emp.Age} - {emp.JoinedOn} - Address: {emp.Address.Street}, {emp.Address.City}");
}
Get All Items (with Pagination)
The below code will show the functionality of the GetAllEmployees method. First, you need to get the model's table reference and check for a pagination token. You can use the paginationToken with scan option to fetch the following result set.
public static async Task < EmployeeViewModel > GetAllEmployees(string paginationToken = "") {
var table = context.GetTargetTable < Employee > ();
var scanOps = new ScanOperationConfig();
if (!string.IsNullOrEmpty(paginationToken)) {
scanOps.PaginationToken = paginationToken;
}
var results = table.Scan(scanOps);
List < Document > data = await results.GetNextSetAsync();
IEnumerable < Employee > employees = context.FromDocuments < Employee > (data);
return new EmployeeViewModel {
PaginationToken = results.PaginationToken,
Employees = employees,
ResultsType = ResultsType.List
};
}
Get Single Item
Console.WriteLine("Single Result");
var foundEmployee = await Single("100e4548-1f68-4288-972d-81133b6dacb1", Convert.ToDateTime("2021-01-01T06:43:18.014Z"));
if (foundEmployee != null) {
Console.WriteLine($"{foundEmployee.EmailAddress} - {foundEmployee.Id} - {foundEmployee.JoinedOn}");
}
public static async Task < Employee > Single(string EmployeeId, DateTime JoinedOn) {
return await context.LoadAsync < Employee > (EmployeeId, JoinedOn);
}
Update Item
public static async Task Update(string EmployeeId, EmployeeInputModel entity) {
var Employee = await Single(EmployeeId, entity.JoinedOn);
Employee.EmailAddress = entity.EmailAddress;
Employee.Username = entity.Username;
Employee.Name = entity.Name;
await context.SaveAsync < Employee > (Employee);
}
Batch Get Items
The code below pulls three items from the EmployeeDetails table in C#. The results elements are not necessarily in the same order as the primary keys you selected.
DynamoDBContext context = new DynamoDBContext(client);
var employeeBatch = context.CreateBatchGet < EmployeeDetails > ();
employeeBatch.AddKey(001);
employeeBatch.AddKey(002);
employeeBatch.AddKey(003);
employeeBatch.Execute();
Console.WriteLine(employeeBatch.Results.Count);
Employee emp1 = employeeBatch.Results[0];
Employee emp2 = employeeBatch.Results[1];
Employee emp3 = employeeBatch.Results[2];
When you try to retrieve multiple items from a database table using one request, you have to consider the following:
- You need to create an instance of the CreateBatchGet class.
- We need to specify the primary key list.
- When you call the Execute method, the response returns the items in the Result property.
Get Items from Multiple Tables
Follow these steps to fetch items from several tables:
- First, create an instance of the CreateBatchGet type for each type and pass in the primary key values you wish to get from each table.
- Using one of the following ways, create an instance of the MultiTableBatchGet class: - Using one of the BatchGet objects you created in the previous step, call the Combine function. - A list of BatchGet objects is used to create an instance of the MultiBatchGet type. - Pass your list of BatchGet objects to the CreateMultiTableBatchGet function of DynamoDBContext.
- Call MultiTableBatchGet's Execute function, which provides typed results in individual BatchGet objects.
The CreateBatchGet function is used in the following C# code to retrieve several items from the Employee and EmployeeWorkDetails databases.
var EmployeeBatch = context.CreateBatchGet < Employee > ();
EmployeeBatch.AddKey(101);
EmployeeBatch.AddKey(102);
var EmployeeWorkDetailsBatch = context.CreateBatchGet < EmployeeWorkDetails > ();
EmployeeWorkDetailsBatch.AddKey(101, "P1");
EmployeeWorkDetailsBatch.AddKey(101, "P2");
EmployeeWorkDetailsBatch.AddKey(102, "P3");
EmployeeWorkDetailsBatch.AddKey(102, "P1");
var employeeAndDetailSuperBatch = EmployeeWorkDetails.Combine(EmployeeWorkDetails);
employeeAndDetailSuperBatch.Execute();
Console.WriteLine(EmployeeBatch.Results.Count);
Console.WriteLine(EmployeeWorkDetailsBatch.Results.Count);
Employee emp1 = EmployeeBatch.Results[0];
Employee emp2 = EmployeeWorkDetailsBatch.Results[1];
EmployeeWorkDetail EmployeeWorkDetail = EmployeeWorkDetailsBatch.Results[0];
Batch Write Items
This section will discuss Putting and deleting multiple items in a batch
write operation. To put or delete in batch write, you need to consider
the following.
- First, execute the CreateBatchWrite method in the DynamoDB an create the BatchWrite class.
- Mention the items you need to put or delete. For that:
- If you put items, use the AddPutItem method or the AddPutItems method.
- If you delete an item, You have to specify either the Item's primary key or the client-side object that maps to the Item you need to delete. Use the AddDeleteItem, AddDeleteItems, and the AddDeleteKey methods to specify the list of items to delete.
- You can call BatchWrite.Execute, a method to put or delete specific items in the table.
DynamoDBContext context = new DynamoDBContext(client);
var bookBatch = context.CreateBatchWrite < Employee > ();
Employee emp1 = new Employee {
EmpId = 101,
EmployeeCategory = "Internal",
Description = "Internal EMployee 1"
};
Employee emp2 = new Employee {
EmpId = 102,
EmployeeCategory = "Internal",
Description = "Internal EMployee 2"
};
bookBatch.AddPutItems(new List < Employee > {
emp1,
emp2
});
employeeBatch.AddDeleteKey(111);
employeeBatch.Execute();
Run Transactions
The following code snippet illustrates how to run the transactions in DynamoDB.
Collection < TransactWriteItem > actions = Arrays.asList(
new TransactWriteItem().withConditionCheck(checkEmployeeValid),
new TransactWriteItem().withUpdate(markItemSold),
new TransactWriteItem().withPut(createOrder));
TransactWriteItemsRequest placeOrderTransaction = new TransactWriteItemsRequest()
.withTransactItems(actions)
.withReturnConsumedCapacity(ReturnConsumedCapacity.TOTAL);
try {
client.transactWriteItems(placeOrderTransaction);
System.out.println("Transaction Successful");
} catch (ResourceNotFoundException rnf) {
System.err.println("One of the table involved in the transaction is not found" + rnf.getMessage());
} catch (InternalServerErrorException ise) {
System.err.println("Internal Server Error" + ise.getMessage());
} catch (TransactionCanceledException tce) {
System.out.println("Transaction Canceled " + tce.getMessage());
}
Run DynamoDB Local
.NET Core loads application configuration from a specified sequence of sources by default. In our situation, when the program begins in the development environment (the default when debugging from Visual Studio), the settings from this extra JSON file will supersede earlier configuration values. For example, the "LocalMode" has been set to true, and the service URL for the local DynamoDB instance is added.
We've selected the value to "http://localhost:8000", but you can change it if you want to run the container on a different host port.
The following code shows how to register the DynamoDB service.
public void ConfigureServices(IServiceCollection services) {
var dynamoDbConfig = Configuration.GetSection("DynamoDb");
var runLocalDynamoDb = dynamoDbConfig.GetValue < bool > ("LocalMode");
if (runLocalDynamoDb) {
services.AddSingleton < IAmazonDynamoDB > (sp => {
var clientConfig = new AmazonDynamoDBConfig {
ServiceURL = dynamoDbConfig.GetValue < string > ("LocalServiceUrl")
};
return new AmazonDynamoDBClient(clientConfig);
});
} else {
services.AddAWSService < IAmazonDynamoDB > ();
}
services.AddMvc().SetCompatibilityVersion(CompatibilityVersion.Version_2_1);
}
We can manually register a service for the IAmazonDynamoDB interface if the LocalMode is true. We utilize the overload to offer an implementation factory that will return the actual implementation in this case. We use the LocalServiceUrl from configuration to build an AmazonDynamoDBConfig instance in our scenario. We then use that configuration to create and return the AmazonDynamoDBClient. We've set this up as a singleton, which means this code will only run once when the service is used.
If LocalMode is false, we need to utilize the AWS SDK helper method AddAWSService to add the service because we added the AWSSDK.Extensions.NETCore.Setup package.
If you want to see your local tables and data in them, you can use Dynobase to query and modify items in offline tables.
Learn more about running DynamoDB locally.
