asked on

Incorrect group by on LINQ query

I am accessing a jSon response using LINQ query. Attached is the Json response, the class I have declared with the fields and my LINQ query.

I have to group the items if they have the same Description, Code, TypeCode, unitsPerBox, color and shipDate.
For the attached json response, I should get the result in my grid as:

Description | TypeCode | unitsPerBox | price | Quantity
ABC | HB | 260 | 4.33 |24
ABC | QB | 12 | 3.6 | 5

Instead, it is summing up both records and I am getting

Description | TypeCode | unitsPerBox | price | Quantity
ABC | HB | 260 | 4.33 |29
jSon_linq_101416.txt
jSonEE_response_101416.txt

Pawan Kumar

So you want to group on Description and want to take max from unitsPerBox, price and Quantity. But how you taking Typecode <<MIN(TypeCode)>> ??

Pawan Kumar

Try..

   Dim dt As DataTable =  New DataTable() 
        dt.Columns.Add("Description", Type.GetType(String))
        dt.Columns.Add("TypeCode", Type.GetType(String))
        dt.Columns.Add("UnitPerBox", Type.GetType(Integer))
        dt.Columns.Add("Price", Type.GetType(Double))
        dt.Columns.Add("Quantity", Type.GetType(Integer))
 
        Dim dr As DataRow =  dt.NewRow() 
        dr(0) = "ABC"
        dr(1) = "HB"
        dr(2) = 260
        dr(3) = 4.33
        dr(4) = 24
        dt.Rows.Add(dr)
 
 
        dr = dt.NewRow()
        dr(0) = "ABC"
        dr(1) = "QB"
        dr(2) = 12
        dr(3) = 3.6
        dr(4) = 5
        dt.Rows.Add(dr)
 
        var result =(from m Function dt.AsEnumerable() As in
                        Description = NewGroup.Key,
                        TypeCode = NewGroup.Min(s => s.Field<string>("TypeCode")),
                        UnitPerBox = NewGroup.Max(s => s.Field<int>("UnitPerBox")),
                        Price = NewGroup.Max(s => s.Field<double>("Price")),
                        Quantity = NewGroup.Sum(s => s.Field<int>("Quantity"))
        End Function
).FirstOrDefault()

Open in new window

ASKER CERTIFIED SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Angel02

ASKER

Thank you it_saige. Worked like a charm!!
'Key' was the key!!

Angel02

ASKER

@Pawan Kumar Khowal

My json response is huge. I had posted just a sample. LINQ is the way to go!