EF: how to do a recursive include?

I have a table Employees with three columns: EmployeeID(uniqueidentifier, PK), Name (nvarchar) and ManagerID(uniqueidentifier, FK to EmployeeID).

I want to be able to get all the sub employees of an employee. 

As confirmed me Jeff, this scenario isn’t supported by EF v1.

With T-SQL, we can do this since SQL Server 2005 and the CTE as I said in my comment.

So my idea is to define myself the sql query in my edm.

For this, I add the following function in my SSDL:

<Function Name=GetSubEmployees IsComposable=false>


        WITH SubEmployees (EmployeeID, Name, ManagerID) AS


        SELECT e.EmployeeID, e.Name, e.ManagerID

        FROM Employees AS e

        WHERE e.ManagerID = @ManagerID

        UNION ALL

        SELECT e.EmployeeID, e.Name, e.ManagerID

        FROM Employees AS e

        INNER JOIN SubEmployees AS se ON e.ManagerID = se.EmployeeID


        SELECT EmployeeID, Name, ManagerID FROM SubEmployees


    <Parameter Name=ManagerID Type=uniqueidentifier />


Then I just need to import it into my CSDL. Note that it’s possible with the designer. The following function import will be added into the CSDL:

<FunctionImport Name=GetSubEmployees EntitySet=Employees ReturnType=Collection(TestCTEModel.Employees)>

    <Parameter Name=ManagerID Type=Guid />


with the following mapping in the MSL:

<FunctionImportMapping FunctionImportName=GetSubEmployees FunctionName=TestCTEModel.Store.GetSubEmployees />

I now have a method GetSubEmployees in my context.

Then, you can extend the Employees class (partial) to add the properties AllSubEmployees:

partial class Employees


    public IEnumerable<Employees> AllSubEmployees




            using (var context = new TestCTEEntities())


                foreach (var e in context.GetSubEmployees(Id))

                    yield return e;





This entry was posted in 7671, 7674, 7675. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *