How To Have A Where Clause On An Insert Or An Update In Linq To 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

Solution 1:

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.

Solution 2:

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?

Solution 3:

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

using (TransactionScopescope=newTransactionScope())
{
    if (!Context.MyTables.Any(t => t.Value == in.Value))
    {
        MyLinqModels.MyTablet=newMyLinqModels.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.