Came across YET another gotcha with the EF designer. After updating my model i got the following 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.
Turns out the designer had not picked up the cascading delete triggers on the foreign key relationships. To fix it you have to go to the properties of each FK relationship and set the end1 action to cascade. No doubt this will get overwritten everytime i update the model …
Thanks to Good-Ol Stack Overflow for setting me in the right direction. Is there anything SO cant do?