Tip 39 – How to set overlapping Relationships – EF 4.0 only

Scenario:

In EF 4 we have FK relationships, available for the first time in .NET 4.0 Beta 2, so it is now possible to have a model something like this:

public class Division
{
public int DivisionID {get;set} // Primary Key
public string Name {get;set;}
public virtual List<Lawyer> Lawyers {get;set;}
public virtual List<Unit> Units {get;set;}
}
public class Lawyer
{
public int LawyerID {get;set;} // Primary Key
public int DivisionID {get;set;} // Primary Key + FK to Division
public string Name {get;set;}
public virtual Division Division {get;set;}
public virtual List<Unit> Units {get;set;}
}
public class ProductTeam
{
public int ProductID {get;set;} // Primary Key
public int? DivisionID {get;set;} // FK to Division & Lawyer
public int? LawyerID {get;set;} // FK to Lawyer
public string Name {get;set;}
public virtual Division Division {get;set;}
public virtual Lawyer Lawyer {get;set;}
}

Notice that the Lawyer has a Compound key made up of both the LawyerID and DivisionID.

This makes things interesting when you start manipulating the ProductTeam class, which has both Lawyer and Division references and the necessary backing FK properties.

If you do something like this:

var team = (from t in ctx.ProductTeams
where t.Lawyer.Name == “Fred Bloggs”
select t).FirstOrDefault();
team.Lawyer = null;
ctx.SaveChanges();

What should this do exactly?

Does this mean clear team.LawyerID & team.DivisionID or just team.LawyerID?

From the relational standpoint nulling any FK property is sufficient to make the relationship go away.

Hmm…

It can be very hard to workout exactly what the user intended, so rather than introduce some magic rules based on naming conventions etc, the EF uses a consistent rule that you can rely on:

When the users sets a reference relationship to null, the EF nulls every nullable FK property that backs the relationship, whether that FK participates in another relationship or not.

Problem:

So in this case the EF nulls both DivisionID and LawyerID, because both back the Lawyer navigation property.

Which means nulling the Lawyer *also* nulls the Division.

Did you really want to do that?

Maybe maybe not.

Solution:

If you only wanted to null out the Lawyer, you have a couple of options:

Change the model so that the DivisionID FK isn’t nullable, in this case the EF can only null out the LawyerID, so the relationship to the Division would be left intact.

But a solution that changes the model isn’t always possible, what if the Division really needs to be nullable too?

The better option is to simply manipulate the relationships through the FK properties directly:

var team = (from t in ctx.ProductTeams
where t.Lawyer.Name == “Fred Bloggs”
select t).FirstOrDefault();
team.LawyerID = null;
ctx.SaveChanges();

This leaves the DivisionID & Division untouched, as desired.

Comments

• Anonymous
  November 01, 2009
  I just came across this site. Awesome links. i am going to dig in. I have one request - We have a complex business application which calls for a very hierarchical data model. Since the db was contructed 10 years ago when SQL server did not have hierarchical query support we make use of a home grown approach. For instance we have a regulatory tree where rules are set at the state level and county level. We make use of two tables to capture regulator entity and regulatory entity links (pointing back to the entity) with distances to the parent. I am wondering if you can post some examples of how one cane make use of EF in such complex scenarios. Our queries are really complicated because we always have to find the nearest setting for an entity as we walk up the treeThanks.