How to have a where clause on an insert or an update in Linq to Sql?

Question

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)

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?

Edit: I am running into a similar issue with an update:

UPDATE [AnotherTable]
SET [Code] = @code
WHERE [ID] = @id AND [Code] IS 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...

Answer 1

If this is an unexpected case (i.e. indicates an error), a UNIQUE constraint should suffice.

There is no direct way to do it via LINQ-to-SQL, so your TransactionScope (or a SqlTransaction on a connection passed in) is a viable mechanism. Another might be an instead-of trigger, or a stored-procedure to do the INSERT.

What you have is probably the simplest; see if it is fast enough (it has an extra round-trip) and stick with it?

Answer 2

I don't believe that's possible with LINQ-to-SQL, but a better option would be to use Any() instead of SingleOrDefault():

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

        // Do some stuff in the transaction

        scope.Complete();
    }
}

I believe Any() uses the EXISTS keyword in SQL, which selects a Boolean instead of the full content of all the columns in that row.

Aaron's suggestion of using a Stored Procedure would probably be more straightforward - and you can wire that up to your datacontext as well.

Answer 3

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.

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.