基于多对多关系的实体框架Linq查询

本文关键字：实体 框架 Linq 查询 关系 于多 | 更新日期: 2023-09-27 18:01:27

我有以下模型:

public class ApplicationUser : IdentityUser<string, ApplicationUserLogin, ApplicationUserRole, ApplicationUserClaim>
{
    public bool HasPermission(string permission)
    {
        return Roles.Any(r => r.Role.Permissions
                  .Any(p => p.Name == permission));
    }
    // Some other Stuff
}
public class ApplicationRole : IdentityRole<string, ApplicationUserRole>
{
    public ApplicationRole(string name) : this()
    {
        this.Name = name;
    }
    public virtual ICollection<Permission> Permissions { get; set; }
 }
public class ApplicationUserRole : IdentityUserRole
{
    public ApplicationUserRole(): base() { }
    public virtual ApplicationRole Role { get; set; }
}
public class Permission
{
    public byte Id { get; set; }
    public string Name { get; set; }
    public virtual ICollection<ApplicationRole> Roles { get; set; }
}

基本上他们映射到五个表:用户，角色，用户角色，权限和角色权限-目前我没有一个模型的角色权限-我需要一个吗?

我想使用linq和lambda表达式获得给定用户的权限列表，类似于下面的SQL:

Select p.Id, p.Name 
From Permission p
inner join RolePermission rp on rp.PermissionId = p.Id
inner join UserRole ur on ur.RoleId = rp.RoleId 
where ur.UserId = 'xyz'

我不确定我从哪个实体开始使用LINQ获得权限列表-我只能使用以下方法获得角色?

var permissions =  _context.Users.Where(u => u.Id == userId)
                               .SelectMany(r => r.Roles.SelectMany(p => p.Role.Permissions))
                               .ToList();

任何想法?

基于多对多关系的实体框架Linq查询

要在Linq中执行连接，您可以使用以下命令:

var permissions = new List<Permission>(){ 
                  new Permission (){
                                      Id= 1, 
                                      Name = "Test1"
                                   }, 
                  new Permission{     Id = 2, 
                                      Name = "Test2"}
};
var applicationRoles = new List<ApplicationRole>(){
                       new ApplicationRole{ 
                                            Id =1, 
                                            PermissionId = 2
    }};
var x = from p in permissions
        join ar in applicationRoles on p.Id equals ar.PermissionId
        select p;

public class Permission{
public byte Id {get;set;}
public string Name {get;set;}
public List<ApplicationRole> Roles {get;set;}
}

public class ApplicationRole {
public byte Id {get;set;}   
public byte PermissionId {get;set;}
}

我不确定这是不是你真正要问的，所以我希望它能帮助你。

结果如下:

var permissions =  _context.Users.Where(u => u.Id == userId)
                           .SelectMany(r => r.Roles.SelectMany(p => p.Role.Permissions)).Distinct().ToList();
相关文章：