Entity Framework – Joining Tables with Ranged Foreign Key Relationships

entity-framework

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

Best Answer

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); }
        }
    }
}
Related Topic