CRUD Operations with Azure Table Storage in an Azure Function – D

In the previous part of this series we briefly described how to Create, Retreive and Update records in an Azure Table Storage using Azure Web Functions. In this part, we will look into final part of quadrant – the Delete operations.

As you would have guessed after going through previous posts in this series, we would be using the CloudTable for deletion of record.

[FunctionName("DeleteRecord")]
public static async Task<IActionResult> DeleteEntity(
    [HttpTrigger(AuthorizationLevel.Anonymous,"POST", Route = "DeleteRecord/{partitionKey}/{rowKey}")] HttpRequest request,
    [Table("todos", "{partitionKey}", "{rowKey}")] TodoTableEntity tableEntity,
    [Table("todos")] CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("Request to delete the record");

    var deleteOperation = TableOperation.Delete(tableEntity);
    var result = await todoTable.ExecuteAsync(deleteOperation);
    return new OkObjectResult(result);
}

In this above example, we are using Bindings to retrieve the record to be deleted. The PartitionKey and RowKey is retrieved from the query string, quite similiar to the method we learned in the Retrieve record post.

A word of caution here. It is tempting to retrieve the record using RowKey alone, but one needs to be aware of the consequences. If one was to attempt retrival/deletion based on RowKey alone, the whole table has to be scanned for the record. Furthermore, it is theoratical to have the same RowKey over different patition (though this could be averted by application logic), which would retrieve multiple entites instead of one.

Deletion Table Binding

Of course, you could achieve the above without Table Binding.

[FunctionName("DeleteWithoutBinding")]
        public static async Task<IActionResult> DeleteWithoutBinding(
        [HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest request,
        [Table("todos")] CloudTable todoTable,
         ILogger log)
{
    log.LogInformation("Request to delete Record without binding");

    string partitionKey = request.Query["pkey"];
    string rowKey = request.Query["rkey"];

    var tableQuery = new TableQuery<TodoTableEntity>();

    var filterRowKeyAndPartitionKey = TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition(nameof(TodoTableEntity.RowKey), QueryComparisons.Equal, rowKey),
        TableOperators.And,
        TableQuery.GenerateFilterCondition(nameof(TodoTableEntity.PartitionKey), QueryComparisons.Equal, partitionKey));

    tableQuery.FilterString = TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition(nameof(TableEntity.PartitionKey), QueryComparisons.NotEqual, "Key"),
        TableOperators.And,
        filterRowKeyAndPartitionKey);

    var itemToDelete = todoTable.ExecuteQuery(tableQuery).First();

    var deleteOperation = TableOperation.Delete(itemToDelete);
    var deleteResponse = await todoTable.ExecuteAsync(deleteOperation);
    return new OkObjectResult(deleteResponse);
}

But honestly, I feel that is quite a lot of boilerplate code which could be avoided using bindings. I strongly suggest you use binding unless you have very strong reasons of doing otherwise.

Well, so far we have seen CRUD operations using the Azure Table Storage. In the next part of this series, we will explore another medium of storage provided by Azure. Until then, enjoy coding.

Azure Storage with Azure Functions

In this series of Articles, we will explore various Azure Storage options using Azure Functions. We will stick to the basic usage, leaving out the more advanced topics involved with the storage to later blog posts.

CRUD using Azure Table Storage

CRUD using Azure Blob Storage

CRUD using Azure Queue Storage

CRUD Operations with Azure Table Storage in an Azure Function – U

In the earlier posts, we enlightened ourselves with creation and retrieval of records from Azure Table Storage using Azure Web Functions. In this segment, we will attempt to update a record.

Let us once again look at our table before proceeding further.

In the first approach, we will attempt to update the record based on the RowKey and PartitionKey provided by the request. Let us go ahead and write our method now.

[FunctionName("Update")]
public static async Task<IActionResult> Update(
    [HttpTrigger(AuthorizationLevel.Anonymous,"post",Route = null)] HttpRequest request,
    [Table("todos")]CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("Request to update Record");

    string requestBody = await new StreamReader(request.Body).ReadToEndAsync();
    var data = JsonConvert.DeserializeObject<TodoDto>(requestBody);

    var rowKeyToUpdate= request.Query[nameof(TableEntity.RowKey)];
    var partitionKeyToUpdate= request.Query[nameof(TableEntity.PartitionKey)];

    var tableEntity = new TodoTableEntity
    {
        RowKey = rowKeyToUpdate,
        PartitionKey = partitionKeyToUpdate,
        Title = data.Title,
        Description = data.Description,
        IsCompleted = data.IsCompletd,
        ETag = "*"
    };

    var updateOperation = TableOperation.Replace(tableEntity);
    var result = await todoTable.ExecuteAsync(updateOperation);
    return new OkObjectResult(result);
}

