none
delete related records RRS feed

  • Question

  • Hello !

    i'm working with entity framework / sql server 2008 , and Visual Basic 2010 and i have a problem :

    i have 2 tables:

    Table 1:                   Table 2 :

    id1                           id2

    name                      value1

    age                         value2

                                    id1

    there is a relationship 1-to-many bettween table1 and table2. I want to delete all records on table2 related with a specific record on table1 ( for example id1=5) , and after i want to delete the record on table1.

    i try this code to delete the records on table2:

    ------------------------------------------------------------------

    dmo = (From t In context.table1 Where t.id1 = 5

     Select t).FirstOrDefault()

    dmo.table2.Clear()

    context.SaveChanges()

    -------------------------------------------------------

    .... but i get this error

    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.

    ---------------------------------------------------

    what can i do ?

    thank you .

    Tuesday, October 25, 2011 6:32 PM

Answers

  • Hello,

    this will not work in common scenario. Clear breaks the relation but doesn't delete dependent records - it just sets null to their foreign key and it si not supported by your model (your multiplicity constraint demands that dependent entity has non null FK). EF doesn't work this way. You must either correctly configure cascade delete in both database and EDMX (code first uses it by default) and after that you don't need to deal with table2 records at all or you must manually call Remove / DeleteObject for all table2 records related to table1 and for table1 record itself. The last option is modifying your model to support identifying relation and in such case the Clear should work as you expect.

    Best regards,
    Ladislav

    Wednesday, October 26, 2011 9:13 AM