LINQ Group and Sum

[gustuner]

Hi, all masters.

I have a problem. I use below code;

Dim dt = New DataTable("tblEntTable")
...
...
dt.Columns.Add("ID", GetType(String))
dt.Columns.Add("Amount", GetType(Decimal))

dt.Rows.Add
.....
.....
Dim res = dt.AsEnumerable()
.OrderBy(Function(row) row.Field(Of String)("ID"))
.GroupBy(Function(row) row.Field(Of String)("ID"))
.[Select](Function(grp) grp.First())

dtgrdClone.DataSource = res.CopyToDataTable


Code is perfect run, but not SUM Amount Fields.

How to SUM "Ammunt" Fields.

Please Help

Kind Regards Gokhan

[KGComputers]

Hi,

I believe you want to group the id's and sum all the amounts for that particular id. Here's how i did it using LINQ.

 Dim result = (From orders In dt.AsEnumerable
                        Group orders By ID = orders.Field(Of String)("ID") Into g = Group
                        Select New With {
                            Key ID,
                            .Amount = g.Sum(Function(r) r.Field(Of Decimal)("amount"))
                       }).OrderBy(Function(tkey) tkey.ID).ToList()

dtgrdClone.DataSource = result

[gustuner]

KGComputers thank you to interest. You are a master,

But there are too many columns in datatable. This code returns just two column "ID" and "amaount"

Real datatable schema like this;

dt.Columns.Add("DATE", GetType(Date))
dt.Columns.Add("ID", GetType(String))
dt.Columns.Add("INFORMATION", GetType(String))
dt.Columns.Add("QTY", GetType(Decimal))
dt.Columns.Add("AMOUNT", GetType(Decimal))
dt.Columns.Add("empID", GetType(Short))
bla
bla
bla

Datatable columns total 60 fields.
I don't want to write for a very long code
What can i do sir.

Thanks.

[gustuner]

KGComputers thank you to interest. You are a master,

But there are too many columns in datatable. This code returns just two column "ID" and "amaount"

Real datatable schema like this;

dt.Columns.Add("DATE", GetType(Date))
dt.Columns.Add("ID", GetType(String))
dt.Columns.Add("INFORMATION", GetType(String))
dt.Columns.Add("QTY", GetType(Decimal))
dt.Columns.Add("AMOUNT", GetType(Decimal))
dt.Columns.Add("empID", GetType(Short))
bla
bla
bla

Datatable columns total 60 fields.
I don't want to write for a very long code
What can i do sir.

Thanks.

Hi,

I believe you want to group the id's and sum all the amounts for that particular id. Here's how i did it using LINQ.

 Dim result = (From orders In dt.AsEnumerable
                        Group orders By ID = orders.Field(Of String)("ID") Into g = Group
                        Select New With {
                            Key ID,
                            .Amount = g.Sum(Function(r) r.Field(Of Decimal)("amount"))
                       }).OrderBy(Function(tkey) tkey.ID).ToList()

dtgrdClone.DataSource = result

[KGComputers]

Datatable columns total 60 fields.

Normally, a datatable does not contain lots of columns as what you have specified. How did you populate the datatable? Through a query?
Or you just add several rows to the datatable at runtime using

dt.Rows.Add(....)