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
EF will produce SQL with the required inner joins.
The fluent equivalent is
You'll probably agree that the first form, "comprehensive syntax", wins.