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; }
}

As you have a property (IsRead) on the FooBar relation that you likely want to set from code, both Foo and Bar need their related collections to be FooBars. Once you map properties at both ends (i.e. the virtual collections in Foo and Bar) to FooBar EF should create the relation correctly. Use the HasMany().WithRequired() to map each class to FooBar. — Morten Mertner, Jun 03 '12 at 06:28

score 9 · Accepted Answer · edited May 23 '17 at 11:50

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

exactly what i was looking for and yes having a composite primary key on `FooBar` was my intent - just forgot to mention it! Thanks for the great answer and explanation! once my profile achieves *15 points* i'll surely come back and up-vote. — culturalanomoly, Jun 03 '12 at 15:01

Mapping Many to Many Relationship w/ Foreign Key Reference

1 Answers1

Linked