Tip 11 – How to avoid Relationship Span


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 (5)

  1. Anonymous says:

    Hopefully if you’re reading this you’ve noticed that I’ve started a series of Tips recently. The Tips

  2. Anonymous says:

    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing.

  3. Anonymous says:

    What are Stub Entities? A stub entity is a partially populated entity that stands in for the real thing

  4. Anonymous says:

    Hi,

    We have a User entity type mapped to a table named tbl_user_profile. This has fields like firstname, lastname, etc. Another entity type Coordinator is mapped to tbl_coordinator. The table has a FK name user_id pointing to the tbl_user_profile table. All coordinators are users but the reverse is not true. The framework we use, expects all fields in the same class and hence, we want all fields of the coordinator in the coordinator entity type. We will not use inheritance because we use RIA domain services which does not support inheritance.

    To achieve this, we added the tbl_user_profile table in the mapping for Coordinator entity type. While doing this we get following error :

    "EntitySets ‘Users’ and ‘Coordinators’ are both mapped to table ‘tbl_user_profile’. Their Primary Keys may collide".

    Error 1 Error 3033: Problem in Mapping Fragment starting at line 2016: EntitySets ‘Users’ and ‘Coordinators’ are both mapped to table ‘tbl_user_profile’. Their Primary Keys may collide.

    E:EDMXEntityModel.edmx 2017 15 MyTrial.Data

    "User" has entityset named "Users" and "Coordinator" has entityset named "Coordinators".

    What is the cause and solution to this problem. Any way to work around it?

    Thanks,

    Yash

  5. Alex D James says:

    @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