Linq query join one table row to another table multiple row

Question

Table name: Namelist

---

id | Name
------------
1  |xxxxx
2  |yyyyy

Table name: Category

---

id | Nameid |Categoryid
-----------------
1  |1       |5
2  |1       |4
3  |2       |3
4  |2       |8

I need a linq query result like this

---

id | Name   |Categoryid
-----------------
1  |xxxx       |5,4
2  |yyyy       |3,8

I tried below linq but it displays first category id only

var list = from n in namelist 
           join c in category on n.id equals c.nameid 
           select new 
           { 
              n.id,
              n.name,
              c.categoryid
           }

Answer 1

You can do this with Group Join and join all the category id's in the group with String.Join like this:-

 var result = (from n in namelist
              join c in categories 
              on n.Id equals c.NameId into g
              select new 
                       {
                          id = n.Id,
                          Name = n.Name,
                          CategorieIds = g.Select(x => x.CategoryId)
                       }).AsEnumerable()
                         .Select(x => new 
                                   { 
                                     Id = x.id, 
                                     Name = x.Name, 
                                     CategoryIds = String.Join(",",x.CategorieIds)) 
                                   });

Answer 2

You can try String.Join:

var list = namelist
    .Select(n => new { 
        n.id,
        n.name,
        categoryids = String.Join(",", category
            .Where(c => c.nameid == n.id)
            .Select(c => c.categoryid))
    });