Mapping Many to Many Relationship w/ Foreign Key Reference

Question

This should be a simple question for the well versed EF user.

I have the following schema (in my head) of how the relationships between the tables should look.

[FooBar]      [Foo]          [Bar]

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

Though, when I try to generate the schema using EF code-first it fails to interpret the relationships between the entities as I've interpreted them (adds foreign key FooId to the [bar] table) and fails to fully create the [FooBar] bridge table.

If someone could guide me on how to achieve the above schema using EF4 code-first I'd appreciate it. Whether the solution involves attributes on my POCO models, fluent configurations or a hybrid of both doesn't matter much - as long as the desired database schema is created.

---

POCO Models:

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; } /* bar entity */

    public virtual ICollection<Bar> BridgedBars { get; set; }

    public Foo()
    {
        Bars = new List<Bar>();
    }
}

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<Foo> BridgedFoos { get; set; }

    public Bar()
    {
        Foos = new List<Foo>();
        BridgedFoos = new List<Foo>();
    }
}

public class FooBar
{
    public int FooId { get; set; }
    public int BarId { get; set; }

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

    public bool IsRead { get; set; }
}

Answer 1

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.)