• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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
0
Angel02
Asked:
Angel02
  • 6
  • 5
2 Solutions
 
Fernando SotoRetiredCommented:
Hi Angel02;

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.
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().contactName,
                          .ContactPhone = g.First().contactPhone,
                          .ContactEmail = g.First().contactEmail
                      }
                      ).ToList()

Open in new window

0
 
Angel02Author Commented:
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.
0
 
Fernando SotoRetiredCommented:
Hi Angel02;

How are the class/s defined then? That is needed so the properties can be accessed correctly.
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Angel02Author Commented:
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
0
 
Fernando SotoRetiredCommented:
But in what class are these defined?
Public Property contactName As String
Public Property contactPhone As String
Public Property contactEmail As Date  

Open in new window

0
 
Angel02Author Commented:
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?
0
 
Fernando SotoRetiredCommented:
The way you have it set up now you will not be able to add the contact right now because you can only have a collection of a class that would be Inventory and that class has no reference to the InventoryContact. InventoryContact has to have a reference from the Inventory class or become part of the Inventory class.
0
 
Angel02Author Commented:
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}).ToList()

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":[]
    }
 ]
}
0
 
Fernando SotoRetiredCommented:
Hi Angel02;

Try the query like the following.
Dim groupingList = inventory.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 = If(g.FirstOrDefault() IsNot Nothing, g.First().contact.First().name, ""),
                        .contactphone = If(g.FirstOrDefault() IsNot Nothing, g.First().contact.First().phone, ""),
                        .contactemail = If(g.FirstOrDefault() IsNot Nothing, g.First().contact.First().email, "")
                    }).ToList()

Open in new window

0
 
Angel02Author Commented:
Thank you!

I just had to change it to the following and it worked!

If(g.First().Contact.FirstOrDefault() IsNot Nothing
0
 
Fernando SotoRetiredCommented:
Not a problem Angel02, glad that worked out for you.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now