How to define Many-to-Many relationship through Fluent API Entity Framework?

The terms Left and Right in MapLeftKey and MapRightKey in the many-to-many mapping with Fluent API can be misunderstood and I guess your problem is caused by this misunderstanding.

One might think that it means they describe the columns that are “left” and “right” in the many-to-many join table. That’s actually the case if you let EF Code-First create the database and join table based on your Fluent mapping.

But it’s not necessarily the case when you create a mapping to an existing database.

To illustrate this with the prototypic many-to-many example of a User–Role model assume you have an existing database with a Users, Roles and RoleUsers table:

Many-to-many database tables

Now, you want to map this table schema to a simple model:

public class User
{
    public User()
    {
        Roles = new List<Role>();
    }

    public int UserId { get; set; }
    public string UserName { get; set; }
    public ICollection<Role> Roles { get; set; }
}

public class Role
{
    public int RoleId { get; set; }
    public string RoleName { get; set; }
}

And you add the Fluent mapping for the Users entity (you must do it this way, because by convention the model above would be one-to-many and you can’t start from the Role entity side because it has no Users collection):

modelBuilder.Entity<User>()
    .HasMany(u => u.Roles)
    .WithMany()
    .Map(m =>
    {
        m.MapLeftKey("RoleId");  // because it is the "left" column, isn't it?
        m.MapRightKey("UserId"); // because it is the "right" column, isn't it?
        m.ToTable("RoleUsers");
    });

This mapping is wrong and if you try to put “Anna” into role “Marketing”…

var anna = ctx.Users.Find(1);
var marketing = ctx.Roles.Find(2);

anna.Roles.Add(marketing);

ctx.SaveChanges();

…SaveChanges will throw exactly the exception you are having. The reason becomes clear when you capture the SQL command that is sent with SaveChanges:

exec sp_executesql N'insert [dbo].[RoleUsers]([RoleId], [UserId])
values (@0, @1)
',N'@0 int,@1 int',@0=1,@1=2

So, EF wants to insert here a row into the join table RoleUsers with a RoleId of 1 and a UserId of 2 which is causing the foreign key constraint violation because there is no user with UserId 2 in the Users table.

In other words, the mapping above has configured the column RoleId as the foreign key to table Users and the column UserId as the foreign key to table Roles. In order to correct the mapping we have to use the “left” column name in the join table in MapRightKey and the “right” column in MapLeftKey:

        m.MapLeftKey("UserId");
        m.MapRightKey("RoleId");

Actually looking at Intellisense the description makes it clearer what “Left” and “Right” really mean:

MapLeftKey

Configures the name of the column(s) for the left foreign key. The
left foreign key represents the navigation property specified in the
HasMany call.

MapRightKey

Configures the name of the column(s) for the right foreign key. The
right foreign key represents the navigation property specified in the
WithMany call.

So, “Left” and “Right” refer to the order in which the entities appear in the Fluent mapping, not to the column order in the join table. The order in the table actually doesn’t matter, you can change it without breaking anything because the INSERT sent by EF is an “extended” INSERT that also contains the column names and not only the values.

Perhaps MapFirstEntityKey and MapSecondEntityKey would have been a less misleading choice of those method names – or maybe MapSourceEntityKey and MapTargetEntityKey.

This was a long post about two words.

If my guess is right that it has anything to do with your problem at all then I would say that your first mapping is incorrect and that you only need the second and correct mapping.

More Related Contents:

Leave a Comment Cancel reply