Solved

Incorrect group by on LINQ query

Posted on 2016-10-14
5
77 Views
Last Modified: 2016-10-14
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
0
Comment
Question by:Angel02
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41843355
So you want to group on Description and want to take max from unitsPerBox, price and Quantity. But how you taking Typecode <<MIN(TypeCode)>> ??
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41843390
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

0
 
LVL 34

Accepted Solution

by:
it_saige earned 500 total points
ID: 41843749
I think you meant to do this:
Imports Newtonsoft.Json

Module Module1
	Const response = "{""message"":""OK"",""invenItems"":[" & _
		"{""invenId"":123,""Description"":""ABC"",""Code"":""FDS"",""category"":""G"",""quantity"":4,""TypeCode"":""HB"",""unitsPerBox"":260,""price"":4.06,""color"":""White"",""shipDate"":""2016-10-24"",""company"":""X""}," & _
		"{""invenId"":234,""Description"":""ABC"",""Code"":""FDS"",""category"":""G"",""quantity"":12,""TypeCode"":""HB"",""unitsPerBox"":260,""price"":4.33,""color"":""White"",""shipDate"":""2016-10-24"",""company"":""X""}," & _
		"{""invenId"":456,""Description"":""ABC"",""Code"":""FDS"",""category"":""G"",""quantity"":8,""TypeCode"":""HB"",""unitsPerBox"":260,""price"":3.79,""color"":""White"",""shipDate"":""2016-10-24"",""company"":""X""}," & _
		"{""invenId"":567,""Description"":""ABC"",""Code"":""FDS"",""category"":""G"",""quantity"":5,""TypeCode"":""QB"",""unitsPerBox"":12,""price"":3.66,""color"":""White"",""shipDate"":""2016-10-24"",""company"":""X""}" & _
		"]}"

	Sub Main()
		Dim parsed = JsonConvert.DeserializeObject(Of Inventory)(response)
		Console.WriteLine("Message: {0}", parsed.Message)
		Dim grouped = parsed.Items.GroupBy(Function(x) New With
											  {
												  Key x.Description,
												  Key x.Code,
												  Key x.TypeCode,
												  Key x.UnitsPerBox,
												  Key x.Color,
												  Key x.ShipDate
											  },
									Function(key, group) New Item() With
													 {
														 .ID = group.Max(Function(x) x.ID),
														 .Description = key.Description,
														 .Code = key.Code,
														 .Category = group.First().Category,
														 .Quantity = group.Sum(Function(x) x.Quantity),
														 .TypeCode = key.TypeCode,
														 .UnitsPerBox = key.UnitsPerBox,
														 .Price = group.Max(Function(x) x.Price),
														 .Color = key.Color,
														 .ShipDate = key.ShipDate,
														 .Company = ""
													 })
		For Each group In grouped
			Console.WriteLine(group)
		Next
		Console.ReadLine()
	End Sub
End Module

Class Inventory
	<JsonProperty("message")> _
	Public Property Message() As String
	<JsonProperty("invenItems")> _
	Public Property Items() As List(Of Item)
End Class

Class Item
	<JsonProperty("invenId")> _
	Public Property ID() As Integer
	Public Property Description() As String
	Public Property Code() As String
	<JsonProperty("category")> _
	Public Property Category() As String
	<JsonProperty("quantity")> _
	Public Property Quantity() As Integer
	Public Property TypeCode() As String
	<JsonProperty("unitsPerBox")> _
	Public Property UnitsPerBox() As Integer
	<JsonProperty("price")> _
	Public Property Price() As Decimal
	<JsonProperty("color")> _
	Public Property Color() As String
	<JsonProperty("shipDate")> _
	Public Property ShipDate() As Date
	<JsonProperty("company")> _
	Public Property Company() As String

	Public Overrides Function ToString() As String
		Return String.Format("ID: {0}; Description: {1}; Code: {2}; Category: {3}; Quantity: {4}; TypeCode: {5}; UnitsPerBox: {6}; Price: {7}; Color: {8}; ShipDate: {9}; Company: {10}", ID, Description, Code, Category, Quantity, TypeCode, UnitsPerBox, Price.ToString("C2"), Color, ShipDate.ToString("yyyy-MM-dd"), Company)
	End Function
End Class

Open in new window

Which produces the following output -Capture.JPG
-saige-
0
 

Author Comment

by:Angel02
ID: 41843815
Thank you it_saige. Worked like a charm!!
'Key' was the key!!
0
 

Author Comment

by:Angel02
ID: 41843818
@Pawan Kumar Khowal

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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

636 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