c# - LINQ 以单个字段为中心
问题描述
我有三个表:用户、用户角色和角色。UserRoles 包含用户与一个或多个角色的关联。
我正在尝试使用 Entity Framework 6 编写一个 LINQ 查询,该查询将产生每行一个用户的输出,列出与该用户关联的所有角色,如下所示:
USER ROLES
John Administrator, Supervisor
Chiara Supervisor
Mark Supervisor, Contributor
目前,我的查询如下,但显然我会根据与用户关联的角色数量获得重复记录:
var users = (from u in db.Users.AsNoTracking()
join ur in db.UserRoles.AsNoTracking() on u.UserId equals ur.UserId into urj
from urc in urj.DefaultIfEmpty()
group u by new
{
u.UserId,
u.UserName,
urc.RoleName,
urc.RoleId
} into grp
orderby grp.Key.UserName
select new
{
grp.Key.UserId,
grp.Key.UserName,
RoleName = grp.Key.RoleName,
RoleId = grp.Key.RoleId
});
有没有办法编写一个或最多两个查询,或者我需要拆分它?我试图找到最佳性能并尽量减少对数据库的查询次数。
解决方案
因此,用户和角色之间存在多对多关系,您需要所有(或部分)用户,每个用户都有其所有角色(或某些角色)。
使用虚拟 ICollections!
显然您正在使用实体框架。如果您遵循了约定,您将拥有类似于以下的类:
class User
{
public int Id {get; set;}
public string name {get; set;}
...
// every User has zero or more roles (many-to-many)
public virtual ICollection<Role> Roles {get; set;}
}
class Role
{
public int Id {get; set;}
public string name {get; set;}
...
// every Role is played by zero or more Users (many-to-many)
public virtual ICollection<User> Users {get; set;}
}
可能是您有不同的属性名称,但最重要的部分是virtual ICollection<...>
. 这通知实体框架有关用户和角色之间的多对多关系。
在实体框架中,表的列由非虚拟属性表示。虚拟属性表示表之间的关系(一对多,多对多,...)
实体框架知道表之间的关系。如果您使用集合,实体框架将自动将其转换为正确的(组)连接。
要求给我所有用户的(一些属性),每个用户都有他们的角色
var usersWithTheirRoles = dbContext.Users
// only if you don't want all Users:
.Where(user => user.City == "New York")
.Select(user => new
{
// Select only the User properties that you plan to use:
Id = user.Id,
Name = user.Name,
...
Roles = user.Roles
// only if you don't want all Roles
.Where(role => ...)
.Select(role => new
{
Id = role.Id,
Name = role.Name,
...
})
.ToList(),
});
自己做组加入
如果您不想使用虚拟 ICollections,或者您的实体框架版本不支持此功能,请考虑自己进行 groupjoin:
var result = dbContext.Users.GroupJoin(dbContext.UserRoles,
user => user.Id, // from every User take the Id
userRole => userRole.UserId, // from every UserRole take the foreign key to User
// parameter resultSelector: for every user, and its zero or more userRoles,
// make one new object, containing the following properties:
(user, userRolesOfThisUser) => new
{
Id = user.Id,
Name = usr.Name,
// for the Roles of this User: groupjoin all Roles with the UserRoles of this User
Roles = dbContext.Roles.GroupJoin(userRolesOfThisUser,
role => role.Id // from every Role take the Id,
userRole => userRole.RoleId, // from every UserRole of this user take the foreign key
// result selector: take every Role, and its zero or more UserRoles
// that are also UserRoles of this User, to make one new Role:
(role, userRolesOfThisRole) => new
{
Id = role.Id,
Name = role.Name,
})
.ToList(),
});
在我看来,这种virtual ICollection
方法看起来更加优雅和自然。
推荐阅读
- excel - 如何使用 sql 语句和 vba 将数据从 MS-Access 导入到 Excel 电源查询?
- javascript - 通过解析 JSON 字符串提取 API GET 请求
- ios - 如何在布局类似于 fork 的 UIViewController 之间导航和传递数据?
- github - 无法 git 推送到 Github
- dictionary - 字典理解:TypeError:'builtin_function_or_method'对象不可迭代
- mysql - 空闲磁盘空间 innoDB Mysql
- python - Python - 重命名文件
- sql - 如何使用 where 子句在数据流中创建 sourceOLEDB 是来自另一个数据库的子查询
- wordpress - sir am new to wordpress and i didn't get menu button in appearance field what is the issue can you please explain me
- python - Run second execute command if first execute command is successful