In my DB I have a relation between some tables that is relying on either an ID or an Enum value. Here is the structure:
Employee
Advantages
EmployeeAdvantages
*) only one of them will have a value for each row
The relation between Employee and Advantages can be either based on a direct relation based on EmployeeID and AdvantagesID or based on the RoleEnum.
So when I load a Employee with the EF then I would like the virtual list of advantages to be filled automatic, And this behaviour is also working when the relation is based on EmployeeID and AdvantagesID, BUT when it is based on RoleEnum it is of course not working out of the box, but isn't it possible to configure EF to handle that situation? So if the employee has a role of "CEO" it will load all the relations that is based on that in role in EmployeeAdvantages?
It is better from a relational modelling perspective that you separate Advantage
s of Employee
s from Advantage
s of Role
s.
It is better to have a separate table like RoleAdvantages
This way you can make a relationship between Employee
and RoleAdvantage
based on the role of the employee and thus automatically load Advantages
of that Role
. You only then have to collect Advantage
s from both collections.
As far as I know, what you want is not possible. But you could create an extension for Employee where you manually load the EmployeeAdvantages that come through the RoleEnum. Basically you would do this in your extension method:
public static class EmployeeExtension
{
public static int VirtualListOfAdvantages(this Employee employee)
{
var repo = new EmployeeAdvantagesRepository();
var enumRoleAdvantages =
repo.GetAll().Where(ea => ea.RoleEnum == employee.RoleEnum).ToList();
enumRoleAdvantages.AddRange(employee.VirtualListOfAdvantages);
return enumRoleAdvantages;
}
}
(I don't know how your repository would look like, but I think you get the idea)
This is certainly not the solution you were looking for, but it's a valid and quite simple solution that should work well.
More about extension methods (and example taken from): How to: Implement and Call a Custom Extension Method