不能在多对多上使用 Linq WHERE/IN/JOIN 和实体框架

Question

我可以在SQL中非常简单地做到这一点，但尝试学习如何使用实体框架在Linq中做到这一点。

我有一个用户，一个用户可以有多个组，一个组可以有多个权限。

我想获取用户的所有权限。

我的数据库上下文中有以下数据库集

public virtual DbSet<User> Users { get; set; }
public virtual DbSet<Group> Groups { get; set; }
public virtual DbSet<Permission> Permissions { get; set; }

有这样的类

public class User : Entity, IUser<long>
{
    public string UserName { get; set; }
    public string PasswordHash { get; set; }
    public User()
        : base()
    {
        this.Groups = new HashSet<UserGroup>();
    }
    public virtual ICollection<UserGroup> Groups { get; set; }
}
public class Group : AuditableEntity
{
    public Group() {
        this.Permissions = new HashSet<GroupPermission>();            
    }
    public string Name { get; set; }
    public virtual ICollection<GroupPermission> Permissions { get; set; }
}
public class Permission 
{
    public string Name { get; set; }
    public Permission()
    {
    }
}

为了应对多对多的关系，我也有这些课程

public class UserGroup
{
    public long UserId { get; set; }
    public long GroupId { get; set; }
    public virtual User User { get; set; }
    public virtual Group Group { get; set; }
}
public class GroupPermission
{
    public long PermissionId { get; set; }
    public long GroupId { get; set; }
    public virtual Permission Permission { get; set; }
    public virtual Group Group { get; set; }
}

现在在SQL中，我只会做

SELECT Permission.* 
FROM Permission
    INNER JOIN PermissionGroup ON Permission.Id = PermissionGroup.PermissionId
    INNER JOIN Group ON PermissionGroup.GroupId = Group.Id
    INNER JOIN UserGroup ON Group.Id = UserGroup.GroupId
WHERE UserGroup.UserId = @UserId

有人可以告诉我为什么我不能在 LINQ 中执行此操作吗？ 好吧，我知道我可以，但我不知道怎么做。

编辑

上下文。权限组和上下文。在我的上下文中，用户组不是数据库集。

List<Permission> result;
using(DataContext context = new DataContext())
{
   result = (from permission in context.Permissions
             join permissionGroup in context.PermissionGroup on permission.Id equals permissionGroup.Id
             join g in context.Groups on permissionGroup.GroupId equals g.Id
             join userGroup in context.UserGroup on g.Id equals userGroup.GroupId
             where userGroup.UserId == user.Id 
             select permission).ToList();
}

Answer 1

使用这个

var linqQuery=(from p in Permission
                    join pg in PermissionGroup on p.Id equals pg.Id
                    join g in Group on pg.GroupId equals g.Id
                    join ug in UserGroup on g.Id equals ug.GroupId
                    where ug.UserId == USERID 
                    select p);

Answer 2

加入 SQL 并不自动意味着您应该加入 LINQ。尽可能使用导航属性。如果您这样做，您的情况会变得简单得多，并为此使用SelectMany：

from u in db.Users
select new { 
             u.UserName,
             Permissions = u.Groups
                            .Select(ug => ug.Group)
                            .SelectMany(g => g.Permissions)
                            .Select(gp => gp.Permission
           }

Answer 3

如果您有自己的DbContext类，其中所有数据库表都表示为DbSets

public partial class OwnContext: DbContext
{
   public DbSet<User> Users { get; set; }
   public DbSet<Group> Groups { get; set; }
   public DbSet<Permission> Permissions { get; set; }
   public DbSet<UserGroup> UserGroups { get; set; }
   public DbSet<PermissionGroup> PermissionGroups { get; set; }
}

您必须使用 using 语句创建该类的实例，并在 using 语句中执行查询。

List<Permission> result;
using(OwnContext context = new OwnContext())
{
   result = (from permission in context.Permissions
             join permissionGroup in context.PermissionGroups on permission.Id equals permissionGroup.Id
             join group in context.Groups on permissionGroup.GroupId equals group.Id
             join userGroup in context.UserGroups on group.Id equals userGroup.GroupId
             where userGroup.UserId == user.Id 
             select permission).ToList();
}

Answer 4

using (var context = new NombreDeEntityDataModel)
{
    var query = from a in context.Permission
                join b in context.PermissionGroup on a.id equals b.PermissionId
                join c in context.Group on b.GroupId equals c.id
                join d in context.UserGroup on c.id equals d.GroupId
                where d.UserId = @UserId
                select a;
}