在反序列化DataTable之后，DateTime列类型变为String类型

Question

我有一个包含两列的DataTable。ShipmentDate（DateTime）和Count（Int）。在我反序列化字符串之后，我注意到如果第一个itemarray值为null，则ShipmentDate的类型将变为字符串。

检查以下示例。除了第一个数组项之外，两个json字符串都有相同的数据。

string jsonTable1 = "[{"ShipmentDate":null,"Count":3},{"ShipmentDate":"2015-05-13T00:00:00","Count":13},{"ShipmentDate":"2015-05-19T00:00:00","Count":1},{"ShipmentDate":"2015-05-26T00:00:00","Count":1},{"ShipmentDate":"2015-05-28T00:00:00","Count":2}]";
string jsonTable2 = "[{"ShipmentDate":"2015-05-13T00:00:00","Count":13},{"ShipmentDate":null,"Count":3},{"ShipmentDate":"2015-05-19T00:00:00","Count":1},{"ShipmentDate":"2015-05-26T00:00:00","Count":1},{"ShipmentDate":"2015-05-28T00:00:00","Count":2}]";
DataTable tbl1 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable1);
DataTable tbl2 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable2);
Console.WriteLine(tbl1.Columns["ShipmentDate"].DataType);
Console.WriteLine(tbl2.Columns["ShipmentDate"].DataType);

在我的场景中，第一个项数组的ShipmentDate可能为null，它通过将其转换为字符串类型来产生问题。

我有一种情况，数据表的模式是动态的。我不能创建强类型类。

Answer 1

这里的基本问题是Json.NET的DataTableConverter通过只查看第一行中的标记值来推断每个DataColumn.DataType。它之所以能以这种方式工作，是因为它将表的JSON流式传输，而不是将整个JSON加载到中间的JToken层次结构中。虽然流式传输在减少内存使用的情况下提供了更好的性能，但这意味着第一行中的null值可能会导致错误键入的列。

这是stackoverflow上不时出现的问题，例如，在反序列化缺少第一列的数据表的问题中。在这种情况下，提问者预先知道列类型应该是double。在您的案例中，您已经声明数据表的模式是动态的，因此不能使用该答案。然而，正如这个问题一样，由于Json.NET是MIT许可证下的开源软件，因此可以使用必要的逻辑创建其DataTableConverter的修改版本。

事实证明，通过记住数据类型不明确的列，然后在确定正确类型时用正确类型的列替换这些列，可以正确设置列类型，同时保留流式传输行为：

