How do I map to a lookup table using Entity Framework Code First with Fluent API

Question

I'm new to asp.net mvc and entity framework code first apporach, I'm not that keen on databases either. I apologise in advance for wrong terminology or the way I understand things.

Now to the question. I have the following models:
User Model

 public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }

    public int RoleId { get; set; }

    [ForeignKey("RoleId")]
    public virtual IEnumerable<Role> Roles { get; set; }
}

Role Model

 public class Role
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }       
}

What i eventually want is a way to use Ef codefirst approach with the fluent API to map the UserId and RoleId to a User_Role table with a one-to-many relation, a user can have multiple roles:

I assume that what s done in this question is the right approach, except that there the author used a many-to-many connection. I tried it that way but the part with u => u.users gives me an error(I assume thats because there is no users property in the model, so he answered his question but didn't update his question?)

My question: What is the exact fluent api code to let Ef generate this table for me?

Things I'm unsure about: (Feel free to ignore)

• Is this the right approach of my problem?
• Once i have the lookup table, is this still the right way to declare my navigational property so i can later use it like user.Roles and retrieve their roles?
• where will be the RoleId in the User model be populated from, Roles table or User_Role?
• Has having an ID in the lookup table any use?

Thanks in advance! I really appreciate your expertise.

Answer 1

First you should get rid of the RoleId property in the User model. Having that as a foreign key tells which single role a user has. Since a user can have many roles, the foreign key should not be in the user table but in the mapping table.

So what you have is a many-to-many relationship between users and roles, and Entity Framework can create the needed mapping table automatically without you having to configure anything.

If you simply have a Roles property in the User entity and a Users property in the Role entity, EF will figure out that you want a many-to-many between those two, and create a table with the primary keys of both entities as a combined primary key, which will be used to map users to roles.

When loading a User from the database you can then use the Roles navigation property to figure out which roles the user has, and you can load a Role to figure out which users are in that role.

The simplest way of making it work would be something like this:

public class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Role> Roles { get; set; }

    static Context()
    {
        Database.SetInitializer(new DropCreateDatabaseAlways<Context>());
    }

    public Context()
        : base("Server=localhost;Initial Catalog=Test;Integrated Security=True;")
    {
    }
}

public class User
{
    public int UserId { get; set; }
    public string Username { get; set; }
    public string Password { get; set; }

    public List<Role> Roles { get; set; }
}

public class Role
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }

    public List<User> Users { get; set; }
}

Running that code results in 3 tables like this: