Need a query builder for Deleting specific Rows

Question

Need a query builder for BulkDeleting specific Rows by using SqlBulkCopy.

 List<Part> parts = new List<Part>();      
parts.Add(new Part() { PartName = "crank arm", PartId = 1234 });
parts.Add(new Part() { PartName = "chain ring", PartId = 1334 });
parts.Add(new Part() { PartName = "regular seat", PartId = 1434 });
parts.Add(new Part() { PartName = "banana seat", PartId = 1444 });
parts.Add(new Part() { PartName = "cassette", PartId = 1534 });
parts.Add(new Part() { PartName = "shift lever", PartId = 1634 });


 var DeleteQuery = new StringBuilder();
foreach (Part aPart in parts)
{
      DeleteQuery = "DELETE FROM tablename WHERE TestcolumnID IN aPart";
}

 using (var bulkCopy = new SqlBulkCopy(destinationConnection, SqlBulkCopyOptions.Default, transaction as SqlTransaction))
                {
                    var command = new SqlCommand
                    {
                        Connection = destinationConnection,
                        CommandType = CommandType.Text,
                        CommandText = DeleteQuery,
                        Transaction = transaction as SqlTransaction
                    };
                    await command.ExecuteNonQueryAsync();
    }

Kindly let me know what has to changed here

Answer 1

I don't really think you need BulkCopy for this as you're not doing anything in bulk. You can do this all in a single query.

var partsToDelete = String.Join(",", parts.Select(p => p.PartId));

DeleteQuery = $"DELETE FROM tablename WHERE TestcolumnID IN ({partsToDelete})";

Normally I don't recommend using strings directly in queries as this is a SQL injection attack target but in your case you are in complete control over the string being injected so this should be OK. If you want to parameterize this query then you could and I definitely would if you're accepting any sort of user input.

Answer 2

I suggest to look into examples from the StackOverflow post listed by Karen in this thread https://learn.microsoft.com/en-us/answers/questions/819258/c-entity-framework-bulk-delete-performance-issue.html

Answer 3

If you want to perform a DELETE FROM ... WHERE IN with parameters for each key, see my dynamic where in repository.

I intentionally did not show a DELETE WHERE IN but that doesn't matter, the key is

DELETE FROM SomeTable WHERE SomeColumn in ({0})

Granted you need to add the library but it will keep data secure from tampering.

Answer 4

first off SqlBulkCopy is only for inserts, and is not used for deleting

        var parts = new List<Part>();      
        parts.Add(new Part() { PartName = "crank arm", PartId = 1234 });
        parts.Add(new Part() { PartName = "chain ring", PartId = 1334 });
        parts.Add(new Part() { PartName = "regular seat", PartId = 1434 });
        parts.Add(new Part() { PartName = "banana seat", PartId = 1444 });
        parts.Add(new Part() { PartName = "cassette", PartId = 1534 });
        parts.Add(new Part() { PartName = "shift lever", PartId = 1634 });

        var ids = Enumerable.Range(1, parts.Count()).Select(x => "@id1");        
        var DeleteQuery = $"DELETE FROM tablename WHERE TestcolumnID IN ({string.Join(",",ids)})";

        var command = new SqlCommand
            {
                Connection = destinationConnection,
                CommandType = CommandType.Text,
                CommandText = DeleteQuery,
                Transaction = transaction as SqlTransaction
            };

        var count = 0;
        parts.ForEach(p => {
            ++count;
            command.Parameters.AddWithValue($"@id{count}", p.PartId);
        });

        await command.ExecuteNonQueryAsync();

also the transaction will slow performance unless this is part of a transaction batch.

Answer 5

Normally I don't recommend using strings directly in queries as this is a SQL injection attack target but in your case you are in complete control over the string being injected so this should be OK

But SQL injection is only one of the issues with this pattern.

One issue is cache littering. That is, if this query runs frequently, with different lists, each command will gets it entry in the SQL Server cache. While this can be mitigated with "optimize for ad hoc workloads", I think it would be wrong as a developer to assume that this setting is in force. And it still creates a cache entry, if a smaller one. But admittedly, if this query runs only once a day, it's not a big issue.

Another possible issue is performance if these lists can be huge. A few hundred are not a problem, but if you send in a list of hundred thousand items, the parse and compile time can be severe.

There are some more alternatives.

One is indeed SqlBulkCopy, but then you need to create a table to copy the values to, and then use that table in the query. This can be a little bulky, if you pardon the pun.

You can also use a table-value parameter. You would need to create a table type for this. I have an article on my web site that shows some examples with table-valued parameters.

Yet other options are to package the parameters into XML or JSON and then shred the values in a temp table in the SQL code.