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.

	public class ElasticTableEntity : DynamicObject, ITableEntity,
	ICustomMemberProvider // For LinqPad's Dump
	{
	public ElasticTableEntity()
	{
	this.Properties = new Dictionary<string, EntityProperty>();
	}
	public IDictionary<string, EntityProperty> Properties { get; private set; }
	public object this[string key]
	{
	get
	{
	if (!this.Properties.ContainsKey(key))
	this.Properties.Add(key, this.GetEntityProperty(key, null));
	return this.Properties[key];
	}
	set
	{
	var property = this.GetEntityProperty(key, value);
	if (this.Properties.ContainsKey(key))
	this.Properties[key] = property;
	else
	this.Properties.Add(key, property);
	}
	}
	#region DynamicObject overrides
	public override bool TryGetMember(GetMemberBinder binder, out object result)
	{
	result = this[binder.Name];
	return true;
	}
	public override bool TrySetMember(SetMemberBinder binder, object value)
	{
	this[binder.Name] = value;
	return true;
	}
	#endregion
	#region ITableEntity implementation
	public string PartitionKey { get; set; }
	public string RowKey { get; set; }
	public DateTimeOffset Timestamp { get; set; }
	public string ETag { get; set; }
	public void ReadEntity(IDictionary<string, EntityProperty> properties, OperationContext operationContext)
	{
	this.Properties = properties;
	}
	public IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
	{
	return this.Properties;
	}
	#endregion
	#region ICustomMemberProvider implementation for LinqPad's Dump
	public IEnumerable<string> GetNames()
	{
	return new[] { "PartitionKey", "RowKey", "Timestamp", "ETag" }
	.Union(this.Properties.Keys);
	}
	public IEnumerable<Type> GetTypes()
	{
	return new[] { typeof(string), typeof(string), typeof(DateTimeOffset), typeof(string) }
	.Union(this.Properties.Values.Select(x => this.GetType(x.PropertyType)));
	}
	public IEnumerable<object> GetValues()
	{
	return new object[] { this.PartitionKey, this.RowKey, this.Timestamp, this.ETag }
	.Union(this.Properties.Values.Select(x => this.GetValue(x)));
	}
	#endregion
	private EntityProperty GetEntityProperty(string key, object value)
	{
	if (value == null) return new EntityProperty((string)null);
	if (value.GetType() == typeof(byte[])) return new EntityProperty((byte[])value);
	if (value.GetType() == typeof(bool)) return new EntityProperty((bool)value);
	if (value.GetType() == typeof(DateTimeOffset)) return new EntityProperty((DateTimeOffset)value);
	if (value.GetType() == typeof(DateTime)) return new EntityProperty((DateTime)value);
	if (value.GetType() == typeof(double)) return new EntityProperty((double)value);
	if (value.GetType() == typeof(Guid)) return new EntityProperty((Guid)value);
	if (value.GetType() == typeof(int)) return new EntityProperty((int)value);
	if (value.GetType() == typeof(long)) return new EntityProperty((long)value);
	if (value.GetType() == typeof(string)) return new EntityProperty((string)value);
	throw new Exception("not supported " + value.GetType() + " for " + key);
	}
	private Type GetType(EdmType edmType)
	{
	switch (edmType)
	{
	case EdmType.Binary : return typeof(byte[]);
	case EdmType.Boolean : return typeof(bool);
	case EdmType.DateTime : return typeof(DateTime);
	case EdmType.Double : return typeof(double);
	case EdmType.Guid : return typeof(Guid);
	case EdmType.Int32 : return typeof(int);
	case EdmType.Int64 : return typeof(long);
	case EdmType.String : return typeof(string);
	default: throw new Exception("not supported " + edmType);
	}
	}
	private object GetValue(EntityProperty property)
	{
	switch (property.PropertyType)
	{
	case EdmType.Binary : return property.BinaryValue;
	case EdmType.Boolean : return property.BooleanValue;
	case EdmType.DateTime : return property.DateTimeOffsetValue;
	case EdmType.Double : return property.DoubleValue;
	case EdmType.Guid : return property.GuidValue;
	case EdmType.Int32 : return property.Int32Value;
	case EdmType.Int64 : return property.Int64Value;
	case EdmType.String : return property.StringValue;
	default: throw new Exception("not supported " + property.PropertyType);
	}
	}
	}

view raw ElasticTableEntity.cs hosted with ❤ by GitHub

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:

	var connectionString="...";
	CloudStorageAccount storageAccount = CloudStorageAccount.Parse(connectionString);
	var client = storageAccount.CreateCloudTableClient();
	var table = client.GetTableReference("Demo");
	table.CreateIfNotExists();
	// dynamic keyword to use a dynamic entity
	dynamic entity = new ElasticTableEntity();
	entity.PartitionKey = "Partition123";
	entity.RowKey = (DateTime.MaxValue.Ticks - DateTime.Now.Ticks).ToString();
	entity.Name = "Pascal";
	entity.Number = 34;
	entity.Bool = false;
	entity.Date = new DateTime(1912, 3, 4);
	entity.TokenId = Guid.NewGuid();
	entity["LastName"] = "Laurin";
	// Insert the entity we created dynamically
	table.Execute(TableOperation.Insert(entity));
	// Query all entities in the table
	var query = new TableQuery<ElasticTableEntity>();
	var result = table.ExecuteQuery(query)
	.ToList().Dump("Result");
	// Query only a subset of properties
	var result2 = table.ExecuteQuery(query.Select(new[] { "FirstName", "Date" }))
	.ToList().Dump("Result with projection");

view raw ElasticTableEntityUsage.cs hosted with ❤ by GitHub

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) PartitionKey RowKey Timestamp ETag FirstName Number Bool Date TokenId LastName Partition123 2520391787589766073 2013-03-13 1:00:40 AM +00:00 W/"datetime'2013-03-13T01%3A00%3A40.619873Z'" Pascal 34 False 1912-03-04 12:00:00 AM +00:00 50604c02-f01c-48fc-862e-7ea66153f434 Laurin

Result with projection
List<ElasticTableEntity> (1 item) PartitionKey RowKey Timestamp ETag Date FirstName Partition123 2520391787589766073 2013-03-13 1:00:40 AM +00:00 W/"datetime'2013-03-13T01%3A00%3A40.619873Z'" 1912-03-04 12:00:00 AM +00:00 Pascal

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