LINQ to SQL Query

Question

I have a LINQ TO SQL query that retrieves all users along with their roles:

var userRoles = from u in db.GetTable<User>() join ur in db.GetTable<UserRole>() on u.UserID equals ur.UserID join r in db.GetTable<Role>() on ur.RoleID equals r.RoleID orderby u.UserID select new { u.UserID, r.RoleName }; 

A user in a system can have several roles. The result of this query (in table format) is as follows:

1 admin
1 employee
2 employee
3 employee

How to overwrite this query to return all user roles as comma-separated values, for example:

1 Admin, Employee
2 employee
3 employee

Answer 1

Hey Kumar, I created a small console application to simulate the data that I believe you have. I think it demonstrates the behavior you are looking for. This is not the best code in the world, but I believe this algorithm is the point. I simply compiled a quick override of ToString () to display the data correctly.

The main change I made was to create a specific class for the displayed data and split the linq query into two separate parts:

 using System; using System.Collections.Generic; using System.Linq; namespace Test { class Program { static void Main() { var users = new List<User> { new User { UserID = "1" }, new User { UserID = "2" }, new User { UserID = "3" } }; var roles = new List<Role> { new Role { RoleID = "1", RoleName = "Admin" }, new Role { RoleID = "2", RoleName = "Employee" } }; var userRoles = new List<UserRole> { new UserRole { UserID = "1", RoleID = "1" }, new UserRole { UserID = "1", RoleID = "2" }, new UserRole { UserID = "2", RoleID = "2" }, new UserRole { UserID = "3", RoleID = "2" } }; var userRoles2 = from u in users orderby u.UserID select new UserList { UserID = u.UserID, Roles = (from r in roles join ur in userRoles on u.UserID equals ur.UserID where ur.RoleID == r.RoleID select r).ToList() }; foreach (var item in userRoles2) { Console.WriteLine(item); } Console.ReadKey(); } } public class User { public string UserID; } public class UserRole { public string UserID; public string RoleID; } public class Role { public string RoleID; public string RoleName; } public class UserList { public string UserID; public List<Role> Roles; public override string ToString() { string output = UserID + " "; foreach (var role in Roles) { output += role.RoleName + ", "; } output = output.Substring(0, output.Length - 2); return output; } } } 

Answer 2

This is one way to do this, not tested it:

  from u in db.GetTable<User>() join ur in db.GetTable<UserRole>() on u.UserID equals ur.UserID join r in db.GetTable<Role>() on ur.RoleID equals r.RoleID orderby u.UserID group u by u.UserID into g select new { UserId = g.Key, Roles = String.Join (" ,", g.UserRoles.SelectMany(c => c.Roles).Select(p=> p.RoleName).ToArray())) };