Linq query joins one row of a table to another table multiple rows

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 only displays the id of the first category

 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 using Group Join and join all category identifiers in a group using String.Join as follows: -

  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)) }); 

Answer 3

Use String.Join() . I changed your expression:

 var list = from n in namelist join c in category on n.id equals c.nameid group g by new { n.id, n.name } select new { id = g.Key.id, Name = g.Key.name, Categoryid = String.Join(",", g.Select(x => xccategoryid)) } 

Answer 4

First, you join the NameList and Category table, and then bring them into objects using ToList or AsEnumerable methods, such as

 var list = (from n in db.NameList join c in db.Category on n.id equals c.nameid select new { id = n.id, Name = n.name, Categoryid = c.id }).ToList(); var list2 = from l in list group by new {l.id, l.Name} into groupings from g in groupings select new{ g.Key.id, g.Key.Name, CategoryId = string.Joing(",", groupings.Where(x=>x.NameId == g.Key.id).Select(y=>y.CategoryId)) }; 

The advantage of using ToList to extract data from db is that in the database you will receive only one query and all necessary records will be extracted from memory. The second statement will group these entries using id and Name and apply string.Join to CategoryId . Note that if you use the string.Join method to query Linq-to-Entities , it will fail because this method cannot be converted to an sql expression.