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
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.
2