Comparison of many in many ways with the foreign keys directory

Question

This should be a simple question for a familiar EF user.

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

[FooBar] [Foo] [Bar] FooId PK,FK Id PK Id PK BarId PK,FK BarId FK Name IsRead Name Description Description 

Although, when I try to generate a circuit using the EF-first code, it cannot interpret the relationships between the objects as I interpreted them (adds the foreign key FooId to the [bar] table) and cannot create the bridge table [FooBar] .

If someone can guide me on how to achieve the above scheme using the EF4 code, I would appreciate it. Regardless of whether the decision is related to the attributes of my POCO models, the current configurations or a hybrid of both does not 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 the FooId foreign key in Bar , which belongs to the relation defined by Foo.BrideBars . EF does not associate this navigation property with one of the ICollection<Foo> properties in Bar , because there are two of them, and EF cannot unambiguously determine what is the correct pair. As a result, a link is created for Foo.BrideBars without the navigation property on the other end. Thus, there is an invisible Bar.Foo property that invokes a foreign key.

The database schema that you want to map to the model does not actually represent a many-to-many relationship, but instead has two one-to-many relationships with the FooBar intermediate bridge. You must use this class in the navigation properties to determine the correct relationship. It will 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; } } 

Correct relationships will be discovered by naming conventions in this model. Only for the FooBar object is FooBar necessary to explicitly define the key, because the property names do not match the conventions (the Id property and no FooBarId ). In this model, it makes sense to use a composite key in FooBar .

I think your real classes and properties do not have the name Foo and Bar . If your real names are inconsistent, you may need to specify a relationship with annotations - or with the 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 the presence of PK in FooBar necessary, because each object in the EF model must have a specific key property - one or multiple, which is mapped to the primary key in the database table.

In this question First, create code, many for many, with additional fields in the join table - more on how to work with this type of relationship. (Sometimes people also call this a many-to-many relationship with a payload (the IsRead property is a “payload” in your IsRead model), but it's not really many-to-many.)