ArgumentOutOfRange exception when deserializing to a DataSet with Json.Net

Question

I have the following JSON string in a variable called strJSON.

{
   "results":[
      {
         "templateName":"HUD Section 8",
         "userID":"2",
         "mobileObjectId":"4582",
         "source":"M",
         "inspectionType":"A",
         "notes":"Window in bedroom needs repair.",
         "agencyID":"",
         "requestDate":"2014-05-09 00:00:00",
         "agencyName":"",
         "inspectionTimeBegun":"2014-05-09 14:00:17",
         "inspectionDate":"2014-05-09 14:30:00",
         "inspectionID":135,
         "inspectionTimeComplete":"2014-05-09 14:29:25",
         "summaryDecision":"F",
         "createdAt":"2014-05-09T18:29:35.050Z",
         "updatedAt":"2014-05-09T18:29:35.050Z",
         "objectId":"1FgtD6WT8Y",
         "ACL":{
            "*":{
               "read":true
            },
            "cryZoU5gXJ":{
               "write":true,
               "read":true
            }
         }
      }
   ]
}

When I call the following line of code...

ds = Newtonsoft.Json.JsonConvert.DeserializeObject(Of DataSet)(strJSON)

I get an exception with the message stating "Specified argument was out of the range of valid values"

The JSON string is created with the following REST API call to Parse.com.

strJSON = http.QuickGetStr(strURL)

I am using this elsewhere with success albeit with simpler Parse classes but I have gone through this JSON string carefully and can't see anything wrong.

Any ideas on what might be causing this error?

Answer 1

In order for Json.Net to deserialize into a DataSet, the JSON must be in a specific format, as described in this answer. Your JSON is close, but the problem is the ACL object. The DataTableConverter that Json.Net 5.0 uses expects all of the columns in the table to be simple data types or it will throw an ArgumentOutOfRangeException (source). Json.Net 6.0 supports nested data tables and arrays in addition to simple types, but your ACL data still does not meet the required format that would allow it to be deserialized correctly to a DataSet. You have a few different options for dealing with this:

Change the JSON

If you control the format of the JSON (i.e. it is not from a third party) you can change it such that Json.Net 6.0 will be able to deserialize it to a DataSet. Here is what it would need to look like for that to work:

{
    "results": [
        {
            "templateName": "HUD Section 8",
            "userID": "2",
            "mobileObjectId": "4582",
            "source": "M",
            "inspectionType": "A",
            "notes": "Window in bedroom needs repair.",
            "agencyID": "",
            "requestDate": "2014-05-09 00:00:00",
            "agencyName": "",
            "inspectionTimeBegun": "2014-05-09 14:00:17",
            "inspectionDate": "2014-05-09 14:30:00",
            "inspectionID": 135,
            "inspectionTimeComplete": "2014-05-09 14:29:25",
            "summaryDecision": "F",
            "createdAt": "2014-05-09T18:29:35.050Z",
            "updatedAt": "2014-05-09T18:29:35.050Z",
            "objectId": "1FgtD6WT8Y",
            "ACL": [
                {
                    "user": "*",
                    "read": true,
                    "write": false
                },
                {
                    "user": "cryZoU5gXJ",
                    "read": true,
                    "write": true
                }
            ]
        }
    ]
}

With this format, the ACL column of the results table will contain a nested DataTable with the individual ACL rows, each row having three columns, user, read and write.

Deserialize to strongly-typed classes

Instead of deserializing into a DataSet, you could deserialize into a set of strongly-typed classes. The advantage to this approach is that everything is in an easily usable form. The disadvantage is that you need to know what is in the JSON before you can create the classes.

