Can Linq solve the problem?

[John]

Hi,

If I have a ADO.NET dataTable contains information like

OrderID, State, SalesAmnt
====== === =======
1 CA 100
2 TX 30
3 WA 50
4 TX 120
5 CA 20

What Linq syntax would be able to produce same results as

SELECT Sum(SalesAmt) As TotalSales, Count(OrderID) As NumberOfOrder
FROM Table
GROUP BY State

If not possible by Linq, Can it be done using SQL Compact?

Thanks very much!
John

 

[Tom Shelton]

This is a multi-part message in MIME format.

------=_NextPart_000_04B3_01C995BE.32A3F2E0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

Hi,=20

If I have a ADO.NET dataTable contains information like

OrderID, State, SalesAmnt
=3D=3D=3D=3D=3D=3D =3D=3D=3D =3D=3D=3D=3D=3D=3D=3D
1 CA 100
2 TX 30
3 WA 50
4 TX 120
5 CA 20

What Linq syntax would be able to produce same results as

SELECT Sum(SalesAmt) As TotalSales, Count(OrderID) As NumberOfOrder
FROM Table
GROUP BY State

If not possible by Linq, Can it be done using SQL Compact?

Thanks very much!
John

I'm not exactly sure what you meant by a DataTable in ado.net... But, I'm
assuming you meant you have a populated DataTable object and want to query it.
Well, it is possilbe - using the System.Data.DataTableExtension methods:

Option Strict On
Option Explicit On

Imports System
Imports System.Data.DataTableExtensions

Module Module1

Sub Main()

Dim table As DataTable = GenerateTable()

Dim sales = From o In _
(From row In table.AsEnumerable() Select New With {.ID = row.Field(Of Integer)(0), .State = row.Field(Of String)(1), .Amount = row.Field(Of Decimal)(2)}) _
Group By o.State Into TotalSales = Sum(o.Amount), NumberOfOrders = Count()

For Each sale In sales

Console.WriteLine("State={0}, Sum={1}, Orders={2}", sale.State, sale.TotalSales, sale.NumberOfOrders)
Next

End Sub

Private Function GenerateTable() As DataTable
Dim dt As New DataTable("SalesInfo")

dt.Columns.Add(New DataColumn("OrderId", GetType(Integer)))

Dim stateColumn As New DataColumn("State", GetType(String))
stateColumn.MaxLength = 2
dt.Columns.Add(stateColumn)

dt.Columns.Add(New DataColumn("SalesAmnt", GetType(Decimal)))

dt.Rows.Add(1, "CA", 100)
dt.Rows.Add(2, "TX", 30)
dt.Rows.Add(3, "WA", 50)
dt.Rows.Add(4, "TX", 120)
dt.Rows.Add(5, "CA", 20)

dt.AcceptChanges()

Return dt
End Function

End Module

HTH

 

[Michael C]

Dim sales = From o In _
(From row In table.AsEnumerable() Select New With {.ID
= row.Field(Of Integer)(0), .State = row.Field(Of String)(1), .Amount =
row.Field(Of Decimal)(2)}) _
Group By o.State Into TotalSales = Sum(o.Amount),
NumberOfOrders = Count()

Look at all those squiggly brackets! Who would use such a language that used
squigglies! They are so ugly.

Michael

PS, Cor and Herfried I'm kidding guys.

 

[Michael C]

Hi,

If I have a ADO.NET dataTable contains information like

OrderID, State, SalesAmnt
====== === =======
1 CA 100
2 TX 30
3 WA 50
4 TX 120
5 CA 20

What Linq syntax would be able to produce same results as

SELECT Sum(SalesAmt) As TotalSales, Count(OrderID) As NumberOfOrder
FROM Table
GROUP BY State

If not possible by Linq, Can it be done using SQL Compact?

Doesn't the sql provided work with sql compact?




Thanks very much!
John

 

[Colbert Zhou [MSFT]]

Hello John,

I have tested Tom's codes in my side. It works fine and returns the
expected result. If you have any future questions or concerns on the same
problem, please feel free to update this thread and I will provide follow
up support.

Also thanks for Tom's input! Wish you both have a good day!

Best regards,
Colbert Zhou (colbertz @online.microsoft.com, remove 'online.')
Microsoft Online Community Support

Delighting our customers is our #1 priority. We welcome your comments and
suggestions about how we can improve the support we provide to you. Please
feel free to let my manager know what you think of the level of service
provided. You can send feedback directly to my manager at:
(e-mail address removed).

 

[Branco]

John wrote:

What Linq syntax would be able to produce same results as

SELECT Sum(SalesAmt) As TotalSales, Count(OrderID) As NumberOfOrder
FROM Table
GROUP BY State

<snip>

I'm sure Tom Shelton's post put you in the right track. I'd just like
to add that, if you want your Linq query a little less verbose, you
can make do without the strongly typed intermediary results suggested
by Tom. I mean, the Group By clause already produces a strongly typed
result, which (it seems to me) is all that you'd need in the end.

Therefore, this is how the query could also be expressed (just a small
variation on Tom's solution):

<example>
Dim SalesByState = _
From Row In Table.AsEnumerable _
Group By State = CType(Row("State"), String) _
Into _
TotalSales = Sum(Ctype(Row("SalesAmt"), Decimal)), _
NumberOfOrders = Count(), _
Rows = Group
</example>

This query will create an enumerable (SalesByState) for a strongly
typed (even though anonymous) class with the following fields:

State As String
TotalSales As Decimal
NumberOfOrders As Integer
Rows As IEnumerable(Of DataRow)

The bonus in this solution is that you get all the rows that are
grouped under a given State already packed in the Rows field. This is
better than the original SQL, which would require individual queries
to get each of these groups.

HTH.

Regards,

Branco.