C# LINQ-to-SQL + 一对多 + DataBinding 删除

LINQ-to-SQL + One-to-Many + DataBinding deleting

提问by Jurij

I use LINQ-to-SQL to load data from a database that has two tables in a one-to-many relationship (one Recipe has many Ingredients).

我使用 LINQ-to-SQL 从具有一对多关系的两个表（一个配方有多个成分）的数据库加载数据。

I load a Recipe and LINQ retrieves Ingredient objects into an EntitySet that is binded into a ListBox.

我加载了一个 Recipe，LINQ 将 Ingredient 对象检索到绑定到 ListBox 的 EntitySet 中。

If I want to delete some Ingredients off a Recipe, I get a "An attempt was made to remove a relationship between a Recipe and a Ingredient. However, one of the relationship's foreign keys (Ingredient.RecipeID) cannot be set to null.

如果我想从配方中删除一些成分，我会收到“尝试删除配方和成分之间的关​​系。但是，关系的外键之一 (Ingredient.RecipeID) 不能设置为 null。

I SOLVED this problem using the well known solution by adding 'DeleteOnNull="true"' to the DBML file. But adding this setting only removes the problem when we are deleting Ingredient objects that were retrieved from the DB.

我使用众所周知的解决方案解决了这个问题，方法是在 DBML 文件中添加“DeleteOnNull="true"”。但是添加此设置只会在我们删除从数据库中检索到的 Ingredient 对象时消除问题。

The problem is with the Ingredient objects that were created in code (added to a Recipe) and added to the EntitySet collection of Ingredients and then deleted BEFORE SubmitUpdates is called. Then, the same exception happens again. This usually happens on a new, unsaved recipe when user is adding ingredients to it, makes a mistake and erases an ingredient off a recipe. I added the DeleteOnNull to both 'Association Name="Recipe_Ingredient"' lines in DBML.

问题在于在代码中创建的成分对象（添加到配方中）并添加到成分的 EntitySet 集合中，然后在调用 SubmitUpdates 之前删除。然后，同样的异常再次发生。这通常发生在一个新的、未保存的食谱上，当用户向其中添加成分、出错并从食谱中删除成分时。我将 DeleteOnNull 添加到 DBML 中的两行“Association Name="Recipe_Ingredient"”。

How am I supposed to remove such objects? The only solution I see at the moment is that I would load the ingredients into a collection not under the DataContext and then when saving, delete all ingredients off a recipe and add then again from that cache..

我应该如何删除这些对象？我目前看到的唯一解决方案是将成分加载到不在 DataContext 下的集合中，然后在保存时，从配方中删除所有成分，然后从该缓存中再次添加..

回答by Andrew Theken

you need to decouple the save code from the events in your GUI, it seems like you're a little to eager to save things to the db before the dust has settled and you're queuing and removing things from the db that never got there in the first place, it would be best if you could identify a point when the user will "commit" their changes, and at that moment, process the full condition of the GUI - this will save you a bunch of spaghetti code.

您需要将保存代码与 GUI 中的事件分离，似乎您有点急于在尘埃落定之前将内容保存到数据库中，并且您正在排队并从数据库中删除从未到达那里的内容首先，最好能确定用户“提交”更改的时间点，并在那时处理 GUI 的完整条件 - 这将为您节省一堆意大利面条式代码。

I would also be curious to know if your entities have autonumber IDs or if you're using some other ID mechanism. You're probably sending DELETEs to the database for the as-yet-uncommitted Ingredient records, if those include NULL IDs, I think the linq could get nasty.

我也很想知道您的实体是否具有自动编号 ID，或者您是否正在使用其他一些 ID 机制。您可能正在向数据库发送 DELETE 以获取尚未提交的成分记录，如果这些记录包含 NULL ID，我认为 linq 可能会变得令人讨厌。

Have you hooked up a textwriter to your DataContext.Log to see what sorts of SQL is generated just before you get your exeception?

您是否已将文本编写器连接到您的 DataContext.Log 以查看在您获得异常之前生成的 SQL 类型？

