Link to home
Start Free TrialLog in
Avatar of Angel02
Angel02

asked on

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?
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Where do you want the results of your final query to appear in? / How do you want the results to look like?
Avatar of Angel02
Angel02

ASKER

In a datagrid which has the following columns

description | code |category | Quantity |Price
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?
Avatar of Angel02

ASKER

Correct.
So to clarify I could have the same description, code, category from different companys and I would like to display those as one.
ASKER CERTIFIED SOLUTION
Avatar of Fernando Soto
Fernando Soto
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Angel02

ASKER

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?
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Angel02

ASKER

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?
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.
Avatar of Angel02

ASKER

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
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