Entity framework generated for me such entities in my database:

public partial class Permission { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public Permission() { this.UserPermissions = new HashSet<UserPermission>(); this.ActionTypes = new HashSet<ActionType>(); } public int Permission_id { get; set; } public string Name { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<UserPermission> UserPermissions { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<ActionType> ActionTypes { get; set; } } public partial class UserPermission { public int UserPermission_id { get; set; } public int User_id { get; set; } public int Permission_id { get; set; } public int Give_User_id { get; set; } public Nullable<int> Remove_User_id { get; set; } public Nullable<System.DateTime> DateDel { get; set; } public System.DateTime DateAdded { get; set; } public virtual Permission Permission { get; set; } public virtual User User { get; set; } } public partial class ActionType { [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2214:DoNotCallOverridableMethodsInConstructors")] public ActionType() { this.Queues = new HashSet<Queue>(); this.UserChangeHistories = new HashSet<UserChangeHistory>(); this.Permissions = new HashSet<Permission>(); } public int Action_id { get; set; } public string Name { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<Queue> Queues { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<UserChangeHistory> UserChangeHistories { get; set; } [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Usage", "CA2227:CollectionPropertiesShouldBeReadOnly")] public virtual ICollection<Permission> Permissions { get; set; } }

In the database, it looks like this: enter image description here

Accordingly, I want to know if a particular User_id has permissions on a specific Action_id or not.

I tried this:

 var result = from t1 in db.UserPermissions join t2 in db.Permissions on t1.Permission equals t2 join t3 in db.ActionTypes on t2.ActionTypes equals t3 select new { f1 = t2.Permission_id };

But he didn’t please the last join ...

  • Your ActionTypes is an ActionTypes ICollection<ActionType> , that is, a collection of ActionType objects associated with one particular Permission . And from this point of view, join t3 in db.ActionTypes on t2.ActionTypes equals t3 means "merge with the ActionTypes table where all the entries in this table are equal to the ActionTypes set of the ActionTypes object", which is pretty crazy :) - Anton Papin
  • LINQ-request from the @Monk answer in my opinion is really the best and most visual option. - Anton Papin

1 answer 1

And what is the expected feature? Though how to get:

 permissions.Any(p => p.User == user && p.Permission.ActionTypes.Any(a => a.Action_id == actionId))

Judging by my experience, working with rights is usually the hardest in databases, so it is regularly optimized - intermediate tables, indices, caching, etc. EF here will not be a panacea, what request do not write, on large volumes will sink.

  • And what will it look like through the syntax of queries? I supplemented my question with my example, where the last join is cursing ... - iluxa1810
  • @ iluxa1810 no idea, I write that way. - Monk