Deserialization of dynamic JSON to a DataTable is losing decimals when first few JSON items have non-decimal values

Question

I'm trying to deserialize some dynamically created JSON to a data table using Json.Net and the resulting table doesn't have the expected decimal values.

string data = @"[
    {""RowNumber"":1,""ID"":4289,""Assets Variance"":100,""Rules Diff"":10.72,""TotalFunding"":0},
    {""RowNumber"":2,""ID"":4233,""Assets Variance"":75,""Rules Diff"":6.7,""TotalFunding"":0},
    {""RowNumber"":3,""ID"":2222,""Assets Variance"":43,""Rules Diff"":6.7,""TotalFunding"":43.22}
]";

DataTable dt = JsonConvert.DeserializeObject<DataTable>(data);

If you look at the first two items in this JSON, the attribute Total Funding has value 0 and third item has value 43.22 but when we convert it to a data table, it will be rendered as 43. This is not happening for the attribute Rules Diff as it has a valid decimal value in the first item itself.

The attributes in the JSON are dynamic and hence casting against a specific type is not an option. How can we deserialize this JSON so that it will retain decimals in the data table?

Answer 1

This is a known limitation with the DataTableConverter that ships with Json.Net. The converter assumes the first row of data in the JSON is a representative sample for all the rows and uses that to determine the data types for the columns in the DataTable.

If you know in advance what data types you have in your JSON, one way to work around the problem is to deserialize to a List<T> instead of a DataTable, where T is a class with property names and types matching the JSON. Then, if you still need a table, you can construct it from the list as a post-processing step.

However, you said that your JSON is dynamic, so you will need to use a custom JsonConverter instead. It's possible to make one which can read ahead through the JSON to determine what is the best data type to use for each column. Something like the following should work. Feel free to customize it to your needs.

public class ReadAheadDataTableConverter : JsonConverter
{
    public override bool CanConvert(Type objectType)
    {
        return objectType == typeof(DataTable);
    }

    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        JArray array = JArray.Load(reader);
        var dataTypes = DetermineColumnDataTypes(array);
        var table = BuildDataTable(array, dataTypes);
        return table;
    }

    private DataTable BuildDataTable(JArray array, Dictionary<string, Type> dataTypes)
    {
        DataTable table = new DataTable();
        foreach (var kvp in dataTypes)
        {
            table.Columns.Add(kvp.Key, kvp.Value);
        }

        foreach (JObject item in array.Children<JObject>())
        {
            DataRow row = table.NewRow();
            foreach (JProperty prop in item.Properties())
            {
                if (prop.Value.Type != JTokenType.Null)
                {
                    Type dataType = dataTypes[prop.Name];
                    row[prop.Name] = prop.Value.ToObject(dataType);
                }
            }
            table.Rows.Add(row);
        }
        return table;
    }

    private Dictionary<string, Type> DetermineColumnDataTypes(JArray array)
    {
        var dataTypes = new Dictionary<string, Type>();
        foreach (JObject item in array.Children<JObject>())
        {
            foreach (JProperty prop in item.Properties())
            {
                Type currentType = GetDataType(prop.Value.Type);
                if (currentType != null)
                {
                    Type previousType;
                    if (!dataTypes.TryGetValue(prop.Name, out previousType) ||
                        (previousType == typeof(long) && currentType == typeof(decimal)))
                    {
                        dataTypes[prop.Name] = currentType;
                    }
                    else if (previousType != currentType)
                    {
                        dataTypes[prop.Name] = typeof(string);
                    }
                }
            }
        }
        return dataTypes;
    }

    private Type GetDataType(JTokenType tokenType)
    {
        switch (tokenType)
        {
            case JTokenType.Null:
                return null;
            case JTokenType.String:
                return typeof(string);
            case JTokenType.Integer: 
                return typeof(long);
            case JTokenType.Float: 
                return typeof(decimal);
            case JTokenType.Boolean: 
                return typeof(bool);
            case JTokenType.Date: 
                return typeof(DateTime);
            case JTokenType.TimeSpan: 
                return typeof(TimeSpan);
            case JTokenType.Guid: 
                return typeof(Guid);
            case JTokenType.Bytes: 
                return typeof(byte[]);
            case JTokenType.Array:
            case JTokenType.Object:
                throw new JsonException("This converter does not support complex types");
            default: 
                return typeof(string);
        }
    }

    public override bool CanWrite
    {
        get { return false; }
    }

    public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
    {
        throw new NotImplementedException();
    }
}

To use the converter, pass an instance to the DeserializeObject method like this:

DataTable dt = JsonConvert.DeserializeObject<DataTable>(data, new ReadAheadDataTableConverter());

Note that this converter will run a little slower than the OOB DataTableConverter due to the extra processing. With small data sets it should not be noticeable.

Working demo here: https://dotnetfiddle.net/iZ0u6Y

Answer 2

Just to add on the accepted answer (I don't have enough reputation to comment), there's also a situation where you can have a first type as a decimal and later on it could be detected as an integer. @brian-rogers answer will put this into a string. I've added another elseif for this case:

else if (previousType == typeof(decimal) && currentType == typeof(long))
{
   dataTypes[prop.Name] = previousType;
}

And here's his modifed demo: https://dotnetfiddle.net/IxGerR