CODEWITHSUNDEEP
asp.net-mvc-3entity-framework-4.1ef-code-first
snoluet
snoluet

Reputation: 181

EF 4.1 Code First multiple Many-to-Many relationships

I'm having trouble wrapping my head around a certain code-first relationship. I have three entities: Group, User, GroupPermission. The GroupPermission entity holds information about permissions that relate to a group. There are three permissions: leader, editor, user. The GroupPermission table should include the primary key Id and the name of the permission. Then I want a relationship table that looks something like this: Id - Group_Id - User_Username - GroupPermission_Id. There can be multiple groups, multiple users, multiple permissions. I have plenty of examples that help me make a single relationship table, but I can't find anything that includes multiple relationships.

Here are my entities...

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 the tables are created using this structure, I get a GroupPermissions table that has Id, Name, GroupId, and UserId. This table needs to only be Id and Name. Then it creates a GroupPermissionUsers table that holds GroupPermissions_Id and User_Username. This is the table that should be Id, Group_Id, User_Username, GroupPermission_Id.

Does anybody have any tips to accomplish this or am I thinking about the design of this incorrectly?

Upvotes: 4

Views: 708

Answers (1)

Ladislav Mrnka
Ladislav Mrnka

Reputation: 364249

In such case you are missing additional entity. It should look like:

New Permission entity 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 entity to form junction table among 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; }
}

Upvotes: 3

Related Questions