Query Over LINQ

[sanndeb]

First of all sorry if i put the question in wrong section... :)

In sql server the following code adds varchar values separated by a comma sign and you can see the output in the image attached... [I found the code somewhere on the web]

CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1))
 
INSERT INTO #YourTable ([ID],[Name]) VALUES (1,'A')
INSERT INTO #YourTable ([ID],[Name]) VALUES (1,'B')
INSERT INTO #YourTable ([ID],[Name]) VALUES (2,'C')
 
select * from #YourTable
 
SELECT 
      [ID]
    , REPLACE(RTRIM((SELECT [Name] + ' ' FROM #YourTable WHERE (ID = Results.ID) FOR XML PATH (''))),' ',', ') AS NameValues
FROM #YourTable Results
GROUP BY ID
 
DROP TABLE #YourTable

Now my question is how do i perform this operation using C# LINQ queries when i have a datatable or a table in sql server and i want that kind of output.

[Christian Binder]

E.g. you've got this data-source
 
List<KeyValuePair<int, char>> kvps = 
  new List<KeyValuePair<int, char>>();
 
kvps.Add(new KeyValuePair<int,char>(1, 'A'));
kvps.Add(new KeyValuePair<int,char>(1, 'B'));
kvps.Add(new KeyValuePair<int,char>(2, 'C'));
 

If it's a a datatable, there would be some slight differences, but generally this way should work

var strs = from kvp in kvps
           let str = kvp.Value.ToString()
           group str by kvp.Key into grp
           select new { 
             grp.Key, 
             CSV = string.Join(", ", grp.ToArray()) 
           };
 

It should be obvious, what's going on, but I give a few hints: if there were strings instead of chars, the second line (let str = ...) can be omitted and instead of group str by ... you could use group kvp.Value by ...
At least there will be created an anonymous type have two properties Key and CSV

[sanndeb]

Thanks Mate... :)
In my case the data will flow from a linq datacontext and also little bit complected as it contains 4 joins... but i got the general idea... and will let you know after trying it....


@ChBinder : But it'll not work when data comes from sql server as string.Join(", ", grp.ToArray()) can't be translated into sql queries by linq....