One table -> two entity types, relationship management

With my two last posts (ssdl view and TPT and ssdl view and TPH), I show you how to have two entity types mapped on one single table without discriminator column.

Now, next step. In my table Employees, I have a FK between Employees.EmployeeManager and Employees.EmployeeId. When I create my EDM from my DB, in my edm, I have a relationship between Employee entity type and itself.

In my CDSL, I add business logic with Manager and Employee distinction and I now want a relationship between Employee and Manager.

For this, I delete my csdl relationship, then my ssdl relationship (only possible by editing edmx xml myself). If I don’t I will have an edmx error. If I delete the csdl relationship without edm designer (so by editing edmx xml), I must also delete the msl part of the relationship.

Then I add a relationship between Employee and Manager and I map it on Employees table.

Great it’s ok! [:)]

Now more difficult. If an employee become a manager, how to do this? So in fact, how to add some sub-employees to an employee?

Following code allow us to do this:

var manager = new Manager { EmployeeId = oldEmployee.EmployeeId, EmployeeManager = oldEmployee.EmployeeManager, EmployeeName = oldEmployee.EmployeeName };

ObjectStateEntry ose;

context.ObjectStateManager.TryGetObjectStateEntry(manager, out ose);

if (ose == null)




manager.SubEmployees.Add(new Employee { EmployeeId = newEmployeeId, EmployeeName = newEmployeeName });


Great ! Now I will explain it.

When we set the manager EmployeeManager property, this action adds the manager entity to the context of oldEmployee.

But if the oldEmployee context isn’t our context instance or if oldEmployee.EmployeeManager is null, we must add the new manager to our context instance.

That’s why I use the TryGetObjectStateEntry test.

Then, I must detach oldEmployee because I will have some conflicts else.

Now, we have a manager instance so we can add some sub-employees. In DB, nothing should change for this record but if we do a SaveChanges, we will have a problem because manager instance has Added state. That’s why I use AcceptAllChanges method.

Then, we can add new sub-employees and after this, we just need to save new employees into DB.

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 *