Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

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:

AgeGroup

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

Customer

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?

SELECT
  AgeGroup.Description

FROM
  Customer,
  AgeGroup

WHERE
  Customer.CustomerID = 493
  AND AgeGroup.MinAge <= Customer.Age
  AND AgeGroup.MaxAge >= Customer.Age
share|improve this question
add comment

2 Answers 2

up vote 1 down vote accepted

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); }
        }
    }
}
share|improve this answer
add comment

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.

share|improve this answer
    
-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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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