Table Mapping in Entity Framework

Question

I am trying to connect asp.net membership tables on asp.mvc 3 website. I am following the tutorial in the sports store book by Steve Sanderson About ASP.NET MVC 3 Framework and applying it to tables generated from exe membership.

So, I have a custom class that looks like this:

namespace Domain.Entities { public class User { public Guid UserId { get; set; } public string UserName { get; set; } public DateTime LastActivityDate; public virtual ICollection<Role> Roles { get; set; } } public class Role { public Guid RoleId { get; set; } public string RoleName { get; set; } } } 

and a context class that looks like this:

 public class EFDbContext : DbContext { public DbSet<User> Users { get; set; } public DbSet<Role> Roles { get; set; } protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder.Entity<User>().ToTable("aspnet_users"); modelBuilder.Entity<Role>().ToTable("aspnet_roles"); } } 

but I get an error because I assume that it is looking for a connection between the two tables when there is actually a join table ( aspnet_UsersInRoles ) between them to avoid the many-many link when I try to reference the model role from the user, for example:

 var test = _repository.Users.FirstOrDefault().Roles.Count(); 

{"Invalid column name 'User_UserId'. \ R \ nRequired column name 'User_UserId'. \ R \ nInvalid column name 'User_UserId'." }

Is there a way to map a table together using a join table using an entity structure? Is it better to just add a new ADO.NET entity data model and let the visual studio simply reconstruct the database?

Answer 1

You need to set up many-to-many mapping as follows

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

Edit: You also need to add the Users property to the Role class. Otherwise, the display must be changed to

  modelBuilder.Entity<User>() .HasMany(u => u.Roles).WithMany() .Map(m => { m.ToTable("aspnet_UsersInRoles"); m.MapLeftKey("UserId"); m.MapRightKey("RoleId"); });