Linq to SQL:调用 SubmitChanges() 时的执行顺序

问题描述

我有 2 个相关的数据库表，它们的简化形式如下

I have 2 related database tables which in simplified form look like this

Product(
  product_id,
  name
)

ProductSpecs(
  spec_id,
  product_id,
  name,
  value
)

外键通过 product_id 字段设置，ProductSpecs 表对 (product_id, name) 对有唯一约束.

Foreign key is set via product_id field and ProductSpecs table has a unique constraint on (product_id, name) pair.

现在在我的 ASP.NET MVC 应用程序中，当用户编辑产品规格并保存数据时，我删除旧规格并将所有规格作为新规格插入.

Now in my ASP.NET MVC application when user edits product specs and saves the data I delete old specs and insert all as new ones.

为此，我首先调用 DataContext.DeleteAllOnSubmit() 并提供当前(旧)ProductSpecs 作为参数，然后将新规范添加到 Product.ProductSpecs 集合.

I do this by first calling DataContext.DeleteAllOnSubmit() and providing current (old) ProductSpecs as a parameter, and then I add new specs to the Product.ProductSpecs collection.

然后我调用 DataContext.SubmitChanges() 并得到一个错误，指出我的唯一约束被违反.

Then I call DataContext.SubmitChanges() and get an error that my unique constraint was violated.

通过查看 DataContenxt.GetChangeText() 返回的 SQL 语句，我可以看到 INSERT 在 DELETE 之前执行(即使我在 Add 之前调用了 DeleteAllOnSubmit()).

By looking at the SQL statements returned by DataContenxt.GetChangeText() I can see that INSERTs are executed before DELETEs (even though I called DeleteAllOnSubmit() before Add).

这种行为的原因是什么以及如何修复或解决它?

What is the reason of this behavior and how to fix or workaround it?

谢谢.

推荐答案

是的，出于某种原因，Linq to SQL 将所有删除作为最后一件事执行.没有办法改变这一点.

Yes, for some reason, Linq to SQL perform all deletes as the last thing. And there is no way to change that.

或者也许有.我还没有查看 codegen DataContext 是否可以覆盖那里的某些内容.

Or maybe there is. I haven't looked into the codegen DataContext to see if we can override something there.

您可以根据需要多次调用 SubmitChanges().当我需要首先删除时，我的解决方法是标记我的删除，调用 SubmitChanges()，然后执行插入和更新，并再次调用 SubmitChanges.

You can call SubmitChanges() as many times you want. My workaround when I need to delete as the first thing, is mark my deletions, call SubmitChanges(), then perform inserts and updates, and call SubmitChanges once again.

您可以将所有内容都包装在 TransactionScope 中:

You can wrap everything inside of a TransactionScope:

    var deletables =
        from toDelete in db.NamedValues
        where toDelete.Name == knownValue.Name
        select toDelete;

    using (var scope = new TransactionScope())
    {
        db.NamedValues.DeleteAllOnSubmit(deletables);
        db.SubmitChanges();

        db.NamedValues.InsertOnSubmit(knownValue);
        db.SubmitChanges();

        scope.Complete();
    }

这篇关于Linq to SQL:调用 SubmitChanges() 时的执行顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！