Mapping Many to Many Relationship w/ Foreign Key Reference

.net, c#, entity-framework, relational-database, sql-server

Solution

Your model will indeed create a foreign key `FooId` in the `Bar` which belongs to the relationship defined by `Foo.BrideBars`. EF doesn't relate this navigation property to one of the `ICollection<Foo>` properties in `Bar` because there are two of them and EF cannot determine uniquely which is the correct pair. As a result it creates a relationship for `Foo.BrideBars` without a navigation property on the other end. So to speak, there is an invisible `Bar.Foo` property which causes the foreign key.

The database schema you want to map to a model does not really represent a many-to-many relationship but instead two one-to-many relationships with the intermediate "bridge" entity `FooBar`. You must use this class in the navigation properties to define the correct relationships. It would look like this:

public class Foo
{
    public int Id { get; set; }
    public string Text { get; set; }
    public string Description { get; set; }

    public int BarId { get; set; }
    public Bar Bar { get; set; }

    public virtual ICollection<FooBar> FooBars { get; set; }
}

public class Bar
{
    public int Id { get; set; }
    public string Text { get; set; }
    public string Description { get; set; }

    public virtual ICollection<Foo> Foos { get; set; }
    public virtual ICollection<FooBar> FooBars { get; set; }

}

public class FooBar
{
    [Key, Column(Order = 0)]
    public int FooId { get; set; }
    [Key, Column(Order = 1)]
    public int BarId { get; set; }

    public virtual Foo Foo { get; set; }
    public virtual Bar Bar { get; set; }

    public bool IsRead { get; set; }
}

The correct relationships will be detected by naming conventions in this model. Only for the `FooBar` entity it is necessary to define a key explicitly because the property names do not meet the conventions (no `Id` and no `FooBarId` property). In this model it makes sense to use a composite key in `FooBar`.

I guess, your real classes and properties don't have the name `Foo` and `Bar`. If your real names do not follow the conventions you possibly have to specify the relationships with annotations - or with Fluent API:

modelBuilder.Entity<Foo>()
    .HasRequired(f => f.Bar)
    .WithMany(b => b.Foos)
    .HasForeignKey(f => f.BarId);

modelBuilder.Entity<FooBar>()
    .HasKey(fb => new { fb.FooId, fb.BarId }); // replaces the [Key] annotations

modelBuilder.Entity<FooBar>()
    .HasRequired(fb => fb.Foo)
    .WithMany(f => f.FooBars)
    .HasForeignKey(fb => fb.FooId);

modelBuilder.Entity<FooBar>()
    .HasRequired(fb => fb.Bar)
    .WithMany(b => b.FooBars)
    .HasForeignKey(fb => fb.BarId);

In your database schema the `FooBar` table will have a composite primary key:

[FooBar]       [Foo]          [Bar]

FooId PK,FK    Id PK          Id PK
BarId PK,FK    BarId FK       Name
IsRead         Name           Description
               Description    

But having a PK in `FooBar` is necessary because every entity in an EF model must have a key property defined - either single or composite - which maps to a primary key in the database table.

In this question - Create code first, many to many, with additional fields in association table - are more details how to work with such a type of relationship. (Sometimes people also call it "many-to-many relationship with payload" (the `IsRead` property is the "payload" in your example model), but in fact it's not many-to-many.)

Problem

Original source

Related problems

• Create code first, many to many, with additional fields in association table