I will skip the Bindings here since we have learned about the same in the previous posts. We will first create an instance of record which will have the new value.

var tableEntity = new TodoTableEntity
{
    RowKey = rowKeyToUpdate,
    PartitionKey = partitionKeyToUpdate,
    Title = data.Title,
    Description = data.Description,
    IsCompleted = data.IsCompletd,
    ETag = "*"
};

Notice that we have used the PartitionKey and Rowkey retrieved from our query (we will address how to fetch it from database, if not given in query later). The other important point to notice here is the presence of the ETag. The Update Table Operation will not be allowed to process without the ETag.

As the next step, we will use the TableOperation to update or replace our record. This turns out to be pretty easy, thanks to the rich collection of methods introduced by Microsoft.

var updateOperation = TableOperation.Replace(tableEntity);
var result = await todoTable.ExecuteAsync(updateOperation);

Now you are ready to hit F5 and test your web function. For example, let us consider the following input.

// Request
http://localhost:7071/api/Update?PartitionKey=L&RowKey=1001

//Body
{
    title:'Learn Azure Func',
    description:'Learn Azure Func with Table Storage',
    isCompleted:'True'
}

We want to flip the IsCompleted Flag to True here. Final result would be.

We could, optimize the code a bit with help of the Bindings we have learned in the previous post. For example, the above code could be rewritten as,

[FunctionName("UpdateUsingBinding")]
public static async Task<IActionResult> UpdateUsingBinding(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = "UpdateUsingBinding/{partitionKey}/{rowKey}")] HttpRequest request,
[Table("todos", "{partitionKey}", "{rowKey}")] TodoTableEntity tableEntity,
[Table("todos")] CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("Request to update Record");

    string requestBody = await new StreamReader(request.Body).ReadToEndAsync();
    var data = JsonConvert.DeserializeObject<TodoDto>(requestBody);

    tableEntity.Title = data.Title;
    tableEntity.Description = data.Description;
    tableEntity.IsCompleted = data.IsCompletd;

    var updateOperation = TableOperation.Replace(tableEntity);
    var result = await todoTable.ExecuteAsync(updateOperation);
    return new OkObjectResult(result);
}

As you could notice, we have made using of the Table Bindings to retrieve the record here, which is then updated with new values passed on from the Http Request.

In the last approach for the post, we will address the case when PartitionKey and RowKey is not known to the HttpRequest and we need to fetch it using the Title. Well, as you might have guessed it, it would involve retrieval we learned in the previous posts.

[FunctionName("UpdateWithRetrival")]
public static async Task<IActionResult> UpdateWithRetrival(
[HttpTrigger(AuthorizationLevel.Anonymous, "post", Route = null)] HttpRequest request,
[Table("todos")] CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("Request to update Record");

    string requestBody = await new StreamReader(request.Body).ReadToEndAsync();
    var data = JsonConvert.DeserializeObject<TodoDto>(requestBody);

    var tableQuery = new TableQuery<TodoTableEntity>();

    tableQuery.FilterString = TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition(nameof(TableEntity.PartitionKey), QueryComparisons.NotEqual, "Key"),
        TableOperators.And,
        TableQuery.GenerateFilterCondition(nameof(TodoTableEntity.Title), QueryComparisons.Equal, data.Title));
    var result = todoTable.ExecuteQuery(tableQuery);

    var itemToUpdate = result.First();
    itemToUpdate.Description = data.Description;
    itemToUpdate.IsCompleted = data.IsCompletd;

    var updateOperation = TableOperation.Replace(itemToUpdate);
    var updateResponse = await todoTable.ExecuteAsync(updateOperation);
    return new OkObjectResult(updateResponse);
}

As you can observe, thanks to the rich API provided by Microsoft, the basic operations with Azure Storage are pretty straightforward. We will address Deletion in the next post, until then, have a great day.

CRUD Operations with Azure Table Storage in an Azure Function – R

In an earlier post, we discussed how to insert a new item in the Azure Storage Table. In this article, we will delve into how to retrieve data from Azure Storage Table.

Retrieve a Single Entity

In the previous article, we had partitioned the entities based on their first letter of their Title. Here is how our table looked at end our insert operations.

As mentioned before, we would use CloudTable to retrieve the entity we desire. For the sake of example, let us assume that the RowKey of the desired record would be passed via QueryString in the Http Request.

