Solved

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

Posted on 2016-07-26
12
59 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
  • 6
  • 6
12 Comments
 
LVL 62

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 62

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
 

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 62

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 62

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 62

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 62

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

744 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

12 Experts available now in Live!

Get 1:1 Help Now