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>

    <CommandText>

        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

    </CommandText>

    <Parameter Name=ManagerID Type=uniqueidentifier />

</Function>


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 />

</FunctionImport>


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

    {

        get

        {

            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 *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>