LINQ-To-SQL and Many-To-Many Relationship Deletions

Question

I have a many-to-many relationship between two tables, let's say Friends and Foods. If a friend likes a food I stick a row into the FriendsFoods table, like this:

ID Friend Food
1 'Tom' 'Pizza'

FriendsFoods has a Primary Key 'ID', and two non-null foreign keys 'Friend' and 'Food' to the 'Friends' and 'Foods' tables, respectively.

Now suppose I have a Friend tom .NET object corresponding to 'Tom', and Tom no longer likes pizza (what is wrong with him?)

FriendsFoods ff = tom.FriendsFoods.Where(x => x.Food.Name == 'Pizza').Single();
tom.FriendsFoods.Remove(ff);
pizza.FriendsFoods.Remove(ff);

If I try to SubmitChanges() on the DataContext, I get an exception because it attempts to insert a null into the Friend and Food columns in the FriendsFoods table.

I'm sure I can put together some kind of convoluted logic to track changes to the FriendsFoods table, intercept SubmitChanges() calls, etc to try and get this to work the way I want, but is there a nice, clean way to remove a Many-To-Many relationship with LINQ-To-SQL?

Answer 1

Assuming the database itself has CASCADE DELETE rules defined on the foreign keys (otherwise you're in trouble), you need to set the Delete Rule on each relationship in the Linq to SQL designer (or the DeleteRule in the AssociationAttribute) to CASCADE.

You also don't need to delete both sides of the association - one will suffice, either from tom or from pizza.

Addendum: @Crispy is also correct in that the real attribute you care about here is the DeleteOnNull attribute in the child association. However, the reason that you can't see this in the designer is that the designer auto-detects the value of this property based on (a) the CASCADE setting of the parent-to-child association, and (b) whether or not the foreign key field is nullable. If you have a non-nullable FK with a CASCADE DELETE in the Linq to SQL designer, it should automatically set DeleteOnNull = true for the child-to-parent association and subsequently delete the record from the mapping table instead of attempting to set its associations to NULL.