I am working in an environment where we have quite a few aspects of the code and data dictated. As such, I need to find a way to map from the database location to the entities without modifying either the data nor the shape of the entities.
The data will be stored across the entities in an hierarchy, but need to be accessible at any child position within the hierarchy. The hierarchy is a typical Parent Child self-reference and the data is on a One-To-Many with the main entity.
I have mocked up a version of this to give some context:
First is the main entity, this has a parent (Manager) and children (Reports), linked using the ManagerId property that is optional (and null at the top-most node):
public class Person
{
[Key]
public int Id { get; set; }
public int? ManagerId { get; set; }
public string Name { get; set; }
public virtual ICollection<Device> Devices { get; set; }
public virtual Person Manager { get; set; }
public virtual ICollection<Person> Reports { get; set; }
public override string ToString() {
return Name;
}
}
This entity references some data represented in the Devices entity:
public class Device
{
[Key]
public int Id { get; set; }
public int HolderId { get; set; }
public string Name { get; set; }
public virtual Person Holder { get; set; }
public override string ToString() {
return Name;
}
}
However the challenge here is when I query for the Person, I need to include in the Devices collection not only the devices that reference the Person, but also the devices that reference their Manager, recursively. I can do this with SQL using a recursive CTE:
WITH DeviceCte AS (
SELECT D.*, P.ManagerId AS ManagerId, P.Id AS JoinId FROM People P LEFT JOIN Devices D ON P.Id = D.HolderId
UNION ALL
SELECT D.*, P.ManagerId AS ManagerId, C.JoinId AS JoinId FROM People P JOIN cte C ON P.Id = C.ManagerId OUTER APPLY (
SELECT * FROM Devices WHERE D.HolderId = P.Id)
)
SELECT P.*, D.Id, D.HolderId, D.Name
FROM People P
LEFT JOIN DeviceCte D ON P.Id = D.JoinId
The question is how do I get Entity Framework to build this query based on this data structure. As well as being restricted in the way data is held in the database, I am also restricted in that the code should be using Code-First migration.
I have put together a fiddle for this: https://dotnetfiddle.net/pbfBc1