上表中唯一约束LINQ2SQL处理插入/删除 [英] Linq2SQL dealing with inserts/deletes on table with unique constraints

问题描述

我有一个类似如下的表：

I have a table that looks like the following:

TABLE Foo
{
  Guid Id [PK],
  int A [FK],
  int B [FK],
  int C [FK],
}

和在A，B和C唯一约束。

And unique constraint over A, B and C.

现在比方说，你插入一行与一个新鲜的PK与A = 1，B = 1，C = 1。

Now say for example, you insert a row with a fresh PK with with A = 1, B = 1, C = 1.

的SubmitChanges（），所有的快乐。

现在您编辑表。

您删除previous入口，并配有fresk PK与A = 1插入一行，B = 1，C = 1。

You remove the previous entry, and insert a row with a fresk PK with A = 1, B = 1, C = 1.

的SubmitChanges（） BOOM！唯一键约束的SQL异常。

SubmitChanges() BOOM! Unique key constraint SQL exception.

这是我所看到的，它会尝试先插入新记录，然后再尝试删除previous之一。我甚至可以明白，这是无法确定这需要发生的顺序。

From what I can see, it attempts to first insert the new record, and then try to delete the previous one. I can even understand that it is not possible to determine the order this needs to happen.

不过，我能做些什么呢？会使得那些3场复合PK（并删除旧的）是一个更好的解决方案，或惯于它甚至工作？

But what can I do about it? Would making those 3 fields a composite PK (and removing the old one) be a better solution or wont it even work?

目前，在解决方案是从数据库中删除唯一约束（但我不想这样做）。

For now, the 'solution' is to remove the unique constraints from the DB (but I'll rather not do so).

推荐答案

一种选择是创建一个交易（无论是连接绑定的交易，或的TransactionScope ） - 删除的记录和的SubmitChanges ，添加记录和的SubmitChanges ，最后提交事务（或回滚，如果你炸毁了）。

One option would be to create a transaction (either a connection-bound transaction, or a TransactionScope) - remove the record and SubmitChanges, add the record and SubmitChanges, then finally commit the transaction (or roll-back if you blew up).

请注意，您可以连接绑定的交易通过数据上下文构造IIRC关联。 的TransactionScope 也应该工作，而且是容易做 - 但不是很有效

Note that you can associate a connection-bound transaction through the data-context constructor IIRC. TransactionScope should also work, and is easier to do - but not quite as efficient.

另外，写，做这种交换工作在数据库的SP，并获得了SP的通过的数据上下文。

Alternatively, write an SP that does this swap job at the database, and access that SP via the data-context.

这篇关于上表中唯一约束LINQ2SQL处理插入/删除的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！