You can use third-party tools like json2csharp.com to help generate the classes from a sample of the JSON, as was suggested in another answer (now deleted), but note that this is not foolproof (and it doesn't do VB). Sometimes you will need to intervene and edit the classes manually. For example, if generate classes from the JSON in your question, you'll notice that it creates a fixed class for each ACL instance. This will not work unless your set of ACLs always has exactly two items, one called Everyone and the other CryZoU5gXJ. I think it is much more likely that the set of ACLs will be variable, so it makes sense to use a Dictionary for these. Here are the classes I would propose:

Class RootObject
    Public Property results As List(Of Result)
End Class

Class Result
    Public Property templateName As String
    Public Property userID As String
    Public Property mobileObjectId As String
    Public Property source As String
    Public Property inspectionType As String
    Public Property notes As String
    Public Property agencyID As String
    Public Property requestDate As String
    Public Property agencyName As String
    Public Property inspectionTimeBegun As String
    Public Property inspectionDate As String
    Public Property inspectionID As Integer
    Public Property inspectionTimeComplete As String
    Public Property summaryDecision As String
    Public Property createdAt As String
    Public Property updatedAt As String
    Public Property objectId As String
    Public Property ACL As Dictionary(Of String, ACL)
End Class

Class ACL
    Public Property read As Boolean
    Public Property write As Boolean
End Class

With this class structure in place, you can deserialize like this:

Dim root As RootObject = JsonConvert.DeserializeObject(Of RootObject)(strJSON)

For the ACLs, the key for each dictionary entry will be the user ID (or * as you have in your example). If you don't actually care about the ACLs, you can simply omit the ACL property from the Result class. By default Json.Net will skip properties that exist in the JSON but do not exist in the class.

Use the LINQ-to-JSON API to parse the JSON

With Json.Net there is always more than one way to skin the cat. Json.Net's LINQ-to-JSON API really shines when the JSON you are parsing is highly variable and/or you don't want to create classes for receiving the data. You can deserialize any valid JSON to a hierarchy of JToken objects and then pick them apart however you need. For example, if you just needed a few select pieces of information from each result, you could do this:

Dim token As JToken = JToken.Parse(json)

For Each result As JObject In token("results").Children(Of JObject)()
    Console.WriteLine("userID: " + result("userID").ToString())
    Console.WriteLine("templateName: " + result("templateName").ToString())
    Console.WriteLine("inspectionID: " + result("inspectionID").ToString())
    Console.WriteLine("inspectionType: " + result("inspectionType").ToString())
    Console.WriteLine("inspectionDate: " + result("inspectionDate").ToString())
    Console.WriteLine("summaryDecision: " + result("summaryDecision").ToString())
    Console.WriteLine("notes: " + result("notes").ToString())
Next

You could use this same approach to manually build a DataSet from the JSON. Here is a generic function that will deserialize JSON into a DataSet but ignore any complex objects (e.g. the ACLs) instead of throwing an exception:

Function DeserializeToDataSet(json As String) As DataSet

    Dim root As JObject = JObject.Parse(json)
    Dim ds As DataSet = New DataSet()

    For Each prop As JProperty In root.Properties

        If prop.Value.Type = JTokenType.Array Then

            Dim dt As DataTable = ds.Tables.Add(prop.Name)
            For Each row As JObject In prop.Value.Children(Of JObject)()

                Dim dr As DataRow = dt.NewRow
                For Each col As JProperty In row.Properties

                    Dim colType As Type = GetColumnType(col.Value.Type)
                    If Not colType Is Nothing Then

                        Dim dc As DataColumn = dt.Columns(col.Name)
                        If dc Is Nothing Then
                            dc = dt.Columns.Add(col.Name, colType)
                        End If

                        dr(col.Name) = col.Value.ToObject(colType)
                    End If
                Next

                dt.Rows.Add(dr)
            Next
        End If
    Next

    Return ds

End Function

Function GetColumnType(tokenType As JTokenType) As Type

    If tokenType = JTokenType.String Then Return GetType(String)
    If tokenType = JTokenType.Integer Then Return GetType(Integer)
    If tokenType = JTokenType.Date Then Return GetType(DateTime)
    If tokenType = JTokenType.Boolean Then Return GetType(Boolean)
    If tokenType = JTokenType.Float Then Return GetType(Double)
    Return Nothing

End Function

Of course if you need the ACLs, you'll need to customize this method to get that data into a form that is consumable by your code. I'll leave that part to you.

Answer 2

Json.Net will only parse directly into a DataSet if it conforms to a certain standard. See this answer for the layout it needs.

However, you could deserialize to an XML document and use the DataSet object's ReadXml method load it for you. See this question for details on how to do this.

(HT to Brian Rogers for dataset structure details)