4

Is it possible to create a One-to-Many relationship with Code First that uses a link/join table between them?

public class Foo {
    public int FooId { get; set; }
    // ...

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

public class Bar { 
    public int BarId { get; set; }
    // ...

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

I want this to map as follows:

TABLE Foo
    FooId INT PRIMARY KEY
    ...

TABLE Bar
    BarId INT PRIMARY KEY

TABLE FooBar
    FooId INT PRIMARY KEY / FOREIGN KEY
    BarId INT FOREIGN KEY

With this, I would have the ability to make sure Foo only has one Bar, but that Bar could be re-used by many different Foos.

Is this possible with Entity Framework? I would prefer to not have to put the key in Foo itself because I don't want a nullable foreign key. If it is possible please provide an example using the Fluent API and not the data annotations.

Dismissile
  • 32,564
  • 38
  • 174
  • 263

1 Answers1

4

You can use Entity splitting to achieve this

public class Foo
{
    public int FooId { get; set; }

    public string Name { get; set; }

    public int BarId { get; set; }

    public virtual Bar Bar { get; set; }
}

Then in your custom DbContext class

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Foo>().HasKey(f => f.FooId);
        modelBuilder.Entity<Foo>()
            .Map(m =>
                     {
                         m.Properties(b => new {b.Name});
                         m.ToTable("Foo");
                     })
            .Map(m =>
                     {
                         m.Properties(b => new {b.BarId});
                         m.ToTable("FooBar");
                     });

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

        modelBuilder.Entity<Bar>().HasKey(b => b.BarId);
        modelBuilder.Entity<Bar>().ToTable("Bar");
    }

The BarId column will be created as a not null column in FooBar table. You can check out Code First in the ADO.NET Entity Framework 4.1 for more details

Eranga
  • 32,181
  • 5
  • 97
  • 96
  • Ok it has created a FooBar table that looks right. The Bar table looks right. The only problem is that it created the Foo table as Foo1 instead of Foo? It also makes BarId on FooBar nullable, which I wanted to avoid and that's why I wanted the link table. – Dismissile Sep 22 '11 at 16:22
  • Still not 100% right. It maps the schema exactly like I want it, but the entities don't work right. It is forcing my Foo to always have a Bar, which is not the case. I tried switching Foo.BarId to int? but then it tries to insert null in the join table. When a Bar isn't present, I don't want a row in the join table at all, not a null key. – Dismissile Sep 22 '11 at 17:23
  • @Dismissile yes it will always insert into `FooBar`. AFAIK your scenario can not be modeled exactly. – Eranga Sep 23 '11 at 00:17