What is the best way to run a custom sql statement using IN from a C# LinQ to sql datacontext? I have tried:
db.ExecuteCommand(
"UPDATE tblCard SET used = 1 WHERE id IN ({0}) AND customer_id = {1}",
Request.Form["ids"], customer_id
);
Which is fine for 1 item passed through the form, but if i get posted through for example "2,1" then I get a sqlclient exception:
Conversion failed when converting the nvarchar value '2,1' to data type int.
If instead I use string.format to insert the parameter it works fine, but obviously this is open to sql injection.
LINQ-to-SQL doesn't use concatenation; that will be a TSQL query of the form:
WHERE id IN (@p0)
with @p0 set to '123,456,789'.
With regular LINQ you could use Contains. With ExecuteQuery there are a few options; you could, for example, pass in the CSV "as is", and use a UDF to split this at the database, and join to it:
UPDATE c SET c.used = 1
FROM dbo.SplitCsv({0}) udf
INNER JOIN tblCard c
ON c.Id = udf.Value
AND c.Customer_ID = {1}
Where dbo.SplitCsv is one of the many such "split" udfs available on the internet.