Is it possible to directly reference a many-to-many table using entity framework, code first

Question

I am using the entity framework and modelling a many-to-many relationship.

I have created the relationship between the two entities using the fluent API (let's say users and groups):

this.HasMany(t => t.Users)
.WithMany(t => t.Groups)
.Map(
m =>
{
  m.ToTable("GroupMembers");
  m.MapLeftKey("Group_Id");
  m.MapRightKey("User_Id");
});

This works great, but I'd like to also be able to reference the GroupMembers table directly. To do that, I have something like:

[Table("GroupMembers")]
public class GroupMember
{
    #region Properties

    /// <summary>
    /// Gets or sets the group.
    /// </summary>
    public virtual Group Group { get; set; }

    /// <summary>
    /// Gets or sets the Id of rht group.
    /// </summary>
    [Key]
    [Column("Group_Id", Order = 1)]
    public int GroupId { get; set; }

    /// <summary>
    /// Gets or sets the user.
    /// </summary>
    public virtual User User { get; set; }

    /// <summary>
    /// Gets or sets the Id of the user.
    /// </summary>
    [Key]
    [Column("User_Id", Order = 2)]
    public int UserId { get; set; }

    #endregion
}

However, I will get the following error during initializing of the DbContext:

Schema specified is not valid. Errors: (381,6) : error 0019: The EntitySet 'GroupUser' with schema 'dbo' and table 'GroupMembers' was already defined. Each EntitySet must refer to a unique schema and table.

I believe this is because the entity framework does not realize that the GroupMembers table specified in the fluent API and the GroupMembers entities table are actually one in the same. In other words, if I remove the fluent API code which is describing the many-to-many relationship, then I am successfully able to initialize the DbContext.

Can I have a many-to-many table that I can also reference directly?

score 12 · Accepted Answer · answered Mar 21 '12 at 18:57

12

No, you can't. If you want to have access to the join table via a separate entity you must replace your many-to-many relationship by two one-to-many relationships and change the navigation properties in User and Group to refer to GroupMember:

public class Group
{
    public int GroupId { get; set; }
    public virtual ICollection<GroupMember> Members { get; set; }
}

public class User
{
    public int UserId { get; set; }
    public virtual ICollection<GroupMember> Members { get; set; }
}

modelBuilder.Entity<Group>()
    .HasMany(g => g.Members)
    .WithRequired(gm => gm.Group);

modelBuilder.Entity<User>()
    .HasMany(u => u.Members)
    .WithRequired(gm => gm.User);

Why do you want this GroupMember entity? It doesn't contain any business meaning and has only references and keys. Usually you can get and modify any content of the join table by writing LINQ queries and by using the Group and User DbSets/entities and their navigation properties.

answered Mar 21 '12 at 18:57

Slauma

175,098
59
401
420

1

Thanks. Both User and Group inherit from a base table, Principal, and there's another table, Permissions, which contains permissions for both users and groups. The LINQ query for that looks something like: from p in context.Permissions join m in context.GroupMembers on p.PrincipalId equals m.GroupId where m.UserId.Equals(principalId) select p I am not quite sure how to perform that query without directly referencing the GroupMembers table. – Eric Mar 21 '12 at 19:24
@Eric: I'd recommend to create a separate question for this with more details about the model. – Slauma Mar 21 '12 at 19:33
@Eric I have absolutely the same problem, it seems illogical that fluent api is able to only create entities instead of using already existing ones – Lu4 Sep 04 '14 at 04:53
2

"No you can't", Oh boy I wish I'd found this answer 9 hours ago. – Julian Mann Dec 03 '14 at 22:43
YES YOU CAN! You just need to put your conflicting mappings in separate contexts. See my answer below. – GPR Oct 01 '18 at 15:29
NO YOU CAN'T! The question was whether you can simultaneously have an entity that uses an implicit many-to-many relationship at the same time as two many-to-one relationships with an explicit mapping table. Those two things cannot be simultaneously configured on the same entity in the same context. Setting up an entirely different context dodges the problem altogether, and while it will work... you'll now be maintaining a separate context, possibly with it's own migrations or you'll need to disable the database initializer so it doesn't try to verify the data model, which can lead to problems. – Triynko Feb 06 '19 at 16:31

score 2 · Answer 2 · answered Oct 20 '16 at 16:46

2

We got around this by creating a view that is only used to define the relationship. We had other fields in the lookup table that we needed to access.

The view just selects the join fields from the lookup table.

answered Oct 20 '16 at 16:46

Ishmael

30,970
4
37
49

GPR · Answer 3 · 2018-08-02T08:43:53.347

1

Yes you can... If you use another Context.

If you try to map more than one entity to the same table in the same Context then you will get the error. But if you put the two mappings in separate Contexts then there will be no issue. This way you can have one mapping that describes a many-many relationship with the joining table transparent as if there are only foreign keys in it... And you can have another mapping, with its own context, to expose the actual join table if you want to get at any properties of the relationship.

edited Aug 02 '18 at 08:43

answered Feb 03 '17 at 19:11

GPR

492
5
11

Does this context need to be configured and maintain its own set of migrations though? – Triynko Feb 06 '19 at 16:21

score -1 · Answer 4 · edited Jun 20 '20 at 09:12

-1

Yes, you can! Check this out. Also, remove GroupMembers entity and IDbSet collection.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 05 '15 at 20:50

Berezh

942
9
12

1

This doesn't answer the question, quite the opposite. This is about how to make it automatic *without* access to the table, The question was about how to access the table. – Gábor Feb 22 '17 at 11:26
Yeah, he's trying to simultaneous have a many to many join (with a hidden auto-generated mapping table) and two one to many relationships to an explicit mapping table. I've set up the correct configuration for both, and they work when configured independently, but when configured simultaneously it gives the error mentioned in the original post: "Each EntitySet must refer to a unique schema and table." They're just two incompatible approaches. And it makes sense, because of the work required to maintain an explicit join table doesn't make sense if EF is trying to auto-manage one. – Triynko Feb 06 '19 at 16:24

Is it possible to directly reference a many-to-many table using entity framework, code first

4 Answers4

Linked