Linq to DataTable. How?

Question

hi all,

         I am having two separate datatables. table 1 consists of 5 columns and table 2 consists of 2 columns. i have joined these two and selected only two columns from it. now in my joined datatable, i have only two columns namely things and id. 

 

things          id

pen          001

pencil       002

eraser      003

pen          004

pen         005

pencil      006

eraser     007

eraser     008

eraser     009

 

this is what my joined datatable looks like. now i wann the result lik the table which is shown as below..

Resultant Table shud be like this,

 

things         idCount

pen              3

pencil           2

eraser          4

 

i need the result lik i have shown above, please help me by providing a linq query to solve my problem.. 

if possible, please send as a single query which comprises the joining of two tables, then bringing out the result as i have shown above.. 

its really urgent frenz, help me...! i have application delivery in a couple of days. so pls help me as soon as possible.

 

thanks in advanz.

abivenkat

Answer 1

You have to do it using 2 steps. Ex:

//Declare the computed datatable
DataTable dtNew = new DataTable();
 
//Add Columns
dtNew.Columns.Add("things", typeof(string));
dtNew.Columns.Add("idCount", typeof(int));

//Get Distinct values
var x = (from r in dt.AsEnumerable()
 select r["things"]).Distinct().ToList();
 
 
//Now for the values....

foreach(DataRow dr in x)
{
   DataRow drNew = dtNew.NewRow();
   drNew["things"] = dr["things"];
   drNew["idCount"] = (dt.Select("things"=  drNew["things"])).Count;
}
 
dtNew.AcceptChanges();

 

This is an example code and not tested. Please correct if stops at any point.

Thanks

---

Anirban Bhattacharya (Lead Consultant - ESSPL)

Answer 2

Where are you getting the datatable from? If you are getting it from SQL then why not make the aggregation in the query itself?

You can perform aggregation using Linq in this manner:

 

var result = from row in dt.Rows.Cast<DataRow>()
       group row by new { Things = (string)row[0] } into g
       select new { g.Key.Things, IdCount = g.Count() };
   

This will however, return an anonymous type.

I believe looping and grouping manually will be more efficient in this case

 

---

Please mark this as answer or vote as helpful if it solved your problem