EF ExecuteSqlCommand with int array parameter

Question

I got a problem while trying to pass parameter with type array of int. What I have done so far as below but both methods failed.

Method 1 (Failed) :

int[] CategoryArray;
CategoryArray = new int[userItem.ListItemId.Count()];
int i=0;

foreach (int catID in userItem.ListItemId)
{
    CategoryArray[i] = catID;
    i++;
}

db.Database.ExecuteSqlCommand("delete from SupportRegion where UserId={0} and CategoryID not in ({1})", userItem.UserId, CategoryArray);

Method 2 (also failed) :

db.Database.ExecuteSqlCommand("delete from SupportRegion where UserId={0} and CategoryID not in ({1})", userItem.UserId, String.Join(",", userItem.ListItemId)); 

How can I make it possible in defining parameter as an array of integer?

thanks a lot

Answer 1

The first case won't work since the database does not understand what the int array means. I don't know what "failed" in the 2nd example means but I imagine that Sql Server cannot convert string to int. I believe what is happening on the server side is that the query is converted to something like this (notice quotes):

delete from SupportRegion where UserId={0} and CategoryID not in ('1, 2, 3')

since the parameter you are passing is a string. However the CategoryID column is not a string the passed parameter cannot be converted to int.

I think what you could try using is a table value parameter but it looks like setting it up is a bit ugly.

Depending on how many entities you are deleting the safest thing may be to bring the entities to the client, mark the ones you want to delete as deleted and call SaveChanges().

Another work around is to set up your command text right (see disclaimer below):

db.Database.ExecuteSqlCommand(
     string.Format(
         "delete from SupportRegion where UserId={{0}} and CategoryID in ({0})", 
         String.Join(",",   userItem.ListItemId), 
      userItem.UserId)); 

this way string.format call should embed your list of ints as a text and then pass it down to the ExecuteSqlCommand method which will take care of the user id.

Disclaimer The above method could be exploited by a Sql Injection attack. Never use it if you don't control the source of the data you use to build the list of ids to be deleted. In general I would recommend to not use this method unless you really know how it is used and you are sure nothing bad happens (which you can really never be...)