Solved

Traverse JSON array using LINQ query

Posted on 2016-08-02
11
82 Views
Last Modified: 2016-08-08
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
Comment
Question by:Angel02
  • 6
  • 5
11 Comments
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41739122
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
 

Author Comment

by:Angel02
ID: 41741317
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41741332
Hi Angel02;

How are the class/s defined then? That is needed so the properties can be accessed correctly.
0
 

Author Comment

by:Angel02
ID: 41741682
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
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41741685
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Angel02
ID: 41742394
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
 
LVL 62

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41742719
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
 

Author Comment

by:Angel02
ID: 41744374
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
 
LVL 62

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41744755
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
 

Author Comment

by:Angel02
ID: 41747847
Thank you!

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

If(g.First().Contact.FirstOrDefault() IsNot Nothing
0
 
LVL 62

Expert Comment

by:Fernando Soto
ID: 41748169
Not a problem Angel02, glad that worked out for you.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
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…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…

912 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now