LINQ-To-SQL and Mapping Table Deletions

Posted by Jake on Stack Overflow See other posts from Stack Overflow or by Jake
Published on 2010-04-02T20:47:17Z Indexed on 2010/04/02 20:53 UTC
Read the original article Hit count: 382

Filed under:
|

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 = tblFriendsFoods.Where(x => x.Friend.Name == 'Tom' && 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?

© Stack Overflow or respective owner

Related posts about c#

Related posts about linq-to-sql