Self Referencing entity with Table per Hierarchy

2. Modeling Entities

2.1 Self Referencing Table

2.1.2 Self Referencing entity with Table per Hierarchy

&& cat.Medias.Count() > 0 select cat;

foreach (var cat in cats) {

Console.WriteLine("Name:{0} Articles:{1} Videos:{2}", cat.Name,

cat.Medias.OfType<Article>().Count(),cat.Medias.OfType<Video>().Count());

}

On the code above I am retrieving the top level category by checking if parent category is null. If the parent or root category is null, the category is a root level category. I am also filtering the categories to ones that have some kind media associated with it. Along with Category I am also retrieving the Media association by using Include operator. Since Media is comprised of Article and Video entity, the collection may contain both types of Media. To explicitly find the articles and videos in a given category, I am using OfType operator. Figure below shows the output on the console window.

2.1.2 Self Referencing entity with Table per Hierarchy

Problem: Figure below shows the database diagram for Employee table that contain Employees with different role.

The above employee table contains Employee with different roles. An

employee could be President, Manager, Supervisor or a SalesAgent identified by the EmployeeType column.Each Employee reports to an employee

identified by ReportTo EmployeeId; for instance a sales agent would report to a Supervisor and a supervisor would report to Manager and Manager would report to the President. You want to import the above table schema using Table per Hierarchy and each Employee should have a navigation property ReportsTo that tells which Employee, the employee reports to. Completed entity data model should look as follows.

Solution:

Discussion:

Steps below outline the process of importing Employee table as a self referencing entity with Table Per Hiearachy for each type of Employee defined by Employee Type.

1. Import Employee table into EDM wizard. Figure below shows the model created by the wizard.

Change the name of Employee1 on the figure to Employees as it is the Many side of the relationship and change Employee2 to ReportsTo.

Also change the names of the roles for the self referencing association created by the designer. Call Many side of the role as Employees and 1 side of the role as ReportTo. Figure below shows the name for the association.

Make Employee entity abstract as we will not create an instance of Employee entity directly and will only serve as a base class. Also remove EmployeeType property because EmployeeType column will be used as a discriminator column for inheritance.

2. Create SalesAgent entity deriving from Employee entity. Move commission column from Employee entity to SalesAgent entity and map Commission property to Commission column in Employee table.

Set the condition for EmployeeType to SalesAgent.

Figure below shows the mapping for SalesAgent entity.

3. Create SalariedEmployee and move Salary property from Employee entity to SalariedEmployee entity. Make salaried Employee abstract since it will serve as base class for President, Manager and Supervisor entity. Additionally it allows us to declare Salary property once and use it in all derived types. However when it comes to mapping Salary to the Salary column in the database, it has to be done for each entity deriving from Salaried Employee.

4. Create Manager Entity deriving from SalariedEmployee. Set the table mapping to Employee table and condition where EmployeeType equal to Manager. You won’t be able to map properties to columns using the designer because we added SalariedEmployee entity that does not map to any table. In later steps we will edit the edmx in xml and map the properties to columns manually.

5. Create President and Supervisor entity deriving from

SalariedEmployee and map both entities to Employee table. For President set EmployeeType condition to President and condition of Supervisor for Supervisor entity.

6. Open up edmx file in xml and configure property mappings for EmployeeId and Salary column for all entities deriving from SalariedEmployee. Code below shows the mapping.

<ScalarProperty Name="EmployeeID"

ColumnName="EmployeeID" />

</MappingFragment>

</EntityTypeMapping>

<ScalarProperty Name="EmployeeID"

ColumnName="EmployeeID" />

<Condition ColumnName="EmployeeType"

Value="President" />

</MappingFragment>

</EntityTypeMapping>

<ScalarProperty Name="EmployeeID"

ColumnName="EmployeeID" />

<Condition ColumnName="EmployeeType"

Value="Supervisor" />

</MappingFragment>

</EntityTypeMapping>

On the code below, I am mapping EmployeeId and Salary property to each entity deriving from SalariedEmployee. The reason we are doing it individually is because SalariedEmployee only serves as abstract class on our conceptual model and does not have any table mapping on the database.

To test the model, we can retrieve our top level employee which is the president. An immediate child for the president would be manager, which will have children of supervisor and that will have SalesAgents working under him. Code below shows how we retrieve four level deep hierarchies.

var db = new SFTPHEntities();

var president = db.Employees

.Include("Employees.Employees.Employees")

.Where(e => e.ReportsTo == null).First();

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

var manager = president.Employees.First();

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

var supervisor = manager.Employees.First();

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

foreach (var agent in supervisor.Employees) {

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.

In document Entity Framework Learning Guide (Page 41-47)