Angel02
asked on
Traverse JSON array using LINQ query
I am using NewtonSoft to convert JSON to classes.
Below is the JSON response with array of inventory that incldues a single array of contacts.
{
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[
{
"name": P,
"phone": 3333,
"email":p@g.com
}
]
}
]
}
Below is the class
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date
End Class
I am using the following code to populate a gridview
Dim groupingList = docs.GroupBy(Function(g) New With {Key .Description = g.description, .Code = g.Code, .Category = g.category} ).
Select(Function(g) New With {g.First().description, g.First().Code, g.First().category, .Sum = g.Sum(Function(s) s.quantity), .Max = g.Max(Function(m) m.Price)}).ToList()
Gridview:
description | code |category | Quantity |Price |contactName
Now, how do I modify my LINQ query to include contact details in the gridview, so the gridview looks like:
description | code |category | Quantity |Price |contactName | contactPhone | contactEmail
Below is the JSON response with array of inventory that incldues a single array of contacts.
{
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[
{
"name": P,
"phone": 3333,
"email":p@g.com
}
]
}
]
}
Below is the class
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date
End Class
I am using the following code to populate a gridview
Dim groupingList = docs.GroupBy(Function(g) New With {Key .Description = g.description, .Code = g.Code, .Category = g.category} ).
Select(Function(g) New With {g.First().description, g.First().Code, g.First().category, .Sum = g.Sum(Function(s) s.quantity), .Max = g.Max(Function(m) m.Price)}).ToList()
Gridview:
description | code |category | Quantity |Price |contactName
Now, how do I modify my LINQ query to include contact details in the gridview, so the gridview looks like:
description | code |category | Quantity |Price |contactName | contactPhone | contactEmail
ASKER
Actually I declared those in the class wrong
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date
would not give me anything as the JSON response does not have these nodes named "contactname", "contactPhone" and "contactEmail"
Please let me know the right way of doing this.
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date
would not give me anything as the JSON response does not have these nodes named "contactname", "contactPhone" and "contactEmail"
Please let me know the right way of doing this.
Hi Angel02;
How are the class/s defined then? That is needed so the properties can be accessed correctly.
How are the class/s defined then? That is needed so the properties can be accessed correctly.
ASKER
Sorry I am not sure what you mean. I defined the class according to the nodes in the JSON response, using the same names.
For response
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[
{
"name": P,
"phone": 3333,
"email":p@g.com
}
]
I defined the class as below:
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
End Class
For response
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[
{
"name": P,
"phone": 3333,
"email":p@g.com
}
]
I defined the class as below:
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
End Class
But in what class are these defined?
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date
ASKER
I now declared them in a different class, like below:
Public Class InventoryContact
Public Property Name As String
Public Property Phone As String
Public Property Email As Date
End Class
Would that work?
Public Class InventoryContact
Public Property Name As String
Public Property Phone As String
Public Property Email As Date
End Class
Would that work?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks!.
I added a reference like below
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
Public Property Contact As List(Of InventoryContact
End Class
Public Class InventoryContact
Public Property Name As String
Public Property Phone As String
Public Property Email As Date
End Class
I modified the LINQ query like below
Dim groupingList = docs.GroupBy(Function(g) New With {Key .Description = g.description, .Code = g.Code, .Category = g.category} ).
Select(Function(g) New With {g.First().description, g.First().Code, g.First().category, .Sum = g.Sum(Function(s) s.quantity), .Max = g.Max(Function(m) m.Price), .contactname = g.First().Contact.First(). name, .contactphone = g.First().contact.First(). phone, .contactemail = g.First().Contact.First(). email}).To List()
The only problem I have now is that the contact array could be empty in few records. That is probably why I am getting the error "Value cannot be null."
How do I handle empty array?
{
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[]
}
]
}
I added a reference like below
Public Class Inventory
Public Property ItemId As Integer
Public Property category As String
Public Property description As String
Public Property company As String
Public Property code As Integer
Public Property Quantity As Integer
Public Property Price As Decimal
Public Property Contact As List(Of InventoryContact
End Class
Public Class InventoryContact
Public Property Name As String
Public Property Phone As String
Public Property Email As Date
End Class
I modified the LINQ query like below
Dim groupingList = docs.GroupBy(Function(g) New With {Key .Description = g.description, .Code = g.Code, .Category = g.category} ).
Select(Function(g) New With {g.First().description, g.First().Code, g.First().category, .Sum = g.Sum(Function(s) s.quantity), .Max = g.Max(Function(m) m.Price), .contactname = g.First().Contact.First().
The only problem I have now is that the contact array could be empty in few records. That is probably why I am getting the error "Value cannot be null."
How do I handle empty array?
{
"inventory": [
{
"ItemId": 123,
"category": "A",
"Description": "xyz",
"Code": "2",
"company": "Z",
"quantity": 5,
"price": 2.5,
"contact":[]
}
]
}
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you!
I just had to change it to the following and it worked!
If(g.First().Contact.First OrDefault( ) IsNot Nothing
I just had to change it to the following and it worked!
If(g.First().Contact.First
Not a problem Angel02, glad that worked out for you.
One question, when you convert the JSON to the class Inventory it is also filling in the three new properties for contact? If this is so then this should do it. Please see the three new lines at the end of the select statement.
Open in new window