Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 98
  • Last Modified:

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
0
Angel02
Asked:
Angel02
  • 2
  • 2
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
Pawan KumarDatabase ExpertCommented:
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
 
it_saigeDeveloperCommented:
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
 
Angel02Author Commented:
Thank you it_saige. Worked like a charm!!
'Key' was the key!!
0
 
Angel02Author Commented:
@Pawan Kumar Khowal

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now