Solved

Incorrect group by on LINQ query

Posted on 2016-10-14
5
49 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
  • 2
  • 2
5 Comments
 
LVL 28

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 28

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 33

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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 …
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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