[FunctionName("TodoGetOne")]
public static async Task<IActionResult> GetOne(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
[Table("todos")] CloudTable todoTable,
ILogger log)
{
    string id = req.Query["id"];

    var tableQuery = new TableQuery<TodoTableEntity>();

    tableQuery.FilterString = TableQuery.CombineFilters(
        TableQuery.GenerateFilterCondition(nameof(TableEntity.PartitionKey), QueryComparisons.NotEqual, "Key"), 
        TableOperators.And, 
        TableQuery.GenerateFilterCondition(nameof(TableEntity.RowKey), QueryComparisons.Equal, id));

    var result = todoTable.ExecuteQuery(tableQuery);
    return new OkObjectResult(result);
}

As you can observe, we have used the TableQuery to filter our desired entity.

var tableQuery = new TableQuery<TodoTableEntity>();

tableQuery.FilterString = TableQuery.CombineFilters(
    TableQuery.GenerateFilterCondition(nameof(TableEntity.PartitionKey), QueryComparisons.NotEqual, "Key"), 
    TableOperators.And, 
    TableQuery.GenerateFilterCondition(nameof(TableEntity.RowKey), QueryComparisons.Equal, id));

The TableQuery.FilterString property enables us to provide Custom filters for the query. This is further facilitated by supporting utility methods in TableQuery, such as CombineFilters, and GeneratedFilterCondition. In our case, as seen in code above, we are filtering the data where the PartitionKey is not equal to text Key (remember, the partition key in our example is a special partition which keeps track of next Primary Id available ) and RowKey equavalent to the Id passed via querystring.

Further, we execute the query using the CloudTable.ExecuteQuery method.

var result = todoTable.ExecuteQuery(tableQuery);

That’s all you would need to fetch the data from Azure Table Storage.

Retrieve One Entity Using Binding

Table Binding also helps us retrieve a single record skipping a lot of boiler plate code, provided we know the parition key and the row key. For example, consider the following code.

[FunctionName("TodoGetOneBinding")]
public static async Task<IActionResult> TodoGetOneBinding1(
[HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = "TodoGetOneBinding/{partition}/{id}")] HttpRequest req,
[Table("todos", "{partition}", "{id}")] TodoTableEntity todo,
ILogger log)
{
    return new OkObjectResult(todo);
}

This effectively does the same as the code in our previous example, but provides a less cluttered code. The {parition} and {id} parameters from the Route is used as parameters for filter the table here.

Retrieve Multiple Entities

I guess there is very little to explain here once we have done the above examples. So let us write the code straightaway for retrieve all the todo items

