Entity Code First User, Role, UserRole tables

Question

I am using Entity Code First to create a database and tables to store Users, Roles and the UserRoles.

My classes are as follows -

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


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


public class UserRole
{
    public int UserRoleId { get; set; }
    public int UserId { get; set; }
    public int RoleId { get; set; }

    public virtual User User { get; set; }
    public virtual Role Role { get; set; }
}

class ConfigurationContext : DbContext
{
    public ConfigurationContext()
    {
        Database.SetInitializer<ConfigurationContext>(new DropCreateDatabaseIfModelChanges<ConfigurationContext>());
    }

    public DbSet<Role> Roles { get; set; }
    public DbSet<User> Users { get; set; }
    public DbSet<UserRole> UserRoles { get; set; }
}

My intention is to connect Users to Roles via the UserRole table.

I could do this by creating the tables and using the CF Reverse Engineer Tool, but that produces lots of excess code and would like to know how to do this cleanly.

Answer 1

You don't need the UserRole entity. EF can manage many-to-many relationships automatically by creating the appropriate link table in the database without that entity.

The simplest solution is just to add two collections to your User and Role entities. EF will detect a many-to-many relationship by convention without further explicit configuration:

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

    public virtual ICollection<Role> Roles { get; set; }
}

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

    public virtual ICollection<User> Users { get; set; }
}

class ConfigurationContext : DbContext
{
    public ConfigurationContext()
    {
        Database.SetInitializer<ConfigurationContext>(
            new DropCreateDatabaseIfModelChanges<ConfigurationContext>());
    }

    public DbSet<Role> Roles { get; set; }
    public DbSet<User> Users { get; set; }
}

If you don't need or want one of the collections, say you don't want Role.Users, then you can still create a many-to-many relationship with only one collection by defining it with Fluent API:

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

    public virtual ICollection<Role> Roles { get; set; }
}

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

class ConfigurationContext : DbContext
{
    public ConfigurationContext()
    {
        Database.SetInitializer<ConfigurationContext>(
            new DropCreateDatabaseIfModelChanges<ConfigurationContext>());
    }

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

    public DbSet<Role> Roles { get; set; }
    public DbSet<User> Users { get; set; }
}