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.

Answer 2

If you're trying to delete the entry from the many-to-many table (FriendFoods) by removing it form the child collection of the linq entity, you'll need to set the DeleteOnNull attribute on the relationship equal to true in the dbml file. Unfortunately, you can't do it via the dbml designer. You have to open the .dbml file as an xml file and then manually edit the xml of the association.

See http://stackoverflow.com/questions/900919/how-do-i-delete-records-from-a-child-collection-in-linq-to-sql

Answer 3

CASCADE DELETE does not apply here. It would only apply in the event that an entity is removed, either the Friend object or the Food object, but not if you simply want to delete the relationship. Is there any reason you are not accessing the relational table directly? I think that would be a much cleaner solution.