Linq to SQL to append multiple records with a delimiter

Question

Sorry for the bad title of the question apologies if it's a duplicate.

I have two db tables:

Users                 Documents
-------               --------- 
ID                    ID
Name                  DocumentName
                      UserID

Say I have 1 record in Users

1, "bob"

and three related records in Documents

1, "Doc1", 1
2, "Doc2", 1
3, "Doc3", 1

I want to generate a result set:

1, "bob", "Doc1, Doc2, Doc3"

I've tried various things involving merging of multiple result sets but get the error: Local sequence cannot be used in LINQ to SQL implementation of query operators except the Contains() operator.

How should I go about doing this.

Answer 1

Either:

• take the column "as is" from the server and do the concat in C#
• write an SP that does it, and call the SP via LINQ

The SP (accepting @UserID) can do this like so:

DECLARE @txt varchar(max)
SET @txt = ''
SELECT @txt = @txt + [DocumentName] + ', '
FROM [Documents]
WHERE [UserID] = @UserID

SELECT [ID], [Name], @txt AS [Docs]
FROM [Users]
WHERE [ID] = @UserID