Linq to Entities - Retrieve entity with "self-reference" from Stored Procedure

Question

I can't quite figure this out. I have a table called Employee with and Id field. The table also contains a ManagerId field which has a foreign key pointing to the Employee's Id field. When I add the table as an entity to my entity data model it creates the new Employee entity with an EmployeeChildren collection element and an EmployeeParent element. I can retrieve all employee's and have them put into a new instance of the Employee entity perfect. The employee entity will have many children, each child entity can have many children of their own and each one has a pointer back to it's parent.

What I need to do now is retrieve a subset of those employee's using a stored procedure. Right now, if I search for employee John Doe, who has 2 people above him, the stored procedure will return 3 rows.

EmployeeID ManagerId Name

1 null Bill

2 1 Jane

3 2 John Doe

Here is my code to do the retrieval:

using (var entity = new TimeEntryEntities())
    {

         var employees =
                 from E in entity.EmployeeSearch(search)
                 orderby E.Name
                 select E;


         return employees.ToList<Employee>();
    }

Right now, this code returns 3 separate entities. How can I have it group them together into one?

Answer 1

It sound like you are trying to flatten out the hierarchy so only one record is returned. Maybe Common Table Expression (CTE) in the stored procedure can help: Here is an alternate of a similar request:

Also another example with more detail (but you have to register for free):

Answer 2

You should return only the entity you're interested in. EF will retrieve the related entities automatically for you. This would be easier to visualize if you we're not using a self-reference, this concept has got in the way.