The classic exmaple of "show Bill and all the employees that report to hiM" is answered easy enough with T-SQL and a recursive CTE such as:
with cte as (
select *, 0 as level from employees where username = '[email protected]'
union all
select employees.*, level+1 from employees
join cte on employees.reportsTo = cte.id
)
select * from cte;
I understand that Linq to Entities does not offer such a recursive query, so I wonder what my alternatives are.
Using EF 4, is it possible to connect Linq to Entites to a UDF or stored procedure that would return employee instances as defined in the entity model? Or perhaps there's an approach that uses Entity Sql?