Removing Entities from Database table via Navigation Property using RIA Services and Entity Framework

Question

I have 3 normalised tables consisting of Employees, Departments and EmployeesToDepartments. I wish to be able to assign an Employee to one or more Department, hence the link table (EmployeesToDepartments). I can successfully query the database and extract the full hierarchy of entities via the Navigation properties using

this.ObjectContext.Employees.Include("EmployeesToDepartments").Include("EmployeesToDepartments.Department")

plus the [Include] attribute in the metadata, thus allowing me to access the Departments for a given Employee. Upon trying to remove a link between an [Employee] and [Department] in the [EmployeesToDepartments] table I was given a Foreign Key Constrain error.

I have simplified my model to include just one navigation property between [Employees] and [EmployeesToDepartments]. A Foreign Key constraint between[Employees].[ID] and [EmployeesToDepartments].[IDEmployee] was preventing me from updating the EmployeesToDepartments table. With this removed via a Relationship setting I can now update the table. I can now execute the following code

foreach (var rel in _employee.EmployeesToDepartments)
{
    _employee.EmployeesToDepartments.Remove(rel);
}

_domainContext.SubmitChanges();

without error.

I was expecting to see the entries in the RelEmployeesToDepartments with the IDEmployee to have been deleted. What I see in the table are the value 0 where the IDEmployee previously was.

Is it possible to force a DELETE statement to be issued? Am I misunderstanding the basic concepts here?

Any help would be much appreciated.

Answer 1

Removing entities in navigation property only breaks the link between entities. You have to delete from the EntitySet to achive what you want.
ex) myDomainContext.EmployeeDepartments.Remove(employeeDepartmentToRemove); myDomainContext.SubmitChanges();