querying complex type in Azure Table

Question

I am new to NoSql concepts. Coming from a mind set of "ModelFirst" i usually design my models first. I have a model as follows.

public class Book
{
    public string Name { get; set; }
    [EntityPropertyConverter(typeof(Category))]
    public List<Category> Categories { get; set; }
}
public class Category
{
    public string Name { get; set; }
}

Student class is a complex type and

[EntityPropertyConverter]

attribute helps serialize Categories before writing to Azure Table and deserialize when reading back.

A book can belong to more than one categories. My question is if someone has to search a book by one of n number of categories, how can we do that.

Azure Table supports quires like below

TableQuery.GenerateFilterCondition("Categories", QueryComparisons.Equal, JsonConvert.SerializeObject(???))

But with this i cannot get the required result.

please do comment if my overall approach is incorrect.

Possible duplicate of [Create a TableEntity with Array or List property?](https://stackoverflow.com/questions/45517481/create-a-tableentity-with-array-or-list-property) — Zhaoxing Lu, May 04 '18 at 02:44
I don't find an attribute type EntityPropertyConverter in Azure Storage SDK, where did you get it? Besides, I'd suggest you to take a look at my answer in the question above. — Zhaoxing Lu, May 04 '18 at 02:45
Converter is custom implementation using reflection. Ill check your answet — Raas Masood, May 04 '18 at 02:49

score 1 · Answer 1 · answered May 28 '18 at 09:34

querying complex type in Azure Table

Based on my understanding, it is not supported currently. If we want to save the complex type data to the table storage, we need to SerializeObject to string and store it to the table storage

According to supported table comparison operators, we could know that it doesn't support contains. It seems that we have no way to filter the expected result.

If Azure cosmos DB(table API) is possible, I recommand that you could have a try.

Document format:

{
    "id": "test",
    "Book": {
        "Categories": [
            {
                "Name": "Category1"
            },
            {
                "Name": "Category2"
            }
        ],
        "Name": "book1"
    }
}

Query string:

SELECT * FROM root WHERE (ARRAY_CONTAINS(root.Book.Categories, {"Name": "Category2"}, true)or ARRAY_CONTAINS(root.Book.Categories, {"Name": "Category4"}, true))

Query from Azure portal:

score 0 · Answer 2 · answered May 31 '18 at 09:25

There is a way to write and query complex objects to table storage which is TableEntity.Flatten method in the SDK to flatten your object first. TableEntity.ConvertBack method to read it back and convert to original complex object. It does not do a full serialization, it keeps native properties as EntityProperty on the flattened object so each property can be queried individually.

That said the IEnumerable/ICollection type of properties are not supported by current version of TableEntity.Flatten api. However, the nuget package Object Flattener Recomposer version 2.0 supports collection/enumerable/indexed type properties:

https://www.nuget.org/packages/ObjectFlattenerRecomposer/

So you can use this nuget instead to write your complex types to table storage if your objects have those type of indexed/enumerable type properties. This nuget package does the same thing as the api s in the SDK (it is the original code behind the TableEntity.Flatten / ConvertBack methods ) but in addition it will convert enumerable/collection type properties into json strings before writing to table storage and it does creating the original complex object back in reading from the table transparently, you do not need to worry about deserializing flattened object back to the original complex type.

Coming back to your original question it is possible to write and read complex objects to table storage either by the methods I mentioned in the SDK or the latest nuget package of ObjectFlattenerRecomposer api. Though if you want to query an individual object from the List type property it will be tricky still because the List as I mentioned will be in the format of a json string on the table.

querying complex type in Azure Table

2 Answers2