How to do in LINQ??

'' Change all Field(0f Integer) to the correct DataType
Dim results = (From r In dt.AsEnumerable()
               Group r By Key = r.Field(Of Integer)("Dep") Into depGroup = Group
               Order By Key
               Select New With {
                   .Dep = Key,
                   .Cnt = depGroup.Count(),
                   .Qty = depGroup.Sum(Function(q) q.Field(Of Integer)("Qty")),
                   .Cost = depGroup.Sum(Function(c) c.Field(Of Integer)("Qty") * c.Field(Of Integer)("Cost")),
                   .Sales = depGroup.Sum(Function(c) c.Field(Of Integer)("Qty") * c.Field(Of Integer)("Sales"))
               }).ToList()
               

Select all Open in new window

Imports System.Data.SqlClient

Module Module1
	Sub Main()
		Dim adapter As SqlDataAdapter = Nothing
		Dim table As DataTable = Nothing
		Dim [list] As List(Of Department)
		Try
			Connection.Open()
			If Connection.State.Equals(ConnectionState.Open) Then
				table = New DataTable()
				[list] = New List(Of Department)()
				adapter = New SqlDataAdapter("SELECT * FROM SALESTABLE", Connection)
				adapter.Fill(table)
				For Each row As DataRow In table.Rows
					[list].Add(New Department() With {.Department = row("DEPT"), .Quantity = row("QTY"), .Cost = row("COST"), .Sales = row("SALES")})
				Next
			End If
		Finally
			If adapter IsNot Nothing Then adapter.Dispose()
		End Try
		Dim a1 = (From row In table
				Order By row("DEPT")
				Group row By DEPT = row("DEPT") Into g = Group
				Select New With
					  {
						Key DEPT,
						    .CNT = g.Count(),
						    .QTY = g.Sum(Function(x) x("QTY")),
						    .COST = g.Sum(Function(x) x("COST") * x("QTY")),
						    .SALES = g.Sum(Function(x) x("SALES") * x("QTY"))
						 })
		Dim a2 = (From item In [list]
			Order By item.Department
			Group item By DEPT = item.Department Into g = Group
			Select New With
			{
				Key DEPT,
				    .CNT = g.Count(),
				    .QTY = g.Sum(Function(x) x.Quantity),
				    .COST = g.Sum(Function(x) x.Cost * x.Quantity),
				    .SALES = g.Sum(Function(x) x.Sales * x.Quantity)
				})

		Console.WriteLine("From a datatable")
		For Each group In a1
			Console.WriteLine(group)
		Next

		Console.WriteLine()
		Console.WriteLine("From a list")
		For Each group In a2
			Console.WriteLine(group)
		Next
		Console.ReadLine()
	End Sub
End Module

Module Globals
	Public Property Connection() As SqlConnection = New SqlConnection("Data Source=.;Initial Catalog=EE_Q28970321;Integrated Security=True")
End Module

Class Department
	Public Property Department() As Integer
	Public Property Quantity() As Integer
	Public Property Cost() As Decimal
	Public Property Sales() As Decimal
End Class

Select all Open in new window

Dim results = (From r In dt.AsEnumerable()
               Group By Key = "OneGroup" Into justOneGroup = Group
               Select New With {
                   .Salary = justOneGroup.Sum(Function(s) s.Field(Of Integer)("Salary")),
                   .Benefits = justOneGroup.Sum(Function(c) c.Field(Of Integer)("Benefits")
               }).ToList()

Select all Open in new window