Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.
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?
Asked:
Who is Participating?
You can have a look at these tools to convert SQl to LINQ:
LINQPAD - https://www.linqpad.net/ (Free)
or Linqer http://www.sqltolinq.com/ (paid)
LINQPAD - https://www.linqpad.net/ (Free)
or Linqer http://www.sqltolinq.com/ (paid)
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()
yes, I see his seems to return the SQL for the LINQ but for me it doesn't return the LINQ from the SQL
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?
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?
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:
Produces the following output from SQL -
And the following output from the application using LINQ -
-saige-
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
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?
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()
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.
All Courses
From novice to tech pro — start learning today.
I know you did. I needed to group so that I can do the Sum function so I grouped ALL rows into one group which then allowed me to do the calculations, not really grouping.