LINQ: equivalent for string_agg from PostgreSql

Question

I need to concatenate multiple columns values to single value (separated with coma for example). I can do it by string_agg(u.name, ',') as Users in PostgreSql. I tried do it in linq query syntax but I failed - all time I just get list with split values instead of one row with values concatenated in one field.

Let's assume that I have only three tables:

Doc                            User                 Doc_User
+----+--------------------+    +----+-----------+   +----+-----------+
| Id | Path               |    | Id | Name      |   | DocId | UserId |   
+----+--------------------+    +----+-----------+   +----+-----------+    
|  1 | "C:\\Files\\A.txt" |    |  1 | "Adam"    |   |  1    | 1      |   
|  2 | "C:\\Files\\B.txt" |    |  2 | "Benny"   |   |  1    | 2      |    
|  3 | "C:\\Files\\C.txt" |    |  3 | "Charlie" |   |  2    | 1      | 
+----+--------------------+    +----+-----------+   |  2    | 2      |
                                                    |  2    | 3      |
                                                    +-------+--------+

At the start I was trying simple join:

var model = (from d in db.Docs
                  join du in db.DU on d.Id equals du.DocId
                  join u in db.Users on du.UserId equals u.Id
                  select new DataModel() { DocPath = d.Path, UserName = u.Name }).ToList();

As I suspected I got list with separated rows:

C:\Files\A.txt | Adam 
C:\Files\A.txt | Benny 
C:\Files\B.txt | Adam
C:\Files\B.txt | Benny 
C:\Files\B.txt | Charlie

But what I want to obtain is:

C:\Files\A.txt | Adam, Benny
C:\Files\B.txt | Adam, Benny, Charlie

string.Join() is not recognized by EF and I can't use it in linq queries, Aggregate() not working too. How can I achieve this?

I've prepared example for play with it: .Net Fiddle.

Answer 1

The code below groups the common documents by path using LINQ and then joins the grouped document's UserNames, using string.Join. I also think you don't need ToList() in this line select new DataModel() { DocPath = d.Path, UserName = u.Name }).ToList(); if you are going to use this solution because we are going to use LINQ again.

var grouped = model.GroupBy(x => x.DocPath);
foreach (var iGrouped in grouped){
    string.Join(",",iGrouped.Select(x => x.UserName));
    Console.WriteLine(iGrouped.Key + " | " + string.Join(",",iGrouped.Select(x => x.UserName)));
}