Explore our sites

Stack Exchange
tour help careers 2.0
Take the tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

I have a postgresql database and what I am trying to do is remove a bunch of rows from a table in as few queries as possible. So looping is not a good option. I am using NPGSQL for the postgres driver.

I have the code below but it is 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 FROM messages WHERE name = {0}", name);
          }
            string idToDelete = String.Join(",", repliesIdsToDelete);
            int result = context.Database.ExecuteSqlCommand(
                 "DELETE FROM message_translations WHERE idx IN ({0})",
                 repliesIdsToDelete);

I get a "ERROR: 22P02: invalid input syntax for integer:" error when trying to execute the last query. Is there a way to overcome this? If yes can something similar be done with first delete statement where I have to use a string?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

update I'm not working with EF, but here's the way to use array as parameter with Npgsql:

var cmd = NpgsqlCommand();
cmd.CommandText = "select * from test where id = any(@list)";
cmd.Connection = "**************************";
cmd.Parameters.Add("list", NpgsqlDbType.Array | NpgsqlDbType.Integer);
cmd.Connection.Open();
var r = cmd.ExecuteReader();

So I suppose in EF it would something like:

var par = new NpgsqlParameter("list", NpgsqlDbType.Array | NpgsqlDbType.Text);
var par.Value = namesToDelete;
int result1 = context.Database.ExecuteSqlCommand(
    "DELETE FROM messages WHERE name = any(@list)",
    par
);

old I don't like this one at all because it could lead to SQL injection:

int result1 = context.Database.ExecuteSqlCommand(
    String.Format(
        "DELETE FROM messages WHERE name in ({0})",
        String.Join(",", namesToDelete.Select(x => "'" + x + "'"))
    )
);

int result2 = context.Database.ExecuteSqlCommand(
    String.Format(
        "DELETE FROM message_translations WHERE idx in ({0})",
        String.Join(",", repliesIdsToDelete)
    )
);

I'm not an expert in EntityFramework, but I think when you use context.Database.ExecuteSqlCommand(command, str), str is used as a parameter, so your command becomes DELETE FROM message_translations WHERE idx in ('1, 2, 3, 4') (note single quotes, '1, 2, 3, 4' is actually a string on server side).

You could use table-valued parameters if your DB was SQL server, may be it's possible to pass array as a parameter into PostgreSQL, have to try later.

share|improve this answer
 
The same error. The = sign was a typo on my part. –  idipous Aug 28 at 12:59
 
@idipous Have you tried to pass string (not list)? changed the answer, check it –  Roman Pekar Aug 28 at 13:11
 
@idipous may be try with string.format? –  Roman Pekar Aug 28 at 13:18
 
I wish I could push the upvote button more than once man. Thank you. IT worked on both cases. Much faster now! One question though. Why did String.Format do the trick in both cases? –  idipous Aug 28 at 13:39
 
@idipous added an short explanation –  Roman Pekar Aug 28 at 13:46
show 6 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.