Modelling a nullable foreign key Entity Framework

Question

Take the following pseudocode database

table Foo
{
    FooID int not null,
    name varchar not null
}

table Bar
{
    BarID int not null,
    name varchar not null
}

table Waa
{
    WaaID int not null,
    name varchar not null
}

table Link
{
    LinkID int not null,
    FooID int null,
    BarID int null,
    WaaID int null,
    Description varchar not null
}

The purpose of the Link table is to allow me to link entities together in a single table, without having multiple Foo_Bar_Link, Foo_Waa_Link etc. tables.

I am moddeling this DB in EntityFramework v5. As such, i want to be able to specify a One to Many relationship between Foo and Link, so that i can get all the Links associated with a given Foo.

Problem
EntityFramework Designer only lets one-many relationships exist where the foreign key is not null. I want a Foo to have a navigation property Associations which gets all of the Association entities with FooID = Foo.FooID. As a note, i am using the Designer.

Can you show your Link model? You might also want to see this post — Nilesh
I am creating the whole thing using the EF Designer, so i am not cutting my own model code. I can paste it if required... — David Colwell
See simple-talk.com/sql/database-administration/… - "One Ring to rule them all and in the darkness bind them" — Colin
Actually Colin, What he is talking about is a table which has no explicit relationship. In this case it would be the equivalent of having a Generic table with Table and Column values (for example "Foo": 1, "Bar":2), so that one can lazily join the tables using WHERE Table = 'Foo' and Value = 1 which is horribly bad practice. The table design is explicit (This foo is related to This Bar) — David Colwell
I tried the designer to start with and found it too limiting. I soon switched to code-first and haven't looked back since. In code-first nullable foreign keys are modelled using nullable properties in the Model class. Simple as that. — Colin

Piotr Stapp Piotr Stapp · Accepted Answer · 2013-08-13T04:19:34

EF is not designed to support unusual models like yours. The only way which I know is to insert "empty" meaning rows for example with id equals to -1. In business logics or in DTO implement "clever" getter and setter.

Modelling a nullable foreign key Entity Framework

1 Answers