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

Question

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<string> srcaddress = (from DataRow dr in dt.Rows group dr by dr["dst"].ToString() Select new dr["src"]).ToList();

Abhishek Jain · 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 diamond · Answer

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 diamond · Answer

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 Jain · Answer

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 Jain · Answer

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 diamond · Answer

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 Jain · Answer

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()) };

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

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (7)