Eager load self referencing entity?

Consider an Org Chart Model where you have a single Entity "Employee".  Employees have a supervisor (also an employee) and a set of subordinates (also employees).  If this is modeled as a self referencing entity. (ie Employee has a Subordinates property containing a collection of Employees and a Supervisor property of type Employee), does LightSpeed support a configuration where the entire hierarchy of Employees can be loaded in an Eager fashion.

This seems to work in a lazy fashion, but not in an eager fashion in my sample.

We don't currently support this because most databases don't provide a mechanism for it.  With a normal eager load we can predict the SQL statements that need to be executed because we're just using subqueries, e.g. (pseudo code):

SELECT ... FROM Orders WHERE ...
SELECT ... FROM OrderDetails WHERE OrderId IN (SELECT Id FROM Orders WHERE ...)
SELECT ... FROM Customers WHERE OrderId IN (SELECT Id FROM Orders WHERE ...)

But with a hierarchy load we would need to issue an unknown depth of SQL statements:

SELECT ... FROM Employees WHERE ...
-- to get immediate subordinates
SELECT ... FROM Employees WHERE SupervisorId IN (SELECT Id FROM Employees WHERE ...)
-- to get the subordinates of the subordinate
SELECT ... FROM Employees WHERE SupervisorId IN (SELECT Id FROM Employees WHERE SupervisorId IN (SELECT Id FROM Employees WHERE ...))
-- etc.

So this is only possible with special language support such as that offered by SQL Server and Oracle, and we don't support that yet.  I have logged a feature request for this but we are unlikely to look at it before LightSpeed 3.

We do appear to have a bug where if you mark a self-association as eager load, it not only fails to eager load but also breaks lazy load.  I have logged a bug for this.

Ivan,

That's pretty much what I had assumed.  Its great to know that you guys are considering this for a future release.  I think for many 2 tier architecture scenarios, lazy loading of child elements would work great.  Where the need starts to show is on 'application server' environments where you want to preemptively load the entire object graph. 

Thanks again for your answer and explanation!  Your responsiveness and support is a real asset to the product.

Hi,

Just wondering if this feature to eager load a self referencing entity has been implemented in LightSpeed 4.

Cheers Karl

Hi Karl,

No this is currently still on our backlog. Since its a provider specific enhancement and hasn't had much prior interest it is fairly low priority but we would love to hear from anyone else who is interested in this.

Thanks!
Jeremy

I am currently evaluating LightSpeed as an alternative to Linq2Sql primarily because of this self recursion issue. It is taking a long time in our application to load the recursive object graph via lazy loading. This is why I am looking at alternative ORM products. The way we have our database set up, we have two different places where we are using self-referencing entities. If LightSpeed could provide this feature for SQL Server 2008, then I would certainly go with your product. What do you think is the likelihood of this being implemented in the near future?

Thanks for the interest in this, at this stage this is still a low priority so currently we are not planning to implement this in the near future.

Jeremy