Tuesday, March 12, 2013

Using Azure Table Storage with dynamic table entities

I've been working with Windows Azure for a few months now and I was trying to figure out a way to use the Azure Table Storage Service with POCOs and complex types rather than only classes inheriting from the TableEntity base class. Turns out that the only thing the CloudTableClient cares about is the ITableEntity interface. DynamicTableEntity also implements ITableEntity but is only used for querying and updating entities. You can see it in action in the example on how to query only a subset of an entity's properties.

So I started to wonder if it was possible to create class that implements ITableEntity and offer the dynamic features of an ExpandoObject. After a bit of hacking around in LinqPad I have this solution.



In this snippet I also implemented the ICustomMemberProvider which is part of the LinqPad extensions API for queries (more on this here). In Visual Studio we'll need to remove that code.

We can now use the ElasticTableEntity class like this:



Please note that you need to use the dynamic keyword to be able to define properties dynamically. You can also use the entity indexer like I did with the LastName property.

Result
List<ElasticTableEntity> (1 item)
PartitionKeyRowKeyTimestampETagFirstNameNumberBoolDateTokenIdLastName
Partition12325203917875897660732013-03-13 1:00:40 AM +00:00W/"datetime'2013-03-13T01%3A00%3A40.619873Z'"Pascal34False1912-03-04 12:00:00 AM +00:0050604c02-f01c-48fc-862e-7ea66153f434Laurin
Result with projection
List<ElasticTableEntity> (1 item)
PartitionKeyRowKeyTimestampETagDateFirstName
Partition12325203917875897660732013-03-13 1:00:40 AM +00:00W/"datetime'2013-03-13T01%3A00%3A40.619873Z'"1912-03-04 12:00:00 AM +00:00Pascal

The ElasticTableEntity allows us to define properties at run time which will be added to the table when inserting the entities. Tables in the Azure Table Storage have flexible schema so we are free to store entities with different properties as long a we respect some limitations:
  • Entities can have no more than 252 different properties (that's for the Table)
  • An Entity's data can be up to 1 MB in size
  • A property must be one of the following types : byte[], bool, DateTime, double, Guid, int, long or string
  • A property value can be up to 64 KB in size (for string and byte array)
  • A property name is case sensitive and can be no more than 255 characters in length
You can store about any kind of data as long as it is one of the supported data type.  You could also encode other kind of date type in a byte array or a string (like a json document).  Just be careful to always stick to one data type for a property (yes, we can store like int, bool and string in the same column using different entities!)

That's it for now. Next time I'll show you how to use the Windows Azure Table Storage Service as a document-oriented database with the ElasticTableEntity.



See also

- Document oriented database with Azure Table Storage Service
- Using Azure Blob Storage to store documents