反序列化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而不是将整个数据加载到中间层次结构中。流式传输可以在减少内存使用的情况下提供更好的性能，但这意味着第一行中的值可能会导致列的类型不正确。
__JTokennull

这是不时出现在stackoverflow上的问题，例如在反序列化缺少第一列的数据表的问题中。在这种情况下，发问者会事先知道列类型应为double。在您的情况下，您已声明 datatable的模式是dynamic
，因此无法使用答案。但是，与该问题一样，由于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, 314, null]

然后，理想地推断出的列类型将是typeof( long? [] )，但是此处未实现。可能有必要将JSON完全加载到JToken层次结构中以进行确定。