?
Solved

Incorrect group by on LINQ query

Posted on 2016-10-14
5
Medium Priority
?
80 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 30

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 30

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 2000 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

Technology Partners: 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

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.
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

719 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