linq to sql中的子查询

Question

我想在我的sql命令中返回子查询结果的逗号分隔。如下所示：

Select  [User].UserName,
    (Select [Role].Name + ', '
            From
            Security.UserRole       As  UserRole
                Inner Join
            Security.[Role]         As  [Role]
                On  UserRole.RoleId =   [Role].RoleId

            Where   UserRole.UserId =   [User].UserId
            For Xml Path('')
    )
    From    Security.[User]         As  [User]

基本上每个用户都有多个角色，我想为每个用户加载逗号分隔格式的角色名称。

Sql中的结果集如下：

​

有没有办法用LINQ写这个查询？

谢谢

Answer 1

尝试代码

创建获取用户角色特定用户的方法

Public String GetuserRoles(int userid)
{
  string strroles="";
        (from UserRole in Security.UserRole 
             join Role in Security.Role on UserRole.RoleId  equals Role.RoleId
            where UserRole.UserId==userid
             select new
             {
                 strs = Role.Name + ","
             }).ToList().ForEach(c => strroles = strroles + c.strs);

     return strroles;
}

编写完Linq查询后：

 var result=( from p in  Security.User  select new {
              UserName=p.UserName,
              Roles=GetuserRoles(p.UserId)
             }).ToList();