LINQ-To-SQL and many-to-many relationships

Question

I have a many-to-many relationship between two tables, say, Friends and Food. If a friend likes food, I insert a row in the FriendsFoods table, for example:

ID Friend Food
1 'Tom' 'Pizza'

FriendsFoods has a primary key identifier and two nonzero foreign keys "Friend" and "Food" in the tables "Friends" and "Products", respectively.

Now suppose I have a Friend tom.NET object matching "Tom" and Tom no longer loves pizza (what's 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 SubmitChanges()in a DataContext, I get an exception because it tries to insert zero in the columns Friendand Foodin the table FriendsFoods.

I'm sure I can put together some kind of curved logic to track changes in the table FriendsFoods, intercept calls SubmitChanges(), etc., to try to get this to work the way I want, but is there a good, clean way to remove many-to-many relationships -many "with LINQ-To-SQL?

Answer 1

Assuming that the database itself has rules CASCADE DELETEdefined on foreign keys (otherwise you have problems), you need to set the Delete rule for each link in the Linq to SQL (or DeleteRulein AssociationAttribute) constructor to CASCADE.

- , tom, pizza.

: @Crispy , , , - DeleteOnNull , , , , , (a) CASCADE () . Linq to SQL FK CASCADE DELETE, DeleteOnNull = true , NULL.

Answer 2

many-to-many (FriendFoods), linq, DeleteOnNull , true dbml. , dbml-. .dbml XML , xml .

. LINQ to SQL?

Answer 3

CASCADE DELETE is not applicable here. This only applies if the object is deleted, either the Friend object or the Food object, but not if you just want to delete the relationship. Is there a reason why you are not directly accessing the relational table? I think that would be a much cleaner solution.