How to solve Code First from database many-to-many mapping in Entity Framework?

Question

Bellow code snippet showing my scenario:

[Table("User")]
public partial class UserModel
{
 public UserModel()
 {
    UserRole = new HashSet<UserRoleModel>();
 }

  public int UserID { get; set; }
  public string FullName { get; set; }
  public virtual ICollection<UserRoleModel> UserRole { get; set; }
}

[Table("UserRole")]
public partial class UserRoleModel
{
    public UserRoleModel()
    {
            User = new HashSet<UserModel>();
    }

   public int RoleID { get; set; }
   public string RoleName { get; set; }
   public virtual ICollection<UserModel> User { get; set; }

}

Now within OnModelCreating(DbModelBuilder modelBuilder) EF Generate code like bellow

modelBuilder.Entity<UserModel>()
                .HasMany(e => e.UserRole)
                .WithMany(e => e.User)
                .Map(m => m.ToTable("UserRoleMapping").MapLeftKey("UserID").MapRightKey("UserRoleID"));

now this is fine add / insert data into UserRoleMapping table. But how to

Get / Update data from UserRoleMapping table ?

I try to solve this issue following create-code-first-many-to-many the post and come-up with third class with join entity

 public partial class UserRoleMappingModel
    {
        [Key, Column(Order = 0)]
        public Guid UserId { get; set; }
        public UserModel User { get; set; }

        [Key, Column(Order = 1)]
        public int RoleId { get; set; }
        public UserRoleModel UserRole { get; set; }
    }

then add public virtual ICollection<UserRoleMappingModel> UserRoleMapping { get; set; } in both the UserModel and UserRoleModel class

But when I try to GET value from database using bellow code

var results = _userRepository.GetAll()
                 .Include(r => r.UserRoleMapping
                 .Select(s => s.UserRole))
                 .SingleOrDefault(e => e.ID == id);

It throws ERROR

"An error occurred while executing the command definition. See the inner exception for details.System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'dbo.UserRoleMappingModel'.\r\n

Even I tried bellow Configuration within OnModelCreating, but nothing work as expected

modelBuilder.Entity<UserRoleMappingModel>()
 .HasKey(e => new { e.UserId, e.RoleId });

When you introduce a junction class (`UserRoleMappingModel`) you should remove the many-to-many mapping and add two 1:n mappings. — Gert Arnold, Jan 10 '18 at 20:33
And why to you want to have the mapping table in your model? It's fine to do, but it's sometimes inconvenient to have to traverse the mapping table in code. — David Browne - Microsoft, Jan 10 '18 at 20:36
@How to get value from Mapping Table without using `Mapping Model` ? — Shubhajyoti Ghosh, Jan 11 '18 at 11:20
@GertArnold: are you talking to remove `.Map(m => m.ToTable("UserRoleMapping").MapLeftKey("UserID").MapRightKey("UserRoleID"));` this ? — Shubhajyoti Ghosh, Jan 11 '18 at 11:32
@DavidBrowne-Microsoft: Without `MappingModel` how to get value from mapping table ? — Shubhajyoti Ghosh, Jan 11 '18 at 12:47
You usually don't need to directly access the linking table, and so EF leaves it out of your entity model, by default. — David Browne - Microsoft, Jan 11 '18 at 14:27
@DavidBrowne-Microsoft: But I need to acess that table, let say I want to Update `User Role ID` how can I do it without Mapping model ? Is their any other way !! — Shubhajyoti Ghosh, Jan 11 '18 at 14:36
You use the Navigation Properties. You add or remove a User from a Role, or a Role from a User. — David Browne - Microsoft, Jan 11 '18 at 14:39
@DavidBrowne-Microsoft: Will you please provide a code snippet, for it — Shubhajyoti Ghosh, Jan 11 '18 at 14:46

kara · Answer 1 · 2018-01-11T11:45:05.813

0

Your class UserRoleMappingModel has no Table-Attribute. Bcause of this, EF searches for a Table UserRoleMappingModel instead von UserRoleMapping.

You have to choose: Either map the n-to-n relationship and don't access the mapping-table or load the table to access the values in it.

As workaround you could implement a Not-Mapped column:

[Table("User")]
public partial class UserModel
{
    public UserModel()
    {
        UserRole = new HashSet<UserRoleModel>();
    }
    public int UserID { get; set; }
    public string FullName { get; set; }
    public virtual ICollection<UserRoleMappingModel> Mappings { get; set; }
    public virtual ICollection<UserRoleModel> UserRole
    {
        get
        {
            return this.Mappings.Select(s => s.UserRole);
        }
    }
}

edited Jan 11 '18 at 11:45

answered Jan 11 '18 at 10:24

kara

3,205
4
20
34

If I don't write a Mapping- Model then how to get value from Mapping table ? If put `Table-Attribute` throw error duplicate schema. – Shubhajyoti Ghosh Jan 11 '18 at 11:18
Correct. You can use any table only once. If you need the values of mapping-table, you can't map "n-n" you have to do "1-n-1". Example: you have to access the roles of you user this way: `myUserModel.Mapping.UserRole` – kara Jan 11 '18 at 11:28

score 0 · Accepted Answer · answered Jan 11 '18 at 19:39

AS per GertArnold response I solve the issue in bellow way.

1st Remove below settings

modelBuilder.Entity<UserModel>()
                .HasMany(e => e.UserRole)
                .WithMany(e => e.User)
                .Map(m => m.ToTable("UserRoleMapping").MapLeftKey("UserID").MapRightKey("UserRoleID"));

2nd Add bellow settings

modelBuilder.Entity<UserRoleMappingModel>()
 .HasKey(e => new { e.UserId, e.RoleId });

3rd add table property in Mapping Model

[Table("UserRoleMapping")]
public partial class UserRoleMappingModel
    {
        [Key, Column(Order = 0)]
        public Guid UserId { get; set; }
        public UserModel User { get; set; }

        [Key, Column(Order = 1)]
        public int RoleId { get; set; }
        public UserRoleModel UserRole { get; set; }
    }

4th Create a Mapping Repository

IUserRoleMappingRepository

5th a simple get Method (Problem Solved)

var results = _userRoleMappingRepository.SearchFor(e => e.UserId == id)
                                                            .Select(s => new
                                                            {
                                                                s.UserId,
                                                                s.UserRoleId,
                                                                s.UserRole.RoleName
                                                            })
                                                           .FirstOrDefault();

Point to be noted : using bellow query I able to get result but unable to serialize with Newtonsoft.Json due to self referencing issue

var results = _userRepository.GetAll()
                 .Include(r => r.UserRoleMapping
                 .Select(s => s.UserRole))
                 .SingleOrDefault(e => e.ID == id);

Try bellow JsonSerializerSettingssetting alternatively but unable to serialize sucessfully

PreserveReferencesHandling = PreserveReferencesHandling.All / Object
ReferenceLoopHandling = ReferenceLoopHandling.Serialize / Ignore

How to solve Code First from database many-to-many mapping in Entity Framework?

2 Answers2