c# - Delete multiple rows in PostgreSQL with Entity Framework using WHERE IN clause -

i have postgresql database , trying remove bunch of rows table in few queries possible. looping not option. using npgsql postgres driver.

i have code below not working.

        string[] namestodelete = promosreplies.                          promosrepliesloaded.groupby(pr => pr.name).                          select(r=>r.key).toarray();         long[] repliesidstodelete = context.promosreplies.                                     where(pr => namestodelete.contains(pr.name)).                                     select(r => r.idx).toarray();         if (repliesidstodelete.length > 0)         {           foreach (var name in namestodelete)           {            context.database.executesqlcommand("delete messages name = {0}", name);           }             string idtodelete = string.join(",", repliesidstodelete);             int result = context.database.executesqlcommand(                  "delete message_translations idx in ({0})",                  repliesidstodelete); 

i "error: 22p02: invalid input syntax integer:" error when trying execute last query. there way overcome this? if yes can similar done first delete statement have use string?

update i'm not working ef, here's way use array parameter npgsql:

var cmd = npgsqlcommand(); cmd.commandtext = "select * test id = any(@list)"; cmd.connection = "**************************"; cmd.parameters.add("list", npgsqldbtype.array | npgsqldbtype.integer); cmd.connection.open(); var r = cmd.executereader(); 

so suppose in ef like:

var par = new npgsqlparameter("list", npgsqldbtype.array | npgsqldbtype.text); var par.value = namestodelete; int result1 = context.database.executesqlcommand(     "delete messages name = any(@list)",     par ); 

old don't 1 @ because lead sql injection:

int result1 = context.database.executesqlcommand(     string.format(         "delete messages name in ({0})",         string.join(",", namestodelete.select(x => "'" + x + "'"))     ) );  int result2 = context.database.executesqlcommand(     string.format(         "delete message_translations idx in ({0})",         string.join(",", repliesidstodelete)     ) ); 

i'm not expert in entityframework, think when use context.database.executesqlcommand(command, str), str used parameter, command becomes delete message_translations idx in ('1, 2, 3, 4') (note single quotes, '1, 2, 3, 4' string on server side).

you use table-valued parameters if db sql server, may it's possible pass array parameter postgresql, have try later.