groupby in datatable

Question

ds_update.ReadXml(strOutPath + @ackup.xml); //ds_update is dataset data contained is eid mid path 112 500 xyz 112 501 abc 113 500 ddd 113 500 aaa 113 600 uuu what i want is result as mid 500 3 501 1 600 1 how can i use group by in datatable.select DataRow[] results = ds_update.Tables[0].Select(?????????); foreach (DataRow dr in results) { } i want each mid with no of times its occurrence

Vulpes · Answer

Sorry I didn't realize that you were still using the query which returns an IEnumerable of an anonymous type. To do something like you want to do I think you'll have to use a 'named' type instead. If you add this class: class MidCount { public object Mid { get; set; } public int Count { get; set; } } and then change the rest of the code to this then it should work: IEnumerable query = null; if (condition) { query = from DataRow dr in ds_update.Tables[0].Rows where dr[eid].ToString() == 113 group dr by dr[mid] into g select new MidCount {Mid = g.Key Count = g.Count()}; } else { query = from DataRow dr in ds_update.Tables[0].Rows where dr[eid].ToString() == 113 && date == something group dr by dr[mid] into g select new MidCount {Mid = g.Key Count = g.Count()}; }

nishant ranjan · Answer

@vulpes:- thanks a lot. worked perfectly fine.

nishant ranjan · Answer

it giving error on select cannot implicitly convert type system.collections.generic.ienumerable to system.collections.generic.ienumerable

Vulpes · Answer

Instead of: var query; try instead: IEnumerable query = null; However you'll also need to add: select dr; at the end of both your queries for the syntax to be valid.

nishant ranjan · Answer

i was asking on if condition i.e. somting like below var query; if(condition) { query= from datarow dr in ds_update.table[0].rows where dr[eid].tostring()==113 ....... } else { query=from datarow dr in ds_update.table[0].rows where dr[eid].tostring()==113 and date=something ....... } i have to do some thing like this

Vulpes · Answer

Yes you can certainly include a 'where' clause in a LINQ query. For example: var query = from DataRow dr in ds_update.Tables[0].Rows where dr[eid].ToString() == 113 group dr by dr[mid] into g select new{Mid = g.Key Count = g.Count()};

nishant ranjan · Answer

m using 3.5

nishant ranjan · Answer

i was asking in ref to ur solution given. var query=from datarow... in this i want to add where clause based on if condition is it possible. because if i declare var query =; and then assign the syntax then its giving error. it is cause of initializing with string. what should i assign so that i can achive my req.

Jignesh Trivedi · Answer

Hi In this case Var is not useful because it is strongly typed. at compile timethe compiler determines the type. so if you are .net framework 4.0 user than use can use dynamic instead of var. dynamic query = null; if(condition) { query=... } else { query=... } Please refer http://msdn.microsoft.com/en-us/library/dd264736.aspx http://msdn.microsoft.com/en-us/library/vstudio/dd264741.aspx hope this will help you.

Vulpes · Answer

No the DataTable.Select method uses a special syntax which is described here: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx Although there are some similarities to SQL it's far more limited and AFAICS there are no constructs available which would enable you to do GROUP BY.

nishant ranjan · Answer

thanks for rpy but can't we change the query with where clause??? that is if(condition) { query=... } else { query=... } its giving error. var query=; string conversion

Sanjeeb Lenka · Answer

check this link http://stackoverflow.com/questions/6551447/c-using-linq-to-group-by-multiple-columns-in-a-datatable http://www.codeproject.com/Articles/28132/Implementing-Simple-SQL-Group-by-Functionality-wit

Jignesh Trivedi · Answer

hi Please refer http://stackoverflow.com/questions/8472005/efficient-datatable-group-by it has multiple way to group by with datatable hope this will help you.

Amit Choudhary · Answer

or just use a single line with LINQ using lambda expressions: DataTable table = // var groups = table . AsEnumerable (). GroupBy ( r => r . Field T >( yourColumn ))

Vulpes · Answer

I don't think it's possible to do this using the DataTable.Select method but you can do it using LINQ: using System.Linq; // .... var query = from DataRow dr in ds_update.Tables[0].Rows group dr by dr[mid] into g select new{Mid = g.Key Count = g.Count()}; foreach(var obj in query) { Console.WriteLine({0} {1} obj.Mid obj.Count); }