Datacontext ExecuteCommand paramters IN statement

Question

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.

Answer 1

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.