locked
Trying to clear a table RRS feed

  • Question

  • i have 3 tables: [Employee]<=[EmployeeSkill]=>[Skill]

    What i am trying to do is delete ALL the data from the [EmployeeSkill] table for one employee.

    MMSEntities

     

     

    context = new MMSEntities();
    Employee employee = context.Employees.Single(e => e.EmployeeID == employeeID);
    employee.EmployeeSkills.Clear();
    context.SaveChanges();

    But it gives the follow error message:
    The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

    I dont understand what this error message is telling me, or what i am doing wrong.

    Does anyone have any suggestions?

    Wednesday, December 16, 2009 4:06 AM

Answers

  • Hello,

    In Entity Framework in general, removing an element from a collection (i.e. EmployeeSkill from the Employee.EmployeeSkills collection) doesn't necessarily imply deleting that element. Instead, it usually imply just removing the relationship between a principal entity (in this case the Employee) and a dependent entity (in this case each EmployeeSkill). Only if you have an "identifying relationship" between the two, you will get the dependent automatically deleted when removed from the collection.

    An identifying relationship exists when the foreign key on de dependent is part of the primary key of the dependent. In this case, if EmployeeSkill had a primary key composed of EmployeeID and SkillID (assuming that those are the names of the properties), you would have an indentifying relationship and you would see the behavior you expect.

    If you still want to have a different key for EmployeeSkill (i.e. you don't like composite keys for some reason) you can still make this work, but you need to explicitly delete the dependents:

    foreach(var empSkill in employee.EmployeeSkills.AsArray())
    {
        context.DeleteObject(empSkill);


    Another reason you might actually want to have a key composed of EmployeeID and SkillID for EmployeeSkills is because this way you would make sure no duplicates (with the same skill and employee) could be inserted on that entity set.

    Hope this helps,
    Diego
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 29, 2009 7:31 AM
    Moderator

All replies

  • something in you db defintion is nor correct.

    the key from empolyee to skill should be able to get a null value.

     

    Wednesday, December 16, 2009 7:33 AM
  • The reason there is an EmployeeSkill table in the middle is so that a many to many relationship can exist between Employee and Skill. This means that there is no need for any null values anywhere in the model.

    However i took the advice of the error message and made the FK's in EmployeeSkill nullable, thinking that it must need to nullify them before deleting the row.

    However the same error message still came back at me.

    I did a google search for the message, and apparently only one other person in the world has ever got this same error message.

    I am using .NET4 beta, do you think that it might be a beta bug?
    Wednesday, December 16, 2009 9:22 PM
  • Hello,

    In Entity Framework in general, removing an element from a collection (i.e. EmployeeSkill from the Employee.EmployeeSkills collection) doesn't necessarily imply deleting that element. Instead, it usually imply just removing the relationship between a principal entity (in this case the Employee) and a dependent entity (in this case each EmployeeSkill). Only if you have an "identifying relationship" between the two, you will get the dependent automatically deleted when removed from the collection.

    An identifying relationship exists when the foreign key on de dependent is part of the primary key of the dependent. In this case, if EmployeeSkill had a primary key composed of EmployeeID and SkillID (assuming that those are the names of the properties), you would have an indentifying relationship and you would see the behavior you expect.

    If you still want to have a different key for EmployeeSkill (i.e. you don't like composite keys for some reason) you can still make this work, but you need to explicitly delete the dependents:

    foreach(var empSkill in employee.EmployeeSkills.AsArray())
    {
        context.DeleteObject(empSkill);


    Another reason you might actually want to have a key composed of EmployeeID and SkillID for EmployeeSkills is because this way you would make sure no duplicates (with the same skill and employee) could be inserted on that entity set.

    Hope this helps,
    Diego
    This posting is provided "AS IS" with no warranties, and confers no rights.
    Tuesday, December 29, 2009 7:31 AM
    Moderator
  • Thank you Diego for that awesome explanation! +1
    Friday, June 4, 2010 10:14 PM