[FunctionName("TodoGetAll")]
public static async Task<IActionResult> GetAll(
    [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
    [Table("todos")] CloudTable todoTable,
    ILogger log)
{
    var tableQuery = new TableQuery<TodoTableEntity>();
    tableQuery.SelectColumns = new List<string> { nameof(TodoTableEntity.Description) };
    tableQuery.FilterString = TableQuery.GenerateFilterCondition(nameof(TableEntity.PartitionKey), QueryComparisons.NotEqual, "Key");

    var result = todoTable.ExecuteQuery(tableQuery);
    return new OkObjectResult(result);
}

That’s all for now. We will continue our journey exploring the Azure Cloud Storage in upcoming articles.

CRUD Operations with Azure Table Storage in an Azure Function – C

In this series of byte sized tutorials, we will create an Azure Function for Crud Operations on an Azure Storage Table. For the demonstration, we will stick a basic web function, which would enable us to do the CRUD operations for a TODO table.

The reason to pick Azure Storage table is primarly it is extremely cost efficient and you could also emulate the storage within your development environment.That’s true, you do not need even a Azure subscription to try out Azure Storage, thanks the Storage Emulator.

One of the key points to remember before we proceed is how an Entity is identified uniquely in a Azure Table Storage. Partitions allows scaling of the system easily and whenever you store an item in the table, it is stored in a partition, which is scaled out in the system. The PartionId allows to uniquely identify the partition in which the data resides. The RowId, uniquely identifies the specific entity within the Partition and together with ParitionKey forms the composite key that would be unique identifier for your entity.

We will get back to this a bit later. But for now, we will define our Entity derieved from TableEntity.

using Microsoft.Azure.Cosmos.Table;
public class TodoTableEntity : TableEntity
{
    public string Title { get; set; }
    public string Description { get; set; }
    public bool IsCompleted { get; set; }
}

We require 3 columns in our Table in addition to the PartionKeyRowKey, and TimeStamp.

We will begin by writing the basic skeleton code for our web function and go through the key components, before the insert operation.

[FunctionName("TodoAdd")]
public static async Task<IActionResult> Add(
    [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
    [Table("todos")] CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("C# HTTP trigger function processed a request.");

    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var data = JsonConvert.DeserializeObject<TodoDto>(requestBody);

    // TODO

    return new OkObjectResult(0);
}

public class TodoDto
{
    public string Title { get; set; }
    public string Description { get; set; }
    public bool IsCompletd { get; set; }
}

The function defined above (TodoAdd) accepts a HttpTrigger (both Get and Post requests). The data to be inserted is passed via the Request Body. We will use the HttpRequest.Body property to read the information and deserialize them. This is quite evident in the code above. What is of more interest at this point is the todoTable parameter.

The todoTable parameter, of type CloudTable represents a table in Microsoft Azure Table Service and provides us all the methods required to access the table. The bindings specify that the table is named todos.

Now that we have our data to be deserialized and the table name, we would proceed to insert the data in the table.

var dataToInsert = new TodoTableEntity
{
    Title = data.Title,
    Description = data.Description,
    IsCompleted = data.IsCompletd,
    PartitionKey = data.Title[0].ToString(),
    RowKey = data.Title[0].ToString()
};


var addEntryOperation = TableOperation.Insert(dataToInsert);
todoTable.CreateIfNotExists();
await todoTable.ExecuteAsync(addEntryOperation);


As mentioned earlier the CloudTable provides us with all the necessary ammuniation to access the table. In this case, we would be using the CloudTable.ExecuteAsync method to execute a TableOperation to insert the record.

However, the following code has a serious flaw, which we will discuss in a moment. Consider the Entity we are about to insert.

var dataToInsert = new TodoTableEntity
{
    Title = data.Title,
    Description = data.Description,
    IsCompleted = data.IsCompletd,
    PartitionKey = data.Title[0].ToString(),
    RowKey = data.Title[0].ToString() // This causes an error
};

After filling the Title,Description and IsCompleted fields from the data we recieved from the Http Request, we are also assigning the PartitionKey and RowKey to the entity. We have decided, for the sake of example, to partition the table based the first alphabet of the Title. This works fine – we would end up with multiple partitions. However, the RowKey would cause an issue. Consider the following two requests.

// First Request
{
    title:'A Test',
    description:'A Test Description`,
    isCompleted:'false`
}
// Second Request
{
    title:'Another Test',
    description:'A Test Description`,
    isCompleted:'false`
}

Both these request would have PartitionKey value as “A” according to the code we wrote above. This is fine, as would want to group all the Entities with title starting with “A” in the same partition. However, the above code would also result in both the RowId to be same as well. This leads to an error as these needs to be separate entities and cannot share the same combination of PartitionKey and RowId.

For this reason, we will need a unique Id to identify the RowId. In this example, we will use a simple technique in which we will create another partition, namely Key, which would contain a single Row. This Row would contain a numerical value which we would use as the Identity value to be used in the table. With each request, we would also need to update the key.

So let us rewrite the code again to make use of the Key entity.

[FunctionName("TodoAdd")]
public static async Task<IActionResult> Add(
    [HttpTrigger(AuthorizationLevel.Function, "get", "post", Route = null)] HttpRequest req,
    [Table("todos","Key","Key",Take =1)] TodoKey keyGen,
    [Table("todos")] CloudTable todoTable,
    ILogger log)
{
    log.LogInformation("C# HTTP trigger function processed a request.");

    string name = req.Query["name"];

    string requestBody = await new StreamReader(req.Body).ReadToEndAsync();
    var data = JsonConvert.DeserializeObject<TodoDto>(requestBody);

    if (keyGen == null)
    {
        keyGen = new TodoKey
        {
            Key = 1000,
            PartitionKey = "Key",
            RowKey = "Key"
        };

        var addKeyOperation = TableOperation.Insert(keyGen);
        await todoTable.ExecuteAsync(addKeyOperation);
    }

    var rowKey = keyGen.Key;

    var dataToInsert = new TodoTableEntity
    {
        Title = data.Title,
        Description = data.Description,
        IsCompleted = data.IsCompletd,
        PartitionKey = data.Title[0].ToString(),
        RowKey = keyGen.Key.ToString()
    };

    keyGen.Key += 1;
    var updateKeyOperation = TableOperation.Replace(keyGen);
    await todoTable.ExecuteAsync(updateKeyOperation);
    var addEntryOperation = TableOperation.Insert(dataToInsert);
    todoTable.CreateIfNotExists();
    await todoTable.ExecuteAsync(addEntryOperation);

    return new OkObjectResult(keyGen.Key);
}

As observed in the code above, we have introduced a new parameter keyGen, which points to the new entity in the same todos table. We have used the bindings to specify the ParitionKey and RowKey to fetch the entity for us.

We then increment the Key, and use it as the RowId for rest of our entities. The resultant table storage would look like following.

In this example, we have create a simple Create Operation for Azure Table Storage. We will explore more into the Azure Bindings and rest of CRUD Operations in rest of the series, but hope this provides a good starting point for learning Azure Storage with Azure functions.