Storing decimal data type in Azure Tables

Question

Windows Azure Table Storage does not support the decimal data type.

A suggested workaround is to use a custom attribute to serialize the decimal property as string:

[EntityDataType(PrimitiveTypeKind.String)]
public decimal Quantity { get; set; }

How could this EntityDataType custom attribute be implemented so decimal properties can be stored and retrieved from Windows Azure Tables?

Answer 1

You can change the type of the property to double. You would then have to convert between decimal and double by mapping the table entity to your own domain type. Another option would be to have two properties on the entity backed by a single decimal field. However, you probably want to keep using the Quantity name for the decimal property and as it is the double property that is be stored in the table you will have to rename this property to Quantity by overriding ReadEntity and WriteEntity. Then you might as well use some of the other solutions proposed here.

Now, you might think that storing a decimal as a double leads to some values not round-tripping correctly. While there certainly are values that will not round-trip simply because the range and precision of the two types are very different most "normal" values like monetary values that are not astronomically large and with human readable precision will round-trip without any problems. The reason for this is that conversion from double to decimal as performed by Convert.ToDouble has a special property:

The Decimal value returned by this method contains a maximum of 15 significant digits.

Here is an example of how round-tripping an otherwise problematic number works because of this:

var originalValue = 2.24M;
var doubleValue = (double) originalValue;

The problem is that there is no exact representation of the decimal number 2.24 using floating point just as there is no exact representation of the rational number 1/3 using a decimal number (2.24 is the rational number 224/100). 0.3333333333333333 is not the same as 1/3. You can verify this by printing doubleValue with enough precision. Console.WriteLine($"{doubleValue:G17}") yields

2.2400000000000002

However, round-tripping the value still works:

var roundTripValue = (decimal) doubleValue;

Now Console.WriteLine(roundTripValue) yields

2.24

So as long as you don't do any computations on the double values you can use them to store decimal values provided that the conversion between double and decimal adheres to the .NET rule cited above.

Answer 2

@EUYUIL has raised a good generic solution which I have used to good effect, however as his answer stands it will fail when using a Nullable type.

       // Get the underlying types 'Parse' method
       if (curType.IsGenericType && curType.GetGenericTypeDefinition() == typeof(Nullable<>))
       {
            curType = Nullable.GetUnderlyingType(curType);
       }

In case it helps anyone, the contents of the ReadEntity override method inside the foreach. There may be better ways to write this, but for illustration purposes this will do.

        var curType = thisProperty.PropertyType;

        // Get the underlying types 'Parse' method
        if (curType.IsGenericType && curType.GetGenericTypeDefinition() == typeof(Nullable<>))
        {
            curType = Nullable.GetUnderlyingType(curType);
        }

        var parse = curType.GetMethods().SingleOrDefault(m =>
            m.Name == "Parse" &&
            m.GetParameters().Length == 1 &&
            m.GetParameters()[0].ParameterType == typeof(string));

        var value = parse != null ?
            parse.Invoke(thisProperty, new object[] { properties[thisProperty.Name].StringValue }) :
                  Convert.ChangeType(properties[thisProperty.Name].PropertyAsObject, thisProperty.PropertyType);


        thisProperty.SetValue(this, value);

Answer 3

Overriding ReadEntity and WriteEntity in the base class is good for this. It's not necessary to write an EntityResolver every time you retrieve the entities.

public class CustomTableEntity : TableEntity
{
    public override void ReadEntity(IDictionary<string, EntityProperty> properties, OperationContext operationContext)
    {
        base.ReadEntity(properties, operationContext);

        foreach (var thisProperty in
            GetType().GetProperties().Where(thisProperty =>
                thisProperty.GetType() != typeof(string) &&
                properties.ContainsKey(thisProperty.Name) &&
                properties[thisProperty.Name].PropertyType == EdmType.String))
        {
            var parse = thisProperty.PropertyType.GetMethods().SingleOrDefault(m =>
                m.Name == "Parse" &&
                m.GetParameters().Length == 1 &&
                m.GetParameters()[0].ParameterType == typeof(string));

            var value = parse != null ?
                parse.Invoke(thisProperty, new object[] { properties[thisProperty.Name].StringValue }) :
                Convert.ChangeType(properties[thisProperty.Name].PropertyAsObject, thisProperty.PropertyType);

            thisProperty.SetValue(this, value);
        }
    }

