EF 4.1 Code First Many-to-Many Multiple Relationships

Question

I'm having trouble moving my head around certain code relationships. I have three objects: Group, User, GroupPermission. The GroupPermission object stores information about the permissions that belong to the group. There are three permissions: supervisor, editor, user. The GroupPermission table must include the primary key identifier and permission name. Then I want the relationship table to look something like this: Id - Group_Id - User_Username - GroupPermission_Id. There may be several groups, several users, several permissions. I have many examples that help me create a single relationship table, but I cannot find anything that includes several relationships.

Here are my essences ...

User:

public class User { [Key, StringLength(EntityLength.UsernameLength)] public string Username { get; set; } [Required, StringLength(EntityLength.NameLength)] public string FirstName { get; set; } [Required, StringLength(EntityLength.NameLength)] public string LastName { get; set; } [Required, StringLength(EntityLength.Email)] public string Email { get; set; } public bool Active { get; set; } public DateTime DateCreated { get; set; } public virtual UserPermission UserPermission { get; set; } public virtual ICollection<Group> Groups { get; set; } public virtual ICollection<Project> Projects { get; set; } public virtual ICollection<Issue> Issues { get; set; } public virtual ICollection<GroupPermission> GroupPermissions { get; set; } public string FullName { get { return FirstName + ' ' + LastName; } } } 

Group

 public class Group { [Key] public int Id { get; set; } [Required, StringLength(EntityLength.GenericLength)] public string Name { get; set; } [Required, StringLength(EntityLength.DescriptionLength)] public string Description { get; set; } public virtual ICollection<User> Users { get; set; } public virtual ICollection<Project> Projects { get; set; } public virtual ICollection<GroupPermission> GroupPermissions { get; set; } } 

GroupPermission:

 public class GroupPermission { [Key] public int Id { get; set; } [StringLength(EntityLength.GenericLength)] public string Name { get; set; } public int GroupId { get; set; } public virtual ICollection<Group> Groups { get; set; } public int UserId { get; set; } public virtual ICollection<User> Users { get; set; } public enum Permission { Leader = 1, Editor = 2, User = 3 } } 

When tables are created using this structure, I get a GroupPermissions table that has identifiers, names, groups, and UserId. This table should only be Id and Name. He then creates the GroupPermissionUsers table, which contains GroupPermissions_Id and User_Username. This is the table that should be Id, Group_Id, User_Username, GroupPermission_Id.

Does anyone have any clues for this or am I thinking about it wrong?

Answer 1

In this case, you are missing an additional object. It should look like this:

New Permission object with Id and Name :

 public class Permission { [Key] public int Id { get; set; } [StringLength(EntityLength.GenericLength)] public string Name { get; set; } public virtual ICollection<GroupPermission> GroupPermissions { get; set; } } 

Modified GroupPermission object for creating a connection table between Users , Groups and Permissions :

 public class GroupPermission { [Key] public int Id { get; set; } public int GroupId { get; set; } public virtual Group Group { get; set; } public string UserName { get; set; } [ForeignKey("UserName")] public virtual User User { get; set; } public int PermissionId { get; set; } public virtual Permission Permission { get; set; } }