Find Distinct from a DataTable

Question

I have a DataTable and in that Table I have 2 Columns Task_Name and Hours.

Task_Name   Hours
-----------------
Proj1        2
Proj2        3
Proj1        3
Proj1        2
Proj2        5

Now what I finally want is I want a DataTable which will result in:-

Task_Name    Hours
------------------
Proj1         7(addition of hours related to Proj1) 
Proj2         8(addition of hours related to Proj2)

I Hope I am Clear with my question.. How can I do that..??

This may be of help http://stackoverflow.com/questions/10855/linq-query-on-a-datatable — unlimit, Jul 24 '13 at 11:14

Sergey Berezovskiy · Accepted Answer · 2013-07-24T11:22:14.687

2

You can use LINQ to DataSet. Calculate aggregated hours for tasks:

var query = from r in table.AsEnumerable()
            group r by r.Field<string>("Task_Name") into g
            select new {
               Task_Name = g.Key,
               Hours = g.Sum(x => x.Field<int>("Hours"))
            };

And use this CopyToDataTable() extension to create new DataTable from anonymous types (if you really need DataTable):

var result = query.CopyToDataTable();

edited Jul 24 '13 at 11:22

answered Jul 24 '13 at 11:15

Sergey Berezovskiy

232,247
41
429
459

Why do you have to do `.AsEnumerable()`? – Cédric Bignon Jul 24 '13 at 11:16
@CédricBignon I believe `DataTable` does not implement `IEnumerable`, so you need to call this extension to use LINQ to DataSet methods – Sergey Berezovskiy Jul 24 '13 at 11:18
var result = query.CopyToDataTable(); is not working in my case..Error-Error 2 The type 'AnonymousType#1' cannot be used as type parameter 'T' in the generic type or method 'System.Data.DataTableExtensions.CopyToDataTable(System.Collections.Generic.IEnumerable)'. There is no implicit reference conversion from 'AnonymousType#1' to 'System.Data.DataRow'. – Vishal Jul 24 '13 at 11:27
@Farzi that's why I added link to which CopyToDataTable method you will need :) Default implementation works only for types which are DataRow, but this MSDN article has implementation of creating datatable from any type. Just copy-paste code from article into your application – Sergey Berezovskiy Jul 24 '13 at 11:37
@lazyberezovsky-Thanx it worked but I want to know one more thing how to avoid null values if value of hours is null coming from database. What change do I need to make to avoid null in hours – Vishal Jul 24 '13 at 12:05
@Farzi you can add `where !r.IsNull("Hours")` just before groupping – Sergey Berezovskiy Jul 24 '13 at 12:50
Thankx and how to check if TaskName is not empty..?? – Vishal Jul 24 '13 at 13:00

Find Distinct from a DataTable

1 Answers1