How to have a where clause on an insert or an update in Linq to Sql?
- by Kelsey
I am trying to convert the following stored proc to a LinqToSql call (this is a simplied 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 seperate 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...
