Foreign Key table Map

Question

I have 3 Entities that have many combinations on my site.

I would to create the follow hierarchy:

1. Each User have collection of UserRoles.
2. Each UserRole have a fixed collection of PermissionRecords
3. Each PermissionRecord have a fild of PermissionRecordPrivileges that varies from user to user.

I would like to get the user's privileges (getting the permissionRecord and UserRole collection is quite trivial). As I understand, I need to create a table that merges the following data: UserId, PermissionRecordId, PermissionPrivilegesId (3 Foreign keys that create primary key)

How can I do this using EF 5 ( or earlier)?

The code:

public class BaseEntity
{
    public int Id { get; set; }
}
public class User:BaseEntity
{
   public virtual ICollection<UserRole> UserRoles{get;set;}
}

public class UserRole:BaseEntity
{
   public ICollection<PermissionRecord> PermissionRecords { get; set; }
}

public class PermissionRecord : BaseEntity
{
    public PermissionRecordPrivileges Privileges { get; set; }
}

public class PermissionRecordPrivileges : BaseEntity
{
    public bool Create { get; set; }

    public bool Read { get; set; }

    public bool Update { get; set; }

    public bool Delete { get; set; }

}

Answer 1

You have to create your entity model classes like below.

Note : Need to maintain Conventions(naming and singular/plural) when you use Entity framework Code First.Like below :

Entity Models

public class UserRole
 {

     public int Id { get; set; }

     public virtual ICollection<PermissionRecord> PermissionRecords { get; set; }

 }


public class PermissionRecord 
 {
    public int Id { get; set; }

    public virtual PermissionRecordPrivilege PermissionRecordPrivilege { get; set; }
 }


public class PermissionRecordPrivilege
 {

    public int Id { get; set; }

    public bool Create { get; set; }

    public bool Read { get; set; }

    public bool Update { get; set; }

    public bool Delete { get; set; }

 }

Your Tables Should like below :

Tables

PermissionRecordPrivileges

Id            int    NotNull
Create        bit   AllowNull
Read          bit   AllowNull
Update        bit   AllowNull
Delete        bit   AllowNull


PermissionRecords

Id                             int    NotNull

PermissionRecordPrivilege_Id   int    NotNull

UserRole_Id                    int    NotNull


UserRoles

Id            int    NotNull

I hope this will help to you.Good Luck.

Answer 2

Your terminology "create a table" is a bit confusing. A table is a database object. I assume you mean a data structure client-side. To collect a User's privileges you can do:

var privileges = (from u in context.Users
                 from ur in u.UserRoles
                 from pr in ur.PermissionRecords
                 where u.UserId = id
                 select ur.Privileges).Distinct();

where id is a variable containing a User's id.