Solved

LINQ query on json sub array

Posted on 2016-09-15
2
138 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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, …
We will take a look at the d3.js library for visualizations.  I will provide a walkthrough of a short bar graph example as well as introduce you to the d3.js API.  We also will explore links of other examples and further information regarding SVG's.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

759 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