group a table by a column which has select distinct on colmn

diamond diamond

Hi ,

I have a table with some fields, it is not easy to describe my issue but, I want to group table by a src field which is have select distinct on dst field.

Table name is F has src, name, dst, time fields.

I want my query by linq is resulted as below:

src dst src dst

1 a

1 c 1 a,c

1 a ====>> 2 b

2 b 3 c

3 c

I want for every src the dst are put in list.

this code gives an error.

List srcaddress = (from DataRow dr in dt.Rows group dr by dr["dst"].ToString() Select new dr["src"]).ToList();

7 Replies

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

Abhishek Jain13y ago
Accepted answer
Sorry, i am unable to understand,
For every src if the count if dst is greater than 0 then what you want is that the comma separated dst should be put into variable, but if there are like to src having 2 dst each then will you create two variables, if so you can do something like this:

var qry = from dr in dt.AsEnumerable()
group dr by dr["src"] into rowsGroup
select new
{
src = rowsGroup.Key,
dst = string.Join(",", rowsGroup.Select(av => av["dst"].ToString()).Distinct().ToList().ToArray()),
dstCount = rowsGroup.Select(av => av["dst"].ToString()).Distinct().Count()
};

Now you have bith the count and the comma separated list of dst for each src, you can do anything with this list by making a for loop or foreach loop of linq.

And You can try something like this:

var list = qry.ToList();

list.ForEach(...);

Hope it helps..

Regards
AJ
diamond diamond13y ago
Thanks,

Just let me know, in src variable save index or string of values?
because my values in src are string .

If I want to say if the src whoes more than 2 dst put in srcaddress list

List srcaddress=new List () ;
foreach (var n in qry)
{
if (n.dst.count>2 )
srcaddress = n.src.ToString ();
}
diamond diamond13y ago
Many thanks Abhishek,

I need to list of string of dst too, but I couldn't put the srcaddress list.
I want to say if every src has more than t dst put it a variable.
Let me know how can I do?
Abhishek Jain13y ago
In your case you can also try this:

var qry = from dr in dt.AsEnumerable()
group dr by dr["src"] into rowsGroup
select new
{
src = rowsGroup.Key,
dstCount = rowsGroup.Select(av => av["dst"].ToString()).Distinct().Count()
};
Abhishek Jain13y ago
Sorry about the query, please try with this:

var qry = from dr in dt.AsEnumerable()
group dr by dr["src"] into rowsGroup
select new
{
src = rowsGroup.Key,
dst = string.Join(",", rowsGroup.Select(av => av["dst"].ToString()).Distinct().ToList().ToArray())
};

Regards
AJ
diamond diamond13y ago
Thanks Abhishek,

It gives an error :

if (dt != null)
{

var qry = from dr in dt.Rows
group dr by dr["saddr"] into rowsGroup
select new
{
src = rowsGroup.Key,
dst = string.Join(",", rowsGroup.Select(av => av["daddr"].ToString()).Distinct().ToList().ToArray())
};
List srcaddress = qry.ToList(); //if (col1 .Container () == hosts[a])
if (srcaddress.Contains(hosts[a], StringComparer.OrdinalIgnoreCase)&& srcaddress.count >= t)
Outgo_matrix[0, a] = 1;
else
Outgo_matrix[0, a] = 0;
}
}
Error:Could not find an implementation of the query pattern for source type 'System.Data.DataRowCollection'. 'GroupBy' not found. Consider explicitly specifying the type of the range variable 'dr'.

I want to put condition if the count of dst of every src is more than t, add 1 to matrix.
Abhishek Jain13y ago
Try this:

var qry = from dr in dt.Rows
  group dr by dr["src"] into rowsGroup
select new
    {
src = rowsGroup.Key,
  dst = string.Join(",", rowsGroup.Select(av => av["dst"].ToString()).Distinct().ToList().ToArray())
};