许多关系模型之间的内在联系 [英] Inner Join Between Many to Many Relationship Models

问题描述

我有两个类似下面的模型被配置为多个关系：

  public class Permission 
 { 
 public int PermissionId {get;组; } 
 public string PermissionName {get;组; } 
 public virtual List< Role>角色{get;组; } 
} 
  

  public class Role 
 {
 public int RoleId {get;组; } 
 public string Name {get;组; } 
 public string ID {get;组; } 
 public virtual List< Permission>权限{get;组; 
} 
  

我想做一个 Inner Join 与Linq。我可以在SQL中轻松实现，因为连接表在那里存在。但是我怎么能用linq呢？以下是我到目前为止可以做的：

 从pr在权限
中加入角色在Pr.Roles上的角色。选择（s => s.RoleId）.FirstOrDefault（）equals role.RoleId 
 select new {pr.PermissionName，role.RoleId} 
  

如上所述， FirstOrDefault 将破坏结果，但除此之外，我无法编译查询而没有错误。

以下是我正在Linq写的查询：

  SELECT P.PermissionName，R.RoleId 
 FROM权限AS P 
 INNER JOIN PermissionRoles AS PR ON P.PermissionId = PR.Permission_PermissionId 
 INNER JOIN角色AS R ON PR.Role_RoleId = R. RoleId 
  

如您所见，内连接是使用连接表进行的，因此查询按预期工作

任何帮助都不胜感激。

解决方案

最简单的语法是

  from p在context.Permissions 
从r在p.Roles //通知p！ 
选择新的{p.PermissionName，r.RoleId，Role = r.Name，etc ...} 
  

EF将产生具有所需内联的SQL。

流畅的等价物是

  Products.SelectMany（p => p.Roles，
（p，r）=> new 
 {
 p.PermissionName，
 $ r 
 
 $ b $ /同意第一种形式综合语法，胜出。
 
I have two model like below that are configured as many to many relationship:
public class Permission
{
    public int PermissionId { get; set; }
    public string PermissionName { get; set; }
    public virtual List<Role> Roles { get; set; }
}

public class Role
{
    public int RoleId { get; set; }
    public string Name { get; set; }
    public string ID { get; set; }
    public virtual List<Permission> Permissions { get; set; }
}
I want to do an Inner Join with Linq. I can do that easily in SQL since the join table is present there. But how can I do this with linq? Below is what I could do so far:
  from pr in Permissions
  join role in Roles on pr.Roles.Select(s => s.RoleId).FirstOrDefault() equals role.RoleId
select new { pr.PermissionName, role.RoleId } 
As you can see above the FirstOrDefault will ruin the result but other than that I cannot get to compile the query without errors.

Below is the query I am trying to write in Linq:
 SELECT P.PermissionName, R.RoleId
   FROM Permissions AS P
        INNER JOIN PermissionRoles AS PR ON P.PermissionId = PR.Permission_PermissionId
        INNER JOIN Roles AS R ON PR.Role_RoleId = R.RoleId
As you can see, an inner join is made with the join table thus query works as expected

Any help is appreciated.
解决方案
The easiest syntax is
from p in context.Permissions
from r in p.Roles // notice the p !
select new { p.PermissionName, r.RoleId, Role = r.Name, etc... }
EF will produce SQL with the required inner joins.

The fluent equivalent is 
Products.SelectMany(p => p.Roles, 
                    (p, r) => new  
                              {
                                p.PermissionName, 
                                r.RoleId,
                                ...
                              })
You'll probably agree that the first form, "comprehensive syntax", wins.

                        
这篇关于许多关系模型之间的内在联系的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！