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

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?
Angel02Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Where do you want the results of your final query to appear in? / How do you want the results to look like?
0
Angel02Author Commented:
In a datagrid which has the following columns

description | code |category | Quantity |Price
0
Fernando SotoRetiredCommented:
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
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Angel02Author Commented:
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
Fernando SotoRetiredCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Angel02Author Commented:
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
Fernando SotoRetiredCommented:
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
Angel02Author Commented:
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
Fernando SotoRetiredCommented:
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
Angel02Author Commented:
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
Fernando SotoRetiredCommented:
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
Angel02Author Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
LINQ Query

From novice to tech pro — start learning today.

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.