Entity Framework 6 Make relation on behalf of ID or enum value
.net c# entity-framework entity-framework-6 sql-server
Question
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
- ID
- Name
- RoleEnum (CEO, Leader, Developer)
- Virtual list of Advantages
Advantages
- ID
- Description
EmployeeAdvantages
- ID
- AdvantagesID
- EmployeeID (Nullable) *
- RoleEnum (Nullable) *
*) 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?
Accepted Answer
It is better from a relational modelling perspective that you separate Advantages of Employees from Advantages of Roles.
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 Advantages from both collections.
Popular Answer
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
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow