Define model for deep relationships/ nested models

Question

I have a db that looks like this:

tblUsers
   - UserId
tblRoles
   - RoleId

tblUserRoles
   - UserRoleId
   - RoleId
   - UserId

class User
{
   [Key]
   public virtual int UserId{ get; set; }

   [ForeignKey("UserId")] // how does this tell ef not to map it to the primary key. 
                             It needs to map to UserId which is not defined as a key.??

   public DbSet<UserRole> Roles{ get; set; }
}    

class UserRoles
{
   [Key]
   public virtual int UserRoleId{ get; set; }

   public virtual int UserId{ get; set; }
   public virtual int RoleId{ get; set; }

   [ForeignKey("RoleId")]
   Public Role RoleInfo {get; set;}
}

class Role 
{
   [Key] 
   public virtual int RoleId {get; set;}

   public string RoleName {get; set;}
}

I don't think this is correct. I need to setup the model where the foreign key is not the primary key but a column in the table. My question: How do I define my model and link the relationship between entities without having a primary key?

score 3 · Answer 1 · edited May 23 '17 at 12:11

3

You don't need to declared the class UserRoles , EF code first will create the relationship and tables needed behind the scene, this is all you need:

public class User
{
    public int Id {get;set;}
    public virtual ICollection<Role> Roles {get;set;}
}

public class Role
{
    public int Id {get;set;}
    public virtual ICollection<User> Users {get;set;}
}

then override your OnModelCreating:

protected override void OnModelCreating(DbModelBuilder builder)
{
    builder.Entity<User>()
    .HasMany(u => u.Roles).WithMany(r => r.Users)
    .Map(t => t.MapLeftKey("UserId")
    .MapRightKey("RoleId")
    .ToTable("UserRoles"));
}

The only time you need to declare the join table yourself, is if you need a many-to-many with payload. Check this post many-to-many with payload

edited May 23 '17 at 12:11

Community

1
1

answered Jun 03 '13 at 21:51

SOfanatic

5,523
5
36
57

How do I this with just One to Many. If you take out Users from the Role Model? – Arcadian Jun 03 '13 at 22:14
@magic-c0d3r , so a `User` has one `Role`? and a `Role`, belongs to many `Users`? check answer edit. – SOfanatic Jun 03 '13 at 22:21
No a User has many Roles and a Role can belong to Many Users. But I dont want to show that in my model. Just need Users with many roles to work for now. Unless I have to include Users in the UserRole class – Arcadian Jun 03 '13 at 22:30
You are always going to need to include a `Property Reference` from one class to another, even if you end up using a `UserRole` class, your `User` class will still need to have a `Collection` of `UserRole` and your `Role` class will need to have a collection of `UserRole`. – SOfanatic Jun 03 '13 at 22:48

jstromwick · Accepted Answer · 2013-06-04T16:04:25.507

2

Do you mean how might you link these tables together? I generally I have my Model setup to mirror (for the most part) the table structure of the database. Below is how I would setup the Model. User has a collection of UserRoles each of which has one Role with that record.

internal class User
{
    public User()
    {
        UserRoles = new List<UserRole>();
    }

    [Key]
    public int UserId { get; set; }

    public ICollection<UserRole> UserRoles { get; set; }
}

internal class UserRole
{
    [Key]
    public int UserRoleId { get; set; }

    public int UserId { get; set; }
    public int RoleId { get; set; }

    [ForeignKey("UserId")]
    public User User { get; set; }

    [ForeignKey("RoleId")]
    public Role Role { get; set; }
}

internal class Role
{
    public Role()
    {
        UserRoles = new List<UserRole>();
    }

    [Key]
    public int RoleId { get; set; }

    public string RoleName { get; set; }

    public ICollection<UserRole> UserRoles { get; set; }
}

So basically there is a One to Many between User and UserRole, and a One to Many between Role and UserRole

edited Jun 04 '13 at 16:04

answered Jun 03 '13 at 21:50

jstromwick

1,206
13
22

It compiled but no roles were loaded in to the list. How is Roles mapped to UserRole in your example? – Arcadian Jun 03 '13 at 22:09
@magic-c0d3r Righto, I updated my code sample to have a One to many between User and UserRole and a One to many between UserRole and Role. Is this what you had in mind? – jstromwick Jun 03 '13 at 22:18
Why is RoleInfo a collection in UserRole? any help is great thanks. – Arcadian Jun 03 '13 at 22:25
@magic-c0d3r oops had that reversed. I updated my answer (code and comments). – jstromwick Jun 04 '13 at 00:44
How does it know to match on UserId and not UserRoleId? – Arcadian Jun 04 '13 at 00:47
@magic-c0d3r I believe it is one of the default conventions for EF pocos that their navigation properties will be setup this way. As you can see `User` has a collection of `UserRoles` and `UserRole` has a reference back to its parent `User`. The "Magic" of EF conventions is that the `UserRole` table will have an FK from its `UserId` property to the primary key of `User` – jstromwick Jun 04 '13 at 05:01

Define model for deep relationships/ nested models

2 Answers2