Prevent Entity Framework from creating foreign key in database

Question

I have a code-first database with the following POCOs:

public Foo {
    public int FooId { get; set; }
    public virtual ICollection<Bar> Bars { get; set; }
}

public Bar {
    public int BarId { get; set; }
    public virtual Foo DefaultFoo { get; set; }
    public virtual ICollection<Foo> Foos { get; set; }
}

This creates the following tables in the database:

Foo

FooId (PK, int, not null)
Bar_BarId (FK, int, null)

Bar

BarId (PK, int, not null)
DefaultFoo_FooID (FK, int, null)

FooBar

Bar_BarId (PK, FK, int, not null)
Foo_FooId (PK, FK, int, not null)

As you can see the Foo table gets a foreign key relation to the Bar table even if the Foo POCO does not have a one-to-one navigation property to the Bar POCO.

However, if I remove the DefaultFoo property from Bar this foreign key is removed.

And, if I leave DefaultFoo in, but remove Bars from Foo and Foos from Bar this foreign key is removed.

In other words, this foreign key is only present if both DefaultFoo and Foos is present on Bar.

How can I make Entity Framework not create this unneccessary foreign key (preferrably without using FluentApi)?

Could you describe what you are trying to achieve? Having a Multiple- and Single- Foo property on Bar doesn't make sense to me. Also, by giving Bar a property of type Foo, you DO make a one-to-one relation on those classes. — J. Swietek
Could you try adding public in SingleFooId{get;set}; in Bar class and decorate the navigational property [ForeignKey("SingleFooId")]public virtual Foo SingleFoo { get; set; } — Developer
@J.Swietek One Barcan have multiple Foo, but one of those Foos is a default value. This is so that when there are many Foos on a Bar you know which one to use if not specifically specified. I've edited the model to say Default instead of Single to (hopefully) make it a bit clearer. — GTHvidsten

tschmit007 tschmit007 · Accepted Answer · 2016-09-06T10:14:51

my guess is:

Foo.Bar_BarId is for Bar.Foos
Bar.DefaultFoo_FooId is for Bar.DefaultFoo
FooBar table is for Foo.Bars

I do not/can not say why.

But For me you have to help de default behavior by using some configuration method: fluent or annotations to explicitly set the many/many relation.

You can challenge that by using linqpad and check the generated sql for some queries.

Prevent Entity Framework from creating foreign key in database

2 Answers