I'm not sure if "Ranged Foreign Key Relationship" is an actual term or if I just made it up, but this is what I am talking about:

AgeGroup Table

int MinAge { get; set; }
int MaxAge { get; set; }
string Description { get; set; }

Customer Table

public int CustomerID { get; set; }
public string Name { get; set; }`
public int Age { get; set; }
public virtual AgeGroup AgeGroup { get; set; }

Given the above two tables, how can I join them using the (EntityFramework, DataAnnontations, DbContext) and be able to access Customer.AgeGroup.Description?

Consider the following example data:


MinAge MaxAge Description
0      12     Youngin
13     19     Teenager
20     29     Twenties
30     39     Thirties
40     9999   Old


CustomerID Name        Age
345        Joe Smith   17
493        Cobaltikus  31
631        Jane Doe    29

How can I relate these? Cobaltikus has an Age of 31. There is no corresponding AgeGroup record with 31. The corresponding AgeGroup record is the one with MinAge = 30 and MaxAge = 39. I can do this easily in SQL, but how do you do it with EntityFramework?



  Customer.CustomerID = 493
  AND AgeGroup.MinAge <= Customer.Age
  AND AgeGroup.MaxAge >= Customer.Age

The AgeGroup table should not be connected to the Customer table by way of a foreign key or Entity Framework construct.

The AgeGroup table is in fact a lookup table, but the lookup should occur at runtime, not compile time.

You should add some custom code to your model by way of a partial class; and encapsulate this lookup inside of either a property or a 'Get' method.

Here's some example code:

using System.Linq;

namespace CobaltikusProject.Models
    public partial class Customer
        public string AgeGroup
            get { return EFEntities.AgeGroups.Single(a => a.MinAge <= this.Age && this.Age <= a.MaxAge); }

There is no such thing as a "ranged foreign key relationship". This is not something that relational databases handle (at least not with foreign keys).

The way to handle the link between customer and their age group is with a sub-query or a lookup query. There is no way to do this with a navigation property in EF. You could create a method on the data context based on a stored procedure that looks up the appropriate age group given a customer key. This would be almost as easy to use in your code as a navigation property.

  • -1: For stored procedure. Definitely overkill for this problem. – Jim G. Aug 7 '12 at 19:40
  • @JimG. unless you're shop has policies that restrict all application access to stored procedures, in which case they aren't overkill, they're the mandate. I see from your answer that you agree with me that a subquery/lookup method is what is needed. Thanks for being disagreable though. – Joel Brown Aug 8 '12 at 0:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.