/// <summary>
/// Converts a <see cref="DataTable"/> to and from JSON.
/// </summary>
public class TypeInferringDataTableConverter : Newtonsoft.Json.Converters.DataTableConverter
{
    // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Converters/DataTableConverter.cs
    // Original license: https://github.com/JamesNK/Newtonsoft.Json/blob/master/LICENSE.md
    /// <summary>
    /// Reads the JSON representation of the object.
    /// </summary>
    /// <param name="reader">The <see cref="JsonReader"/> to read from.</param>
    /// <param name="objectType">Type of the object.</param>
    /// <param name="existingValue">The existing value of object being read.</param>
    /// <param name="serializer">The calling serializer.</param>
    /// <returns>The object value.</returns>
    public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
    {
        if (reader.TokenType == JsonToken.Null)
        {
            return null;
        }
        DataTable dt = existingValue as DataTable;
        if (dt == null)
        {
            // handle typed datasets
            dt = (objectType == typeof(DataTable))
                ? new DataTable()
                : (DataTable)Activator.CreateInstance(objectType);
        }
        // DataTable is inside a DataSet
        // populate the name from the property name
        if (reader.TokenType == JsonToken.PropertyName)
        {
            dt.TableName = (string)reader.Value;
            reader.ReadAndAssert();
            if (reader.TokenType == JsonToken.Null)
            {
                return dt;
            }
        }
        if (reader.TokenType != JsonToken.StartArray)
        {
            throw JsonSerializationExceptionHelper.Create(reader, "Unexpected JSON token when reading DataTable. Expected StartArray, got {0}.".FormatWith(CultureInfo.InvariantCulture, reader.TokenType));
        }
        reader.ReadAndAssert();
        var ambiguousColumnTypes = new HashSet<string>();
        while (reader.TokenType != JsonToken.EndArray)
        {
            CreateRow(reader, dt, serializer, ambiguousColumnTypes);
            reader.ReadAndAssert();
        }
        return dt;
    }
    private static void CreateRow(JsonReader reader, DataTable dt, JsonSerializer serializer, HashSet<string> ambiguousColumnTypes)
    {
        DataRow dr = dt.NewRow();
        reader.ReadAndAssert();
        while (reader.TokenType == JsonToken.PropertyName)
        {
            string columnName = (string)reader.Value;
            reader.ReadAndAssert();
            DataColumn column = dt.Columns[columnName];
            if (column == null)
            {
                bool isAmbiguousType;
                Type columnType = GetColumnDataType(reader, out isAmbiguousType);
                column = new DataColumn(columnName, columnType);
                dt.Columns.Add(column);
                if (isAmbiguousType)
                    ambiguousColumnTypes.Add(columnName);
            }
            else if (ambiguousColumnTypes.Contains(columnName))
            {
                bool isAmbiguousType;
                Type newColumnType = GetColumnDataType(reader, out isAmbiguousType);
                if (!isAmbiguousType)
                    ambiguousColumnTypes.Remove(columnName);
                if (newColumnType != column.DataType)
                {
                    column = ReplaceColumn(dt, column, newColumnType, serializer);
                }
            }
            if (column.DataType == typeof(DataTable))
            {
                if (reader.TokenType == JsonToken.StartArray)
                {
                    reader.ReadAndAssert();
                }
                DataTable nestedDt = new DataTable();
                var nestedUnknownColumnTypes = new HashSet<string>();
                while (reader.TokenType != JsonToken.EndArray)
                {
                    CreateRow(reader, nestedDt, serializer, nestedUnknownColumnTypes);
                    reader.ReadAndAssert();
                }
                dr[columnName] = nestedDt;
            }
            else if (column.DataType.IsArray && column.DataType != typeof(byte[]))
            {
                if (reader.TokenType == JsonToken.StartArray)
                {
                    reader.ReadAndAssert();
                }
                List<object> o = new List<object>();
                while (reader.TokenType != JsonToken.EndArray)
                {
                    o.Add(reader.Value);
                    reader.ReadAndAssert();
                }
                Array destinationArray = Array.CreateInstance(column.DataType.GetElementType(), o.Count);
                Array.Copy(o.ToArray(), destinationArray, o.Count);
                dr[columnName] = destinationArray;
            }
            else
            {
                object columnValue = (reader.Value != null)
                    ? serializer.Deserialize(reader, column.DataType) ?? DBNull.Value
                    : DBNull.Value;
                dr[columnName] = columnValue;
            }
            reader.ReadAndAssert();
        }
        dr.EndEdit();
        dt.Rows.Add(dr);
    }
    static object RemapValue(object oldValue, Type newType, JsonSerializer serializer)
    {
        if (oldValue == null)
            return null;
        if (oldValue == DBNull.Value)
            return oldValue;
        return JToken.FromObject(oldValue, serializer).ToObject(newType, serializer);
    }
    private static DataColumn ReplaceColumn(DataTable dt, DataColumn column, Type newColumnType, JsonSerializer serializer)
    {
        var newValues = Enumerable.Range(0, dt.Rows.Count).Select(i => dt.Rows[i]).Select(r => RemapValue(r[column], newColumnType, serializer)).ToList();
        var ordinal = column.Ordinal;
        var name = column.ColumnName;
        var @namespace = column.Namespace;
        var newColumn = new DataColumn(name, newColumnType);
        newColumn.Namespace = @namespace;
        dt.Columns.Remove(column);
        dt.Columns.Add(newColumn);
        newColumn.SetOrdinal(ordinal);
        for (int i = 0; i < dt.Rows.Count; i++)
            dt.Rows[i][newColumn] = newValues[i];
        return newColumn;
    }
    private static Type GetColumnDataType(JsonReader reader, out bool isAmbiguous)
    {
        JsonToken tokenType = reader.TokenType;
        switch (tokenType)
        {
            case JsonToken.Integer:
            case JsonToken.Boolean:
            case JsonToken.Float:
            case JsonToken.String:
            case JsonToken.Date:
            case JsonToken.Bytes:
                isAmbiguous = false;
                return reader.ValueType;
            case JsonToken.Null:
            case JsonToken.Undefined:
                isAmbiguous = true;
                return typeof(string);
            case JsonToken.StartArray:
                reader.ReadAndAssert();
                if (reader.TokenType == JsonToken.StartObject)
                {
                    isAmbiguous = false;
                    return typeof(DataTable); // nested datatable
                }
                else
                {
                    isAmbiguous = false;
                    bool innerAmbiguous;
                    // Handling ambiguity in array entries is not yet implemented because the first non-ambiguous entry in the array
                    // might occur anywhere in the sequence, requiring us to scan the entire array to determine the type, 
                    // e.g., given: [null, null, null, 314, null]
                    // we would need to scan until the 314 value, and do:
                    // return typeof(Nullable<>).MakeGenericType(new[] { reader.ValueType }).MakeArrayType();
                    Type arrayType = GetColumnDataType(reader, out innerAmbiguous);
                    return arrayType.MakeArrayType();
                }
            default:
                throw JsonSerializationExceptionHelper.Create(reader, "Unexpected JSON token when reading DataTable: {0}".FormatWith(CultureInfo.InvariantCulture, tokenType));
        }
    }
}
internal static class JsonSerializationExceptionHelper
{
    public static JsonSerializationException Create(this JsonReader reader, string format, params object[] args)
    {
        // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/JsonPosition.cs
        var lineInfo = reader as IJsonLineInfo;
        var path = (reader == null ? null : reader.Path);
        var message = string.Format(CultureInfo.InvariantCulture, format, args);
        if (!message.EndsWith(Environment.NewLine, StringComparison.Ordinal))
        {
            message = message.Trim();
            if (!message.EndsWith(".", StringComparison.Ordinal))
                message += ".";
            message += " ";
        }
        message += string.Format(CultureInfo.InvariantCulture, "Path '{0}'", path);
        if (lineInfo != null && lineInfo.HasLineInfo())
            message += string.Format(CultureInfo.InvariantCulture, ", line {0}, position {1}", lineInfo.LineNumber, lineInfo.LinePosition);
        message += ".";
        return new JsonSerializationException(message);
    }
}
internal static class StringUtils
{
    // Adapted from https://github.com/JamesNK/Newtonsoft.Json/blob/master/Src/Newtonsoft.Json/Utilities/StringUtils.cs
    public static string FormatWith(this string format, IFormatProvider provider, object arg0)
    {
        return format.FormatWith(provider, new[] { arg0 });
    }
    private static string FormatWith(this string format, IFormatProvider provider, params object[] args)
    {
        return string.Format(provider, format, args);
    }
}
internal static class JsonReaderExtensions
{
    public static void ReadAndAssert(this JsonReader reader)
    {
        if (reader == null)
            throw new ArgumentNullException("reader");
        if (!reader.Read())
        {
            throw JsonSerializationExceptionHelper.Create(reader, "Unexpected end when reading JSON.");
        }
    }
}

然后像这样使用：

var settings = new JsonSerializerSettings { Converters = new[] { new TypeInferringDataTableConverter() } };
DataTable tbl1 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable1, settings);
DataTable tbl2 = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(jsonTable2, settings);

不要设置NullValueHandling = NullValueHandling.Ignore，因为现在可以正确处理null。

原型小提琴

请注意，虽然这个类处理具有null值的列的重新键入，但它不处理包含数组值的列（其中第一个数组项为null）的重新键入。例如，如果某列的第一行具有值

[null, null, null, 314, null]

那么推断出的列类型理想情况下是typeof( long? [] )，但是这里没有实现。可能需要将JSON完全加载到JToken层次结构中才能做出该决定。

Answer 2

我的问题64647406链接到这里。

我发现我可以使用强类型的DataTable导数，并预先添加我知道的DataType的任何列。这些将不受[FromBody]的影响，并按原样简单使用。只要列名正确，就可以从索引0开始添加它们——不需要添加所有列；只是那些你需要绝对控制的。

这意味着第一行中的NULL值不会为这些值创建string列。