The internal relationship between many of the many relationship models

Question

I have two models, as shown below, that are configured as much for many relationships:

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 Inner Join with Linq. I can do this easily in SQL, since the connection table is there. But how can I do this with linq? Below I can 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, FirstOrDefault will ruin the result, but other than that, I cannot compile the query without errors.

The following is the query I'm trying to write to 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, the inner join is done with the join table, so the query works as expected

Any help is appreciated.

Answer 1

The simplest 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 create SQL with the necessary internal joins.

Free equivalent

 Products.SelectMany(p => p.Roles, (p, r) => new { p.PermissionName, r.RoleId, ... }) 

You will probably agree that the first form, the “comprehensive syntax,” wins.

Answer 2

 var x = from pr in Permissions from role in Roles where pr.Roles.Exists(r => r.RoleId == role.RoleId) select new { pr.PermissionName, role.RoleId };