Many to Many Relationship in Entity Framework User Roles

entity-framework

Question

Hello, I'm attempting to put up my entity structure for a User to Role many to many connection.

What's in the database is shown in the image below:

Many to Many relationships

The User Model is:

public class User : IEntity
    {
        public virtual int UserId { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(100)]
        public virtual string UserName { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(100)]
        public virtual string FirstName { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(100)]
        public virtual string LastName { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(200)]
        public virtual string EmailAddress { get; set; }
        public int AreaId { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(64)]
        public string CreatedByUserName { get; set; }
        public DateTime CreatedDateTime { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(64)]
        public string LastModifiedByUserName { get; set; }
        public DateTime? LastModifiedDateTime { get; set; }

        //Navigation properties
        //public virtual Role Role { get; set; }
        public virtual Area Area { get; set; }

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

}

The role model is:

public class Role : IEntity
    {
        public int RoleId { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(100)]
        public string Name { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(1000)]
        public string Description { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(64)]
        public string CreatedByUserName { get; set; }
        public DateTime CreatedDateTime { get; set; }
        [Column(TypeName = "varchar")]
        [StringLength(64)]
        public string LastModifiedByUserName { get; set; }
        public DateTime? LastModifiedDateTime { get; set; }

        //Navigation Properties
        public ICollection<User> Users { get; set; }
    }

The UserRole is:

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

        //Navigation properties
        public virtual User User { get; set; }
        public virtual Role Role { get; set; }
    }

I assumed I had everything configured properly, however my code reads something like this:

var roles = from r in user.Roles
                        select r.Name;

also self-destructs with the following errors:

Server Error in '/' Application.
Invalid object name 'dbo.RoleUser'. 

So I changed the context by adding the following:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
        {
            modelBuilder.Entity<User>()
                .HasMany(i => i.Roles)
                .WithMany(u => u.Users);

}

But now I'm seeing these errors:

Application at /: Server Error. Role RoleId's column name is invalid. The column name "User UserId" is invalid.

I must not have things set up properly here, then. Can someone assist me in the proper route, Andy?

1
5
11/8/2011 1:49:03 AM

Accepted Answer

The connection table does not need to be modelled.UserRole as a class since it only participates in the connection through the main keys of the tables. therefore discard theUserRole class.

EF may assume the connection table name to be if you are modeling an existing database asRoleUser . You may set up the link table as follows to prevent this.

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

 }
9
11/8/2011 4:16:09 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow