Entity Framework 6 is appending underscore with foreign key columns

Question

I am using entity framework version 6 in my MVC5 application. I data model (.edmx) contains 2 entities User and Role corresponding to my tables in the database Users and Roles.

Users table contains columns: Id int, Name nVarchar(100), RoleId int (RoleId has foreign key constraint with Roles.Id)

Roles table contains columns: Id int, Name nVarchar(100)

My Entity model is generating the correct entities as per table structure.

Now when I am querying User entity (_db.Users.ToList()) from DB context, entity framework is looking for Role_Id column in user table instead of RoleId column, hence resulting in error.

So my question is why entity framework query generator is looking for Role_Id column while querying entity?

spadelives spadelives · Accepted Answer · 2016-04-29T16:16:42

Users and Roles would normally have a many-to-many relationship so you should have the corresponding relationships defined in both Users and roles.

public class Users
{
    public int Id { get; set; }
    public string UserName { get; set; }

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

public class Role
{
    public int Id { get; set; }
    public string Name { get; set; }

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

You can also explicitly define the association as an entity if you'd like to add more attributes to the relationship:

public class UserRoles// Association table implemented as entity
{
    public int UserId{ get; set; }
    public virtual User User{ get; set; }
    public int RoleId{ get; set; }
    public virtual Role Role{ get; set; }
    public DateTime AssignmentDate{ get; set; }
}

You can also define the relationship explicitly http://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx

Finally, you can also use DataAnnotions Create Foreign Key using Data Annotations

Entity Framework 6 is appending underscore with foreign key columns

2 Answers