CODEWITHSUNDEEP
c#.netlinq-to-sql
Matthew Dresser
Matthew Dresser

Reputation: 11442

Linq to SQL to append multiple records with a delimiter

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.

Upvotes: 4

Views: 1461

Answers (2)

Tomas
Tomas

Reputation: 1

if youre using raw sql then you can use STUFF and FOR XML PATH syntax, as described in: How Stuff and 'For Xml Path' work in Sql Server other solution, you need first get data from sql by linq, then make result .AsEnumerable() - now you can use string.Join function

Upvotes: 0

Marc Gravell
Marc Gravell

Reputation: 1063704

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

Upvotes: 5

Related Questions