回答by mjwills

        try
        {
            // Needed for existing records, but will fail for new records
            yourLINQDataContext.Ingredients.DeleteOnSubmit(ingredient);
        }
        catch (Exception)
        {
            // Swallow
        }

        yourRecipeObject.Ingredients.Remove(ingredient);

回答by Greg D

It seems that you're looking for something that I was looking for myself just a few days back when I asked "How do I design backing data types for a databound WPF dialog with Ok/Cancel buttons?".

几天前，当我问“如何为具有确定/取消按钮的数据绑定 WPF 对话框设计支持数据类型？”时，您似乎正在寻找我自己正在寻找的东西。

The answer is an intriguing post from Paul Stovell describing a sample IEditable adapterfor Linq to Sql. This will let you create your desired "Apply/Cancel" semantics in a generalized manner without completely dissociating yourself from the underlying ORm-generated classes through a full custom-written layer.

答案是 Paul Stovell 的一篇有趣的文章，它描述了一个用于 Linq 到 Sql的示例 IEditable 适配器。这将使您能够以通用的方式创建所需的“应用/取消”语义，而无需通过完整的自定义编写层将自己与底层 ORm 生成的类完全分离。

It's a pretty slick trick, overall, that will essentially let you sidestep the problems you're fighting right now. :)

总的来说，这是一个非常巧妙的技巧，基本上可以让你避开你现在正在解决的问题。:)

On a different note, I'm curious as to why your recipe to ingredient relationship is 1:n instead of m:n. Is it for simplicity's sake? I use garlic in a lot of recipes. :)

另一方面，我很好奇为什么你的配方与成分的关系是 1:n 而不是 m:n。是为了简单吗？我在很多食谱中都使用大蒜。:)

回答by Jurij

Thank you for your answer, I will examine the posts and see what I can do. I must say I'm surprised to even see this problem occuring, it seems quite natural to me that one could add records to the LINQ-provided "cache" of data, then decide to erase some of them and then commit. Change tracking should be able to handle that. I just starting with LINQ so I might be doing a stupid mistake somewhere in the code (wouldn't be the first).

谢谢你的回答，我会检查帖子，看看我能做些什么。我必须说，我什至看到这个问题的发生感到很惊讶，对我来说，可以将记录添加到 LINQ 提供的数据“缓存”中，然后决定擦除其中的一些，然后提交，这似乎很自然。更改跟踪应该能够处理。我刚开始使用 LINQ，所以我可能在代码的某个地方犯了一个愚蠢的错误（不会是第一个）。

On the other note: You are quite correct that garlic can belong to many recipes (not my coctail recipes thought!). I actually model that with an Article object/table. But for a recipe, you need quantities. So in my model, you have a Recipe that has 1:n Ingredients, each of them having a Quantity, a 1:1 link to an Article (which has a Name, an AlcoholContent and some data to establish an interchangeability hierarchy) and a 1:1 link to an Unit (for the quantity to make sense). So in a sense, Ingredient table makes a M:N relationship between Recipe and Article, and at the same time adding some additional information to each individual linked pair.

另一方面：您说的很对，大蒜可以属于许多食谱（不是我的鸡尾酒食谱认为的！）。我实际上是用一个文章对象/表来建模的。但是对于食谱，您需要数量。因此，在我的模型中，您有一个包含 1:n 成分的 Recipe，每个成分都有一个 Quantity、一个指向文章的 1:1 链接（其中包含一个名称、一个酒精含量和一些数据以建立可互换的层次结构）和一个1:1 链接到一个单位（使数量有意义）。所以从某种意义上说，Ingredient table 在 Recipe 和 Article 之间建立了 M:N 关系，同时为每个单独的链接对添加了一些额外的信息。

回答by Jurij

I had exactly the same problem. I had a parent / child hierarchy, and when adding and removing the child entity without saving to the database I received the "An attempt was made to remove a relationship" exception.

我遇到了完全相同的问题。我有一个父/子层次结构，当添加和删除子实体而不保存到数据库时，我收到了“尝试删除关系”异常。

I discovered that this problem only arose when I set an object style property of the child to another linq-sql entity before saving. eg

