Linq合并左联接数据 [英] Linq Combine Left Join Data

问题描述

说我有以下数据库:

Users
-------
UserId (PK)
UserName

Roles
-----
RoleId (PK)
RoleName

UserRoles
---------
UserId (PK)
RoleId (PK)

用户1-M UserRoles M-1角色

Users 1-M UserRoles M-1 Roles

使用LinqToSQL，我想返回以下设置:

Using LinqToSQL, I want to return the following set:

[User1], [Role1, Role2, Role3]
[User2], [Role2, Role3]
[User3], []

等等...

创建此LinqToSql查询的最有效方法是什么?

What is the most efficient way to create this LinqToSql Query?

此外，如果我想创建一个过滤器以仅返回具有Role1的用户，那将带来什么?

In addition, if I want to create a filter to return only users that have Role1, what would that entail?

谢谢.

推荐答案

定义有效".但是否则...

Define "efficient". But otherwise...

from u in dataContext.Users
select new { User = u, Roles = u.UserRoles.Select(ur => ur.Role) }

并按RoleID过滤用户:

from u in dataContext.Users
where u.UserRoles.Any(ur => ur.RoleID == 1)
select u

或通过其他Role属性，例如Name:

Or by some other Role attribute, say, Name:

from u in dataContext.Users
where u.UserRoles.Any(ur => ur.Role.Name == "Role 1")
select u

将所有内容组合在一起:

Combining it all together:

from u in dataContext.Users
select new
{
     User = u,
     Roles = from ur in u.UserRoles
             where ur.RoleID == 1 || ur.Role.Name == "Role 1"
             select ur.Role
}

这篇关于Linq合并左联接数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！