groupby in datatable

nishant ranjan

13y10.6k.NET

ds_update.ReadXml(strOutPath + @"\backup.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

15 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes13y ago
Accepted 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 ranjan13y ago
@vulpes:- thanks a lot. worked perfectly fine.
nishant ranjan13y ago
it giving error on select

cannot implicitly convert type system.collections.generic.ienumerable to system.collections.generic.ienumerable
Vulpes13y ago
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 ranjan13y ago
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
Vulpes13y ago
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 ranjan13y ago
m using 3.5
nishant ranjan13y ago
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 Trivedi13y ago
Hi,
In this case Var is not useful because it is strongly typed. at compile time,the 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.
Vulpes13y ago
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 ranjan13y ago
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 Lenka13y ago
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 Trivedi13y ago
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 Choudhary13y ago
or just use a single line with LINQ using lambda expressions:
```
DataTable table = // var groups = table.AsEnumerable().GroupBy(r => r.Field<T>("yourColumn"))
```
Vulpes13y ago
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);
}