This is a migrated thread and some comments may be shown as answers.

Best way to transpose rows into comma separated value string

0 Answers 27 Views
Grid
This is a migrated thread and some comments may be shown as answers.
Gregory
Top achievements
Rank 1
Gregory asked on 07 May 2014, 05:40 PM
Anyone know the best way to get my rows as a comma separated string?  I got it to work one way, but I think it returns too much data. I built an iqueryable sequence with a sub query. The sub query are the results want to transpose. Then I selected from the returned object and used string.join().  However, the first query repeats my main records and will waste bandwidth. For example (pseudo code):

Table1.Column1 Table1.Column2
1 John Smith

Table2.Column1 Table2.Column2
1 Blue
1 Green

var a = (from Table1 in context
select new Model {
Name = Table1.Column2,
Color = context.table2.where(x => x.Column1 == Table1.Column1).Select(x => x.Column2)
});

When executed a returns multiple names. e.g.

John Smith Blue
John Smith Green

the following does delimit it properly, however i'm concerned of the wasted bandwidth caused by the previous query.  

var b = a.Select(q => new Model
{Name = a.Name,
Color = string.join(",",a.Color)
});

The final result will be like this:

John Smith Blue, Green.

Should I make two connections to the sql server instead? Should I send an array of my IDs to sql or should I loop thru the first results then get the colors? Any suggestions would be great!  I prefer to keep using the DataSourceRequest object, rather than a custom stored procedure where I have to handle the paging/etc manually.  I hope this makes sense...

No answers yet. Maybe you can help?

Tags
Grid
Asked by
Gregory
Top achievements
Rank 1
Share this question
or