Associations without foreign keys

23 Oct 2009 1 mins Entity Framework

Michal Bláha asked me, before my session when I stopped in his office, whether it’s possible to create associations in Entity Data Model without foreign keys in database, as he’s not using FKs, he’s enforcing referential integrity in application (yeah, if you’re transaction guy like I am, your brain is about to blow).

Well, it’s for sure possible. First we define some simple tables:

create table test_master(
  id int primary key,
  foo nvarchar(20) not null
);
create table test_detail(
  id int primary key,
  id_master int not null,
  bar nvarchar(20) not null
);

You see, no FK defined. Then you generate model from database, just next > next > finish style and you end up with:

Now the magic begins. 😃 Just kidding. First delete the id_master column from entity, it has nothing to do in conceptual model. Next create new association (right click in empty space in designer) and create it as 1-*. OK, we’re almost there. The last step, is to map the association. It’s mapped to test_detail: test_master.id to id_master and test_detail.id to id.

Now you can start querying the data across associations.

string s = context.test_master.Include("test_details").ToTraceString();

Easily done, isn’t it.

Jiří Činčura is .NET, C# and Firebird expert. He focuses on data and business layers, language constructs, parallelism, databases and performance. For almost two decades he contributes to open-source, i.e. FirebirdClient. He works as a senior software engineer for Microsoft. Frequent speaker and blogger at www.tabsoverspaces.com.