Using Common CTE with Self Referencing Entity

Console.WriteLine(" Agent:{0} Type:{1}", agent.Name, agent.GetType().Name);

}

On the code above, I have included 3 includes for Employees. First include for employee will return the manager reporting to the president.

Second Include will return supervisor reporting to the manager and third include would return agents reporting to the supervisor. The query returns the top level employee by checking to see if the employee has no one to report as president does not report to anyone. Next I retrieve the manager for the president and prints it information to the console window. Then for the manager entity, I retrieve its supervisor followed by agents for the supervisors. The screenshot below shows the results printed on the console window.

2.1.3 Using Common CTE with Self Referencing Entity

Problem: Figure below shows the conceptual model we created earlier in problem 3.1.3 with slight modifications.

To recap the above model, an Employee can be a SalesAgent, Manager, Supervisor or a President. Each employee except the President reports to an employee above it which can be identified by ReportsTo navigation property.

In business terms a SalesAgent reports to a Supervisor, a Supervisor reports to Manager and a Manager ReportsTo the president. Given an instance of

president entity, you want to return all the employees that directly or indirectly report to the president. This requires that we traverse an infinite dept for the ReportsTo relationship. The end result should be a collection of Employees containing SalesAgent, Manager, Supervisor and President.

Solution: EF does not support recursive queries that can return all employees that indirectly reports to the President. The ReportsTo navigation property only returns the immediate employees that report to presidents which are Managers. If we want our result to also include employees that report to Manager and so on, we need to make use of Common Table Expressions.

CTE is a feature introduced in Sql server 2005 that allows recursive queries

until you reach to the end of the list. To use CTE, we need to create a stored procedure on the database that takes an EmployeeId and returns all employees that directly and indirectly reports to the employeeid passed in. The stored procedure then needs to be imported into the store model. To use the stored procedure inside our ObjectLayer, we need to use FunctionImport to import the stored procedure into the conceptual model and set the return type to Employee entity. Since the employees returned by the stored procedure would contain different types of employees ranging from Manager, Supervisor and SalesAgent, we need to map each row returned by the stored procedure to appropriate derived entity based on the EmployeeType column. After setting up the mapping, we can call the method created on the ObjectContext that takes in the EmployeeId and returns a list of Employees that directly or indirectly reports to the that employee.

Discussion: Steps below outline the process of returning recursive queries for a self referencing entity.

1. To return all employees that directly or indirectly reports to a Employee we need to create a function on the store model that uses Common Table Expression to recursively go through each Employee’s ReportsTo column and find its ReportsTo until we reach to the last record in the list. Code below shows the function we need to define on the store model.

from emps join sr.Employee e2 on emps.EmployeeId = e2.ReportsTo

)

select * from emps where employeeid != @EmployeeId

</CommandText>

<Parameter Name="EmployeeId" Type="int" Mode="In" />

</Function>

We could have created a stored procedure and referenced the stored procedure inside of the function but for demo purposes, it is easier to declare the sql

inline using CommandText option. Beware if you use this technique, your function will get overwritten the next time you try to update the model from the database.

2. Import the stored procedure on the store model into the conceptual model by using Function Import option which can be accessed by right clicking the stored procedure on the model browser and choose Create Function Import. On the dialog, set the return type for the method to Employee entity. Figure below shows the values inputted on the dialog.

3. Since stored procedure returns various derived types of Employees, we need to map each record returned from the stored procedure to its appropriate derived typed. Mapping a derived type to a result returned from the stored procedure cannot be accomplished using the designer.

To perform mapping open the edmx file in xml and find the section for the function import and modify the function import as follows.

<FunctionImportMapping FunctionImportName="GetSubEmployees"

FunctionName="EcommerceModel.Store.GetSubEmployees">

<ResultMapping>

<EntityTypeMapping TypeName="SelfReferencing.SalesAgent">

<Condition ColumnName="EmployeeType"

Value="SalesAgent"/>

</EntityTypeMapping>

<EntityTypeMapping

</FunctionImportMapping>

On the above code, based on the value on the EmployeeType returning I am mapping the record to a particular derived entity. For instance if EmployeeType has a value of SalesAgent, then we need to create an instance of SalesAgent entity.

Code below test the above recursive method we have created on the ObjectContext.

var db = new SFCTEPHEntities();

var president = db.Employees.First(e => e.ReportsTo == null);

var emps = db.GetSubEmployees(president.EmployeeID);

Console.WriteLine("All Employees working under president\r\n");

foreach (var emp in emps) {

Console.WriteLine("Name:{0} Type:{1}", emp.Name, emp.GetType().Name);

}

First I am getting the root Employee, president and using its

EmployeeId I call GetSubEmployees to get all the employees that work below him. For each Employee that works below him, I am printing Employee Name and the type of Employee using GetType. Since we have configured the entity Type mapping for stored procedure, we will observe from the results on the console window that results would include SalesAgent, Manager and Supervisor. Figure below shows the result on the console window.

Note: In V1 of EF, stored procedure mapping does not support base type that is marked abstract. To overcome this limitation like we did in our case, ensure that base entity Employee is not marked abstract and map it to a dummy discriminator value for EmployeeType that is not defined on the database.