The Never Ending Journey: Using Azure Table Storage with dynamic table entities

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.

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); } } }

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");

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

PartitionKey	RowKey	Timestamp	ETag	FirstName	Number	Bool	Date	TokenId	LastName
List<ElasticTableEntity> (1 item)
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

PartitionKey	RowKey	Timestamp	ETag	Date	FirstName
List<ElasticTableEntity> (1 item)
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.

The Never Ending Journey

Tuesday, March 12, 2013

Using Azure Table Storage with dynamic table entities

See also

14 comments: