上表中唯一约束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屋!