Search data in Cloud table azure storage

How to search any text in azure cloud table, as we have learned cloud table structure in different than sql table, so search function will be little different, we will be using LINQ with some additional function to search any text in our azure cloud table.

If you are working with azure cloud table for the first time, better you read following two post about adding data and curd operations.

• Create Cloud table and insert data in azure storage
• Cloud table CRUD operation in azure storage

To search any text in cloud table we have to write a filter clause string fileterClause = TableQuery.GenerateFilterCondition("Firstname", QueryComparisons.Equal, searchText);

And then we use that filter clasue, which is a string type, with a table query where clasue like _query.Where(fileterClause).AsTableQuery();

Following method will return a list of customers where first name match the string we are passing to the method.

using Microsoft.Azure.Cosmos.Table;
using Microsoft.Azure.Cosmos.Table.Queryable;


async Task<List<Customer>> SearchCustomers(string searchText)
{
string _message = "Search customer table";
	
string _storageConnection = _configuration.GetSection("AzureStorage")["ConnectionString"];
var storageAccount = CloudStorageAccount.Parse(_storageConnection);
	
try
{
var tableClient = storageAccount.CreateCloudTableClient(new TableClientConfiguration());
var customerTable = tableClient.GetTableReference("custTable");
		
// notice, how to ignore case, by calling ToLowerInvariant()
string fileterClause = TableQuery.GenerateFilterCondition("Firstname", QueryComparisons.Equal, searchText);
					   
TableQuery<Customer> _query = new TableQuery<Customer>();
_query = _query.Where(fileterClause)
			.AsTableQuery();

IEnumerable<Customer> _result = customerTable.ExecuteQuery(_query);
		if (_result != null)
			SearchedCustomers = _result.ToList();
}
catch (StorageException e)
{
	_message = e.ToString();
}
catch (RequestFailedException ex)
{
	_message = ex.ToString();
}
    return await Task.FromResult(SearchedCustomers);
}

Multiple where clause in cloud table

Now we see how to write multiple conditions in where clause, suppose we want to search for matching first and last name of customers.

As we know cloud table does not support relational query, it’s more like no-sql, so writing multiple where clause in cloud table would be different that sql query.

TableQuery has a method called CombineFilters, where we can combine two filters

string fileter1 = TableQuery.GenerateFilterCondition("Firstname", 
                   QueryComparisons.Equal, searchText);

string fileter2 = TableQuery.GenerateFilterCondition("Lastname",
   QueryComparisons.NotEqual, searchText);

string filterFinal = TableQuery.CombineFilters(fileter1, TableOperators.Or, fileter2);

TableQuery<Customer> _query = new TableQuery<Customer>();
_query = _query.Where(filterFinal)
	.AsTableQuery();

TableOperators has all enums like And Or etc

Now you may wonder what if I want to write more than two where clauses, as we can see in above example TableQuery.CombineFilters method accept only three parameters.

Solution is writing TableQuery.CombineFilters within another TableQuery.CombineFilters with TableOperators Or And etc.