Jess31
asked on
How to do in LINQ??
I am using vb.net. I have a data table with these columns:
Dep
Qty
Cost
Sales
In SQL I would do:
Select
Max(Dep) dep,
count(*) cnt,
Sum(qty) qty,
Sum(Cost*qty) Cost
Sum(sales*qty) Sales
From tbl
Group By dep
Order By dep
How can I do this in LINQ?
Dep
Qty
Cost
Sales
In SQL I would do:
Select
Max(Dep) dep,
count(*) cnt,
Sum(qty) qty,
Sum(Cost*qty) Cost
Sum(sales*qty) Sales
From tbl
Group By dep
Order By dep
How can I do this in LINQ?
ASKER
I tried LINQPAD, it runs my query, but I don't see how to get the conversion to LINQ
Theres an example here
https://sampathloku.blogspot.com/2012/08/how-to-convert-linq-entity-query-into-t.html
It does linq to sql, just check the other options to convert it backwords
https://sampathloku.blogspot.com/2012/08/how-to-convert-linq-entity-query-into-t.html
It does linq to sql, just check the other options to convert it backwords
Hi Jess31;
See if this give you what you are looking for.
See if this give you what you are looking for.
'' 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()
ASKER
To your statement, "yes, I see his seems to return the SQL for the LINQ but for me it doesn't return the LINQ from the SQL". Are you querying the SQL database or the DataTable object in code?
ASKER
SQL Database. The Datatable is created in code, I don't know if I am able to query against it in this tool
Are you using the SQL database to get the information you posted in the question or have you crated a DataTable object and filled it in some other means and wish to query that DataTable?
ASKER
I am using a table on SQL DB that is similar to the DataTable against which I want to run LINQ
Jess, My mistake,
Linqpad doesn't convert SQL to LINQ, only the other way (LINQ to SQL) It relies on the .Net Linq-to-Sql library or Entity framework for the translation.
Linqpad doesn't convert SQL to LINQ, only the other way (LINQ to SQL) It relies on the .Net Linq-to-Sql library or Entity framework for the translation.
So if you are wanting to query the DataTable object that is in local memory then try my last post that has the Linq query. Also in that query you will need to change, dt in dt.AsEnumerable(), to your actual instance name for the DataTable object.
*NO POINTS*
Proof of concept:
Proof of concept:
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
From a set of data in the database -Produces the following output from SQL -And the following output from the application using LINQ --saige-
ASKER
Fernando Soto
I got the code to do the Group Totals, etc.
If I just wanted total aggregation with not groups what change do I need to make ?
I got the code to do the Group Totals, etc.
If I just wanted total aggregation with not groups what change do I need to make ?
Please post the query you are working with. When you say, "If I just wanted total aggregation with not groups", does that mean you do not want any Grouping?
ASKER
Right not grouping.
dt =
Name
Salary
Benefits
And I want grand total of Salary and Benefits (name is ignored)
dt =
Name
Salary
Benefits
And I want grand total of Salary and Benefits (name is ignored)
Hi Jess31;
Something like this should work.
Something like this should work.
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()
But the fields are not the same, is this a different question?
ASKER
But I am asking specifically w/o Grouping, just grand totals
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
LINQPAD - https://www.linqpad.net/ (Free)
or Linqer http://www.sqltolinq.com/ (paid)