我发现只有在保存之前将子项的对象样式属性设置为另一个 linq-sql 实体时才会出现此问题。例如

1. This creates the error

1. 这会产生错误

 RetailAccountCustomerCard racc = new RetailAccountCustomerCard();

 Card addedCard = _idc.Cards.Where(c => c.CardId == card.CardId).ToList().First();

 racc.Card = addedCard;

 this.CurrentCustomer.RetailAccountCardsBindingList.Add(racc); 

 // Some code triggered by the user before saving to the db

 CurrentCustomer.RetailAccountCardsBindingList.Remove(racc);

2. This doesn't create the error

2. 这不会产生错误

 RetailAccountCustomerCard racc = new RetailAccountCustomerCard();

 racc.CardId = card.CardId;  // note that I have set the Id property not the object

 this.CurrentCustomer.RetailAccountCardsBindingList.Add(racc); 


 // Some code triggered by the user before saving to the db

 CurrentCustomer.RetailAccountCardsBindingList.Remove(racc);

Strangely enough, the error that arises in 1. specifies the problem is to do with the relationship is on the RetailAccountCustomerId property of RetailAccountCustomerCard. IT HAS NOTHINGto do with the Card object I added. It seems that simply setting anyobject property of the new entity triggers the problem.

奇怪的是，在 1. 中出现的错误指定问题是与关系有关的 RetailAccountCustomerCard 的 RetailAccountCustomerId 属性。它与我添加的 Card 对象无关。似乎简单地设置新实体的任何对象属性都会触发问题。

NB. Example 1 works fine in terms of saving, it only causes a problem if the the new entity is deleted before saving.

注意。示例 1 在保存方面工作正常，只有在保存之前删除新实体时才会出现问题。

回答by leppie

I am running into a similar issue, as a workaround, I need to call DataContext.GetChanges(), then everything seems to have caught on again :)

我遇到了类似的问题，作为一种解决方法，我需要调用 DataContext.GetChanges()，然后一切似乎又流行起来了 :)

Another problem you could have it that you are binding to columns and not entity properties, and hence the referential collections are not updated (already stated by someone else, but enforcing the fact).

另一个问题可能是您绑定到列而不是实体属性，因此引用集合没有更新（其他人已经声明，但强制执行事实）。

回答by TexTheAfk

// Create new entities
Cart c = new Cart();
CartEntry ce = new CartEntry();
ce.Cart = c;

// Delete the entry
c.CartEntries.Remove(ce);
dc.Cartentries.Attach(ce);
dc.CartEntries.DeleteOnSubmit(ce);

// Insert the cart into database
dc.Carts.InsertOnSubmit(c);
dc.SubmitChanges();

Explaination of the issue: Both entities, c and ce, are not related to a data context - they are not being tracked. EntitySet.Remove() (first delete line) only removes the relation between c and ce. While c can exist without associated cart entries, ce can't exist without an assiciated cart because of a foreign key constraint. When submitting changes to the database, the disconnected ce is dealt with as well, causing a constraint violation and the exception.

问题解释：实体 c 和 ce 都与数据上下文无关 - 它们没有被跟踪。EntitySet.Remove()（第一个删除行）只删除 c 和 ce 之间的关系。虽然 c 可以在没有关联购物车条目的情况下存在，但由于外键约束，ce 不能在没有关联购物车的情况下存在。在向数据库提交更改时，断开连接的 ce 也会被处理，从而导致违反约束和异常。

In order to get rid of that untracked and disconnected cart entry you need to attach it to your data context (causing it to be tracked) and then mark it for delete on submit. The moment you submit your changes the cart entry will be deleted properly and not cause the exception.

为了摆脱未跟踪和断开连接的购物车条目，您需要将其附加到您的数据上下文（使其被跟踪），然后在提交时将其标记为删除。当您提交更改时，购物车条目将被正确删除并且不会导致异常。

For more details on that issue check this out: http://msdn.microsoft.com/en-us/library/bb546187%28v=VS.100%29.aspx

有关该问题的更多详细信息，请查看：http: //msdn.microsoft.com/en-us/library/bb546187%28v=VS.100%29.aspx