Solved

LINQ query on json sub array

Posted on 2016-09-15
2
150 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

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

Get proactive database performance tuning online

At Percona’s web store you can order full Percona Database Performance Audit in minutes. Find out the health of your database, and how to improve it. Pay online with a credit card. Improve your database performance now!

Question has a verified solution.

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

The Confluence of Individual Knowledge and the Collective Intelligence At this writing (summer 2013) the term API (http://dictionary.reference.com/browse/API?s=t) has made its way into the popular lexicon of the English language.  A few years ago, …
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 this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

630 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