Unexpected end when setting [PARAMETER]'s Value - Visual Studio says string is not valid JSON when online validators say it is
c# dapper json json.net visual-studio
Question
I have a SQL stored procedure returning a very large complex JSON string as its result through the use of FOR JSON PATH and a set of JSON_QUERY, and am attempting to convert this String to an object using JsonConvert.DeserializeObject<MyObject> in a custom TypeHandler I've written for Dapper. However I'm running into an issue where I get the following error:
Unexpected end when setting ChargeType's value. Path...
My object is expecting an int for that value, and the JSON is providing it with a proper value type. Additionally, when I attempt to inspect the string in Visual Studio it says that it is not formatted as a JSON string. Using an online JSON validator, however, (jsonformatter.org) I'm told that the string IS valid JSON.
Here is a small sample section of the JSON that is similar to what is being deserialized. The important thing here is that the JSON string is extremely large, with many more entries like this one. This entry in particular is an invoice, and belongs to an invoice header with many other invoices. The invoice header belongs to an invoice group which has other invoice headers in it as well, and the invoice group belongs to an order which has several other invoice groups.
...More objects and parent object above...
{
"Id": "B5F18C84-4790-E811-80CC-005056BA0972",
"RefId": "4532516",
"InboundWeight": 0,
"OutboundWeight": 4352,
"Total": 4.75,
"Surcharge": 0,
"FlatRate": 130,
"Credit": 0,
"Surcharge2": 0,
"Cwt": 0,
"ChargeType": 155,
"Key": "18981760",
"ReferenceKey": "3003858587",
"StopKey": "11792108",
"RawTotal": 4.75,
"ChargetTypeKey": "SANITIZED",
"ChargeTypeDescription": "SANITIZED",
"RawWeight": 4352,
"Header_Id": "B4F18C84-4790-E811-80CC-005056BA0972"
}
...More objects below...
Accepted Answer
It turns out that Visual Studio is indeed doing something wrong on its end. While the stored procedure returns the entire JSON string, Visual Studio truncates the string extremely short. I'm currently looking into options for either preventing it from truncating the string or splitting out the JSON into multiple result sets. I'd like to avoid this if possible as this will require spinning through the JSON in order to add invoice groups to the order, headers to their groups, invoices to their proper headers, etc.
Edit: Further clarification and an actual solution
My issue was partially related to the way SQL Server and ADO.net communicate from my initial reading on the subject. Essentially, the data was being sent over in truncated chunks, and because I was using Dapper's QuerySingle<MyObject> to attempt to get all of it at once, I was actually only accessing the first chunk which resulted in the error being thrown.
Using String.Concat(Query<string>(MyArgsHere)) I am able to get the entire JSON string, and then from there I should be able to serialize that into my object. I am, however, still looking at potentially breaking this down into multiple result sets as per @JeroenMostert's comment.
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow