CODEWITHSUNDEEP
.netvb.netlinqgroup-bysum
Ramesh Durai
Ramesh Durai

Reputation: 2686

GroupBy and Sum on a DataTable

I am using LINQ in VB on a datatable to filter my results.

m_oDataTable.Columns.Add("Name1", GetType(String))
m_oDataTable.Columns.Add("Name2", GetType(String))
m_oDataTable.Columns.Add("Time", GetType(Double))

I am trying to filter the datatable by Name1 or Name2 based on user selection.

groupBy = "Name1" 
'groupBy = "Name2"

I grouped my data but I was unable to Sum the desired field.

Dim test =  From tab In m_oDataTable.AsEnumerable()
            Group tab By groupDt = tab.Field(Of String)(groupBy) Into Group
            Select Group

'Getting Error
Dim test2 = From tab In m_oDataTable.AsEnumerable()
            Group tab By groupDt = tab.Field(Of String)(groupBy) Into Group
            Select New With 
            {
             .Grp = Key, ' Error in this line(Key is a type, cannot be used as a expression)
             .Sum = Group.Sum(Function(r) Double.Parse(r.Item("Time").ToString()))
            }

I tried in c# and got the desired result. But have no luck with VB :(

var test = from tab in m_oDataTable.AsEnumerable()
           group tab by tab.Field<string>(groupBy)
                 into groupDt
                 select new
                 {
                     Group = groupDt.Key,
                     Sum = groupDt.Sum(r => double.Parse(r["Time"].ToString()))
                 };

How to achieve this in VB?

Upvotes: 0

Views: 10960

Answers (1)

Dave Williams
Dave Williams

Reputation: 2246

Not 100% sure why but VB.Net does not support .Key on query expression syntax. You actually define the key variable in the query. You are trying to use a variable "Key" which has not been defined. Instead you need to use they key you defined in the query (groupDt).

Change one small line and it should work...

Select New With 
                {
                 .Grp = groupDt,
                 .Sum = Group.Sum(Function(r) Double.Parse(r.Item("Time").ToString()))
                }

OR you can use fluent syntax:

Dim test2 = Table.AsEnumerable().GroupBy(Function(row) row.Item("Name1")).Select(Function(group) New With {.Grp = group.Key, .Sum = group.Sum(Function(r) Double.Parse(r.Item("Time").ToString()))})

Upvotes: 2

Related Questions