如何对INSERT或Linq中的更新SQL where子句？ [英] How to have a where clause on an insert or an update in Linq to Sql?

问题描述

我想下面的存储过程到LinqToSql调用（这是SQL的一个简化版本）转换：

I am trying to convert the following stored proc to a LinqToSql call (this is a simplified version of the SQL):

INSERT INTO [MyTable]
    ([Name], [Value])
SELECT
    @name, @value
WHERE
   NOT EXISTS(SELECT [Value] FROM [MyTable] WHERE [Value] = @value)

在DB没有在球场上约束这是获得在检查需要手动进行这种特定情况下检查如此。也有很多项目不断被插入以及所以我需要确保当该特定插入情况是没有价值领域愚弄。我的第一直觉是做到以下几点：

The DB does not have a constraint on the field that is getting checked for so in this specific case the check needs to be made manually. Also there are many items constantly being inserted as well so I need to make sure that when this specific insert happens there is no dupe of the value field. My first hunch is to do the following:

using (TransactionScope scope = new TransactionScope())
{
    if (Context.MyTables.SingleOrDefault(t => t.Value == in.Value) != null)
    {
        MyLinqModels.MyTable t = new MyLinqModels.MyTable()
        {
           Name = in.Name,
           Value = in.Value
        };

        // Do some stuff in the transaction

        scope.Complete();
    }
}

这是我第一次真正碰到这个场景，所以我要确保我将它的正确途径。 ？这看起来正确的或任何人可以提出要去一个关于它的更好的方法，而无需两个单独的呼叫

This is the first time I have really run into this scenario so I want to make sure I am going about it the right way. Does this seem correct or can anyone suggest a better way of going about it without having two separate calls?

编辑：我正在成类似的问题与更新：

I am running into a similar issue with an update:

UPDATE [AnotherTable]
SET [Code] = @code
WHERE [ID] = @id AND [Code] IS NULL

我怎么会做Linqtosql相同的检查？我想我需要做一个GET，然后将所有的值和提交，但如果有人从时间更新[代码]为null之外的东西，我做的到，当更新执行？

How would I do the same check with Linqtosql? I assume I need to do a get and then set all the values and submit but what if someone updates [Code] to something other than null from the time I do the get to when the update executes?

同样的问题作为插入...

Same problem as the insert...

推荐答案

在包裹这个的TransactionScope 实际上并不防止冲突，如果有数据库中没有唯一约束。它只是保证了的这个的事务。

Wrapping this in a TransactionScope does not actually prevent conflicts if there's no unique constraint in the database. It only guarantees atomicity of this transaction.

这是完全可能的，可能是在一个高容量的情况可能有两个同时交易通过第一原子空检查（这只是读），让周围开始他们更新之前。强制执行数据库本身的唯一性约束这真的很重要 - 如果你不能在这里这样做，那么你有你的工作等着你。

It's completely possible and probably likely in a high-volume scenario to have two simultaneous transactions pass the first null check (which is just a read) before getting around to beginning their updates. It's really important to enforce uniqueness constraints in the database itself - if you can't do that here, then you have your work cut out for you.

老实说，基于你的要求，我会建议，而不是用存储过程做。 LINQ到SQL是一个很好的工具，但它不能做的所有的该SQL即可;这似乎是那些需要更多的控制比L2S真的可以给你的案例之一。

Honestly, based on your requirements, I would recommend doing it with a stored procedure instead. Linq to SQL is a great tool but it can't do everything that SQL can; this seems to be one of those cases where you need more control than L2S can really give you.

这篇关于如何对INSERT或Linq中的更新SQL where子句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！