Rico Mariani, the .NET Performance guru, had an article on this very subject. It's not as simple as one might suspect. The basic advice is this:
If your pattern looks like:
x = f1(...) + f2(...) + f3(...) + f4(...)
that's one concat and it's zippy, StringBuilder probably won't help.
If your pattern looks like:
if (...) x += f1(...)
if (...) x += f2(...)
if (...) x += f3(...)
if (...) x += f4(...)
then you probably want StringBuilder.
Yet another article to support this claim comes from Eric Lippert where he describes the optimizations performed on one line +
concatenations in a detailed manner.
If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.
I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH
method can handle the concatenation of the rows very easily.
If there is a table called STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Result I expected was:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
I used the following T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
) [Students]
FROM dbo.Students ST2
) [Main]
You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring
to skip the first one so you don't need to do a sub-query:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
), 2, 1000) [Students]
FROM dbo.Students ST2
Best Answer
This answer shows usage of LINQ (
Aggregate
) as requested in the question and is not intended for everyday use. Because this does not use aStringBuilder
it will have horrible performance for very long sequences. For regular code useString.Join
as shown in the other answerUse aggregate queries like this:
This outputs:
An aggregate is a function that takes a collection of values and returns a scalar value. Examples from T-SQL include min, max, and sum. Both VB and C# have support for aggregates. Both VB and C# support aggregates as extension methods. Using the dot-notation, one simply calls a method on an IEnumerable object.
Remember that aggregate queries are executed immediately.
More information - MSDN: Aggregate Queries
If you really want to use
Aggregate
use variant usingStringBuilder
proposed in comment by CodeMonkeyKing which would be about the same code as regularString.Join
including good performance for large number of objects: