LINQ-to-SQL和多对多关系删除 [英] LINQ-To-SQL and Many-To-Many Relationship Deletions

问题描述

我在两个桌子之间有一个多对多的关系，比如说Friends and Foods.如果朋友喜欢食物，我可以在FriendsFoods表中添加一行，如下所示:

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具有一个主键"ID"，并且分别在"Friends"和"Foods"表中具有两个非空外键"Friend"和"Food".

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

现在假设我有一个与'Tom'对应的Friend tom .NET对象，而Tom不再喜欢披萨(他怎么了?)

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);

如果我尝试在DataContext上使用SubmitChanges()，则会出现异常，因为它试图在FriendsFoods表的Friend和Food列中插入空值.

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.

我确定我可以组合使用某种复杂的逻辑来跟踪对FriendsFoods表的更改，拦截SubmitChanges()调用等，以使其按我想要的方式工作，但是有一个不错的选择干净的方法来删除与LINQ-To-SQL的多对多关系?

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?

推荐答案

假定数据库本身在外键上定义了CASCADE DELETE规则(否则会遇到麻烦)，则需要设置 Delete Rule 到Linq to SQL设计器中的每个关系(或AssociationAttribute中的DeleteRule)到CASCADE.

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.

您也不需要删除关联的两面-只需从tom或pizza即可.

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

附录: @Crispy 也是正确的，因为您在这里关心的 real 属性是子关联中的DeleteOnNull属性. 但是，您在设计器中看不到的原因是，设计器基于(a)父级到子级的CASCADE设置自动检测此属性的值(b)外键字段是否可为空.如果您在Linq to SQL设计器中有一个带有CASCADE DELETE的不可为空的FK，它应该自动为子对父关联设置DeleteOnNull = true，然后从映射表中删除该记录，而不是尝试设置其记录.与NULL的关联.

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.

这篇关于LINQ-to-SQL和多对多关系删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！