Solved

LINQ query on json sub array

Posted on 2016-09-15
2
100 Views
Last Modified: 2016-09-18
I am working on the attached jSon response. I need to read the sub-array named "details" and populate a datagrid.
I am using VB.NET.

Attached is how I declared the classes to be used in my LINQ query.

Below is my code so far.

Dim docs = JArray.FromObject(parsedObject("invoice")).Select(Function(x) x.ToObject(Of MyClass.InvoiceList)())
'Dim invoiceL = docs.Select(Function(x) x).OrderBy(Function(x) x.number).ToList

Dim InvoiceDetailL = docs.Select(Function(g) New With {g.details.First().totalBoxes, g.details.First().description, g.details.First().store, g.details.First().productId, g.details.First().totalPrice, g.details.First().Type}).ToList()
           
            gdviewDetail.DataSource = InvoiceDetailL
            gdviewDetail.DataBind()

As you can see, I am able to create "InvoiceDetailL" with only the first row of "details", as I used the function First(). How can I obtain all elements in the sequence?
Class_Declaration_EE.txt
jSon_Response_EE.txt
0
Comment
Question by:Angel02
2 Comments
 
LVL 33

Accepted Solution

by:
it_saige earned 500 total points
ID: 41800199
First of all, your class structure does not match the presented json.  If I go by the structure you have laid out, then this would be your class structure:
Public Class [MyClass]
	Public Property status As String
	Public Property invoice As List(Of InvoiceList)
End Class

Public Class InvoiceList
	Public Property id As Integer
	Public Property number As Integer
	Public Property customerName As String
	Public Property customerId As Integer
	Public Property details As New List(Of InvoiceDetailList)
End Class

Public Class InvoiceDetailList
	Public Property store As String
	Public Property productId As Integer
	Public Property Description As String
	Public Property totalBoxes As Integer
	Public Property Type As String
	Public Property totalPrice As Double
End Class

Open in new window

Now when deserialized into this class structure, we would use First to get the first InvoiceList item, then set the details to the datasource of the DataGridView; e.g. -
gdviewDetail.DataSource = docs.invoice.First().details
gdviewDetail.DataBind()

Open in new window


Proof of concept -
Imports Newtonsoft.Json.Linq
Imports Newtonsoft.Json

Public Class Form1
	Const json = "{""status"": ""OK"",""invoice"": [{""id"": 45667,""number"": ""2344"",""customerName"": ""ABC"",""customerId"": 676,""details"": [{""store"": ""XY"",""Type"": ""2"",""description"": ""PQR"",""productId"": 333,""totalBoxes"": 10,""totalPrice"": ""30.00""},{""store"": ""YZ"",""Type"": ""2"",""description"": ""STU"",""productId"": 222,""totalBoxes"": 5,""totalPrice"": ""15.00""},{""store"": ""ZA"",""Type"": ""1"",""description"": ""EGF"",""productId"": 332,""totalBoxes"": 20,""totalPrice"": ""50.00""}]}]}"

	Private Sub OnLoad(sender As Object, e As EventArgs) Handles MyBase.Load
		Dim docs = JsonConvert.DeserializeObject(Of [MyClass])(json)
		DataGridView1.DataSource = docs.invoice.First().details
	End Sub
End Class

Public Class [MyClass]
	Public Property status As String
	Public Property invoice As List(Of InvoiceList)
End Class

Public Class InvoiceList
	Public Property id As Integer
	Public Property number As Integer
	Public Property customerName As String
	Public Property customerId As Integer
	Public Property details As New List(Of InvoiceDetailList)
End Class

Public Class InvoiceDetailList
	Public Property store As String
	Public Property productId As Integer
	Public Property Description As String
	Public Property totalBoxes As Integer
	Public Property Type As String
	Public Property totalPrice As Double
End Class

Open in new window


Produces the following output -Capture.JPG
-saige-
0
 

Author Comment

by:Angel02
ID: 41800569
Thank you it_saige.

With your idea, I did the following

Dim InvoiceDetailL = docs.First().details.Select(Function(g) New With {g.totalBoxes, g.description, g.store, g.productId, g.totalPrice, g.Type}).ToList()
gdviewDetail.DataSource = InvoiceDetailL

That worked!

Hope it's a good way.
0

Featured Post

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
ElasticSearch Filter Query 1 256
Angularjs loop 9 54
Linq query getting % of questions in test 9 66
Deserialize JSON array VB.Net problem 3 58
Introduction JSON is an acronym for JavaScript Object Notation.  It is a text-string data transport mechanism, capable of representing simple or complex data structures in a consistent and easy-to-read manner.  Similar in concept to XML, but more e…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question