Solved

Incorrect group by on LINQ query

Posted on 2016-10-14
5
46 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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

If you're writing a .NET application to connect to an Access .mdb database and use pre-existing queries that require parameters, you've come to the right place! Let's say the pre-existing query(qryCust) in Access takes a Date as a parameter and l…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

773 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