    public override IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
    {
        var properties = base.WriteEntity(operationContext);

        foreach (var thisProperty in
            GetType().GetProperties().Where(thisProperty =>
                !properties.ContainsKey(thisProperty.Name) &&
                typeof(TableEntity).GetProperties().All(p => p.Name != thisProperty.Name)))
        {
            var value = thisProperty.GetValue(this);
            if (value != null)
            {
                properties.Add(thisProperty.Name, new EntityProperty(value.ToString()));
            }
        }

        return properties;
    }
}

When you use, just make your entities extend from CustomTableEntity and it will be transparent when inserting or retrieving entities. It supports DateTime, TimeSpan, decimal and those types who have Parse method or implement IConvertible interfaces.

Answer 4

Have you tried using the Lokad.Cloud FatEntities product?

I think they are just using binary serializer for the whole object you want to store in the Table. It might be worthwhile to take a look at the "Object-to-Cloud mapper" project also:

https://github.com/Lokad/lokad-cloud

Answer 5

You may override the WriteEntity method in TableEntity and use EntityResolver

public class CustomTableEntity : TableEntity
{
    private const string DecimalPrefix = "D_";

    public override IDictionary<string, EntityProperty> WriteEntity(OperationContext operationContext)
    {
        var entityProperties = base.WriteEntity(operationContext);
        var objectProperties = GetType().GetProperties();

        foreach (var item in objectProperties.Where(f => f.PropertyType == typeof (decimal)))
        {
            entityProperties.Add(DecimalPrefix + item.Name, new EntityProperty(item.GetValue(this, null).ToString()));
        }

        return entityProperties;
    }
}

the entity we will use

public class MyEntity : CustomTableEntity
{
    public string MyProperty { get; set; }

    public decimal MyDecimalProperty1 { get; set; }
    public decimal MyDecimalProperty2 { get; set; }
}

usage which includes Create Table / Insert / Retreive

#region connection

CloudStorageAccount account = CloudStorageAccount.DevelopmentStorageAccount;
CloudTableClient client = account.CreateCloudTableClient();
CloudTable table = client.GetTableReference("mytable");
table.CreateIfNotExists();

#endregion


const string decimalPrefix = "D_";

const string partitionKey = "BlaBlaBla";
string rowKey = DateTime.Now.ToString("yyyyMMddHHmmss");


#region Insert

var entity = new MyEntity
    {
        PartitionKey = partitionKey,
        RowKey = rowKey,
        MyProperty = "Test",
        MyDecimalProperty1 = (decimal) 1.2,
        MyDecimalProperty2 = (decimal) 3.45
    };

TableOperation insertOperation = TableOperation.Insert(entity);
table.Execute(insertOperation);

#endregion



#region Retrieve

EntityResolver<MyEntity> myEntityResolver = (pk, rk, ts, props, etag) =>
    {
        var resolvedEntity = new MyEntity {PartitionKey = pk, RowKey = rk, Timestamp = ts, ETag = etag};

        foreach (var item in props.Where(p => p.Key.StartsWith(decimalPrefix)))
        {
            string realPropertyName = item.Key.Substring(decimalPrefix.Length);
            System.Reflection.PropertyInfo propertyInfo = resolvedEntity.GetType().GetProperty(realPropertyName);
            propertyInfo.SetValue(resolvedEntity, Convert.ChangeType(item.Value.StringValue, propertyInfo.PropertyType), null);

        }

        resolvedEntity.ReadEntity(props, null);

        return resolvedEntity;
    };

TableOperation retrieveOperation = TableOperation.Retrieve(partitionKey, rowKey, myEntityResolver);
TableResult retrievedResult = table.Execute(retrieveOperation);
var myRetrievedEntity = retrievedResult.Result as MyEntity;

// myRetrievedEntity.Dump(); 

#endregion