Solved

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

Posted on 2016-07-26
12
61 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

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 …
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

932 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