Use LINQ to group data from DataTable

Question

I want to use LINQ to group data from a DataTable (columns: userid, chargetag, charge).

The content could look like this:

userid	chargetag	charge
user1	tag3	100
user2	tag3	100
user3	tag5	250

I need something like this as a result:

chargetag	count	sum
tag3	2	200
tag5	1	250

This is what I have so far:

var groupedData = from b in dataTable.AsEnumerable()
                  group b by b.Field<string>("chargetag") into g
                  let count = g.Count()
                  select new
                  {
                      ChargeTag = g.Key,
                      Count = count,
                  };

I can extract the name of the chargetag and the number of it. How would I have to change the LINQ query to access the sum of charges as well?

Jon Skeet · Accepted Answer · 2010-09-30 09:05:35Z

47

That's pretty easy - just use the Sum extension method on the group.

var groupedData = from b in dataTable.AsEnumerable()
                  group b by b.Field<string>("chargetag") into g
                  select new
                  {
                      ChargeTag = g.Key,
                      Count = g.Count(),
                      ChargeSum = g.Sum(x => x.Field<int>("charge"))
                  };

(I've removed the let clause here as it wasn't really buying you anything.)

Now that may be inefficient; it may end up grouping twice in order to perform two aggregation operations. You could fix that like with a query continuation like this, if you really wanted:

var groupedData = from b in dataTable.AsEnumerable()
                  group b by b.Field<string>("chargetag") into g
                  select new
                  {
                      ChargeTag = g.Key,
                      List = g.ToList(),
                  } into g
                  select new 
                  {
                      g.ChargeTag,
                      Count = g.List.Count,
                      ChargeSum = g.List.Sum(x => x.Field<int>("charge"))
                  };

Or with a let clause instead:

var groupedData = from b in dataTable.AsEnumerable()
                  group b by b.Field<string>("chargetag") into g
                  let list = g.ToList()
                  select new
                  {
                      ChargeTag = g.Key,
                      Count = list.Count,
                      ChargeSum = list.Sum(x => x.Field<int>("charge"))
                  };

answered Sep 30, 2010 at 9:05

Jon Skeet

1.5m893 gold badges9.3k silver badges9.3k bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kjn Over a year ago

Nice explanation (as usual). I'd be interested to know if the first option actually groups twice or if it gets optimised.

user153923 Over a year ago

@Jon Skeet: What's a good way for me to grasp all this LINQ stuff? I've got your C# In Depth book, but I get lost in the LINQ section (like your example above). The rest of the coding stuff isn't too bad.

Jon Skeet Over a year ago

@jp2code: Well, there are various books just about LINQ... or you might like my Edulinq blog series: msmvps.com/blogs/jon_skeet/archive/tags/Edulinq/default.aspx

RookieAppler Over a year ago

Can someone give me the code in VB? I tried the converters online but I get an error "'Into' expected".

Jon Skeet Over a year ago

@RookieAppler: Rather than try converters, I suggest you read the documentation for grouping with LINQ in VB - then if you're still having problems, ask a new question with details of what you're trying to do and what you've tried so far.

Collectives™ on Stack Overflow

Use LINQ to group data from DataTable

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related