Solved

LINQ query to populate a datagrid in VB.NET from JSON response

Posted on 2016-07-26
12
106 Views
Last Modified: 2016-08-02
I am slowly getting a hang of JSON. At this point I am stuck with the issue of populating a datagrid in VB.NET from a JSON response. The JSON response is something like

{  
   "inventory": [
   {
     "ItemId": 123,
     "category": "A",
     "Description": "xyz",
     "Code": "2",
     "company": "Z",
     "quantity": 5,
     "price": 2.5,
    }
 ]
}

The code I am using is

    Public Class Inventory
        Public Property ItemId As Integer
        Public Property description As String
        Public Property Code As String
        Public Property category As String
        Public Property company As String
        Public Property quantity As Integer
        Public Property Price As Integer
    End Class

 Dim parsedObject = JObject.Parse(response)

        Dim docs = JArray.FromObject(parsedObject("inventory")).Select(Function(x) x.ToObject(Of MyClass.Inventory)())
        Dim invenList = docs.Select(Function(x) x).OrderBy(Function(x) x.description).ToList
       
        myGrid.DataSource = invenList
        myGrid.DataBind()



I am able to populate a datagrid using the code above. Now I need to group by description, code, category and get Sum(Quantity) and Max(Price). How can I modify my LINQ query for that?
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
  • 6
  • 6
12 Comments
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41730148
Where do you want the results of your final query to appear in? / How do you want the results to look like?
0
 

Author Comment

by:Angel02
ID: 41730157
In a datagrid which has the following columns

description | code |category | Quantity |Price
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41730178
So you want all the records that match the same grouping to be individually displayed in the grid and you don't want the records in the group flattened?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:Angel02
ID: 41730190
Correct.
So to clarify I could have the same description, code, category from different companys and I would like to display those as one.
0
 
LVL 63

Accepted Solution

by:
Fernando Soto earned 500 total points
ID: 41730397
Hi Angel02;

See if this is what you are looking for.
Dim groupingList = docs.GroupBy(Function(g) New With {Key .Discription = 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()

Open in new window

0
 

Author Comment

by:Angel02
ID: 41733780
Thanks. That's exactly what I wanted.
But for some reason I am not getting the price as a decimal. The response shows as 2.4 but the array returns 2. This is even without the group by

  Dim invenList = docs.Select(Function(x) x).OrderBy(Function(x) x.description).ToList
  Dim testStr As String = invenList.Item(0).Price.ToString()

I also tried using convert.toDouble but it still returns integer.

Dim groupingList = docs.GroupBy(Function(g) New With {Key .Discription = 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 = Convert.ToDouble(g.Max(Function(m) m.Price))}).ToList()

Can you please help?
0
 
LVL 63

Assisted Solution

by:Fernando Soto
Fernando Soto earned 500 total points
ID: 41733791
Does the class Inventory still have an Integer as the data type for Price? If so it would be an issue.  
Public Class Inventory
    Public Property ItemId As Integer
    Public Property description As String
    Public Property Code As String
    Public Property category As String
    Public Property company As String
    Public Property quantity As Integer
    Public Property Price As Integer
End Class

Open in new window

0
 

Author Comment

by:Angel02
ID: 41734861
Thanks! That was it. I changed it to decimal in the class and that fixed it.

Two quick (hopefully not stupid) questions:

Dim groupingList = docs.GroupBy(Function(g) New With {Key .Discription = 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()

In the code above that you provided, .Sum and .Max would be the alias, correct?

Also, you used parameter s in  .Sum = g.Sum(Function(s) s.quantity)
and
m in .Max = g.Max(Function(m) m.Price)

Any reason to use different parameter for each column?
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41734975
Hi Angel02;

To your question, "In the code above that you provided, .Sum and .Max would be the alias, correct?", because, g.Sum(Function(s) s.quantity) and  g.Max(Function(m) m.Price), appear in a Anonymous object that will be returned from the query you need to give it a name in that Anonymous object. The other three values to do not need a name because the compiler can infer the name from the statement, so .Sum and .Max are the names to represent the values on the right of the equals sign.

To your question, "Any reason to use different parameter for each column?" note that g represents the a Function that represents one og the Grouping object and so in the Sum and Max functions we needed to use a different identifier in this case I used s and m but the identifiers s and m could have been the same.
0
 

Author Comment

by:Angel02
ID: 41737547
Got it. Thank you! One more question.

This JSON reponse as another node with sub-node "Contact"

{  
   "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
}
]
    }
 ]
}

How do I read those into the grid so that my grid looks like

description | code |category | Quantity |Price |contactName | contactPhone | contactEmail
0
 
LVL 63

Expert Comment

by:Fernando Soto
ID: 41737744
Hi Angel02;

I will be out for most of the rest of the day so let me ask you a couple of questions and please post the answers in a new question so when I get back I can take a look at it.
  1. Are you using NewtonSoft to convert JSON to classes?
  2. Is the Contact a single contact or is it a array of contacts?
  3. Please post the Classes you are using to convert the JSON to classes
0
 

Author Comment

by:Angel02
ID: 41739087
0

Featured Post

The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

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 …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

734 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