Share via

Tip 11 – How to avoid Relationship Span

  • Article

Background and Motivation:

In my last post on EF Jargon I introduced the concept of Relationship Span.

If you remember Relationship Span is basically responsible for compensating for the lack of Foreign Key properties* in your Entity.

Relationship Span for an Entity, lets say StaffMember, insures that the Entity Framework knows the EntityKeys of the other Entities that the StaffMember has 0..1 relationship with (for example DisciplineHistory).

These EntityKeys are important because without them the Entity Framework doesn’t know how to delete or update the StaffMember (See Tips 7 & 9 for more information on this concept).

Now usually a 0..1 type of relationship is established in the database by having an FK in the StaffMember table pointing to the target DisciplineHistory table.

In this situation Relationship Span can pull the EntityKey for the DisciplineHistory pretty cheaply, because we can do 'join elimination'.

Now no two databases are the same though, and it entirely possible to model this same relationship completely differently: you could put the FK in the DisciplineHistory table.

One way to do this is make the PK of the related DisciplineHistory table the same as the PK of the StaffMember, i.e. the PK is the FK. In fact this is the only way, that the EF supports, in which you have a 1 to 0..1 relationship that is enforced in the database.

This approach is mostly commonly used when modeling additional 'Aspects' of an Entity in the database.

In these situations Relationship Span is a little more costly, because the Entity Framework is either not smart enough to do join elimination or it just isn't possible (both are possible).

If you extrapolate this situation further so that there are many entities related via 0..1 relationships (for example StaffMember has many 'Aspects' like SalaryHistory, DisciplineHistory, AuditLog and BIO etc) then you easily end up in a place where doing a simple query for a StaffMember ends up being very costly, purely because of the cost of doing the Relationship Span.

The obvious question is…

How do you avoid Relationship Span?

Well it is very very easy. You simply do a no-tracking query:

var source = ctx.Staff;
source.MergeOption == MergeOption.NoTracking;

var staff = (from s in source
where s.ID == 12
select s).First();

And the resulting query won’t “span” in information about all the related SalaryHistory, DisciplineHistory etc.

Unfortunately the result won’t be ‘in’ the ctx either. So if you plan on using the result you will have to manually Attach it again**.

*There is a workaround for this in .NET 4.0, namely FK Associations, if you have FK Associations instead of Independent Associations we don’t need to do relationship span at all.

**With short-lived contexts this rarely causes problems, but with long-lived contexts you have to be careful the same Entity (but not the same object) isn’t already in the context as a result of an earlier query. Also if you want to update the entity, rather than simply using it to establish new relationships, you have to go and get all the PKs of all the related entities first.

Comments

  • Anonymous
    April 07, 2009
    Hopefully if you're reading this you've noticed that I've started a series of Tips recently. The Tips
  • Anonymous
    June 19, 2009
    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing.
  • Anonymous
    June 19, 2009
    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing
  • Anonymous
    October 11, 2009
    The comment has been removed
  • Anonymous
    October 12, 2009
    @Yash,If you have all the columns for Coordinators in the tbl_coordinator, I don't understand why you are trying to map Coordinators to tbl_user_profile.So I'll assume you are re-using some columns from the tbl_user_profile in the Coordinators.Here you have a pretty fundamental problem. You can't have two properties User.X and Coordinator.X which point to the same value in the database. You could easily get unexpected inconsistencies.What are exactly are you trying to do?Alex