Concatenating multiple column values inside a variable by comma separated

Question

I have a query in linq-to-sql as follows:

var query = (from users in _context.Users 
            join consumers in _context.Consumers 
            on users.usersId equals consumers.consumerId 

            from clients in _context.Clients.Where(x => x.Id == users.Id).DefaultIfEmpty().Take(1)
            where consumerId.Contains(consumers.consumerId)
            select new UserConsumerDto 
            {
             FirstName = users.FirstName, 
             LastName = users.LastName, 
             ClientName = clients.Name
            }).ToList()

The above query returns me several rows; meaning that a user can have multiple clients. Therefore, I added Take(1) to get only one client for the time being.

Now, when I remove the Take(1), several records are returned. I want to avoid this, by adding the client name in a single record for a user by separated comma.

For eg:

User 1 | ClientA, ClientB

Instead of:

User 1 | Client A
User 1 | Client B

Can someone please help me to achieve this?

Amir Molaei · Accepted Answer · 2019-03-13T15:22:12.877

1

You can use group by to group the records by for example UserName and then aggregate the client names using string.Join(",",ClientName) to concatenate the client names.
Here is a sample code:

    var userClients = from c in (dbContext joined tables)
      group c by c.UserName into u 
      select new {
          UserName = u.First().UserName,
          ClientName = string.Join(",", (from n in u select n.ClientName).ToArray()) 
      };

Check the sample code in this demo

edited Mar 13 '19 at 15:22

answered Mar 13 '19 at 05:46

Amir Molaei

3,700
1
17
20

How to achieve this in `linq-to-sql` ? – crazydev Mar 13 '19 at 06:15
@crazydev Answer was updated. Sample code and demo added. – Amir Molaei Mar 13 '19 at 06:45

score 0 · Answer 2 · answered Mar 13 '19 at 05:55

You can try this code

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

for reference check

Concatenating multiple column values inside a variable by comma separated

2 Answers2