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

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