LINQ to SQL查询
问题描述:
我有一个LINQ to SQL查询来检索所有用户与他们的角色一起:LINQ to SQL查询
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
};
在系统中的用户可以拥有多个角色。这个查询(以表格形式)的结果是这样的:
1管理员
1员工
2员工
3员工
我怎样才能重新写这个查询返回的所有用户角色等逗号分隔值:
1联系,员工
2雇员
3雇员
答
嘿库马尔,我创建了一个小型控制台应用程序来模仿我相信你有的数据。我认为它展现了你正在寻找的行为。这不是世界上最伟大的代码,但我认为算法是关键。我只是将ToString()的一个快速覆盖放在一起以正确显示数据。
我做的最主要的变化是创建一个定义的类要显示的数据,并打破LINQ查询分成两个独立的部分:
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;
}
}
}
答
这是做它的一种方式,没有测试它:
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()))
};