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 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 2

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 3

Did you try my code?

Source for code below and class project

If not, here is an example where I mocked up data with NuGet package Bogus.

public class BogusOperations  
{  
    public static List<Part> PartsList(int count = 50)  
    {  
        int id = 1456;  
        return new Faker<Part>()  
            .CustomInstantiator(f => new Part(id++))  
            .RuleFor(part =>  
                    part.PartName,  
                f => f.Commerce.Product())  
            .Generate(count);  
    }  
}  

In a separate class create the WHERE IN

public static (string actual, string exposed) DeleteOrUpdateExample(string commandText, List<int> identifiers)  
{  
  
    using var cn = new SqlConnection() { ConnectionString = GetSqlWhereDatabaseConnection() };  
    using var cmd = new SqlCommand() { Connection = cn };  
      
    cmd.CommandText = SqlWhereInParamBuilder.BuildInClause(commandText + " ({0})", "p", identifiers);  
  
  
    cmd.AddParamsToCommand("p", identifiers);  
  
    return (cmd.CommandText, cmd.ActualCommandText());  
  
}  

Usage

var partIdentifiers = BogusOperations.PartsList().Select(part => part.PartId).ToList();  
  
var (actual, exposed) = DataOperations.DeleteOrUpdateExample(  
    "DELETE FROM Parts WHERE PartId IN", partIdentifiers);  
  
Console.WriteLine(actual);  
Console.WriteLine(exposed);  

Results

DELETE FROM Parts WHERE PartId IN (@p0,@p1,@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21,@p22,@p23,@p24,@p25,@p26,@p27,@p28,@p29,@p30,@p31,@p32,@p33,@p34,@p35,@p36,@p37,@p38,@p39,@p40,@p41,@p42,@p43,@p44,@p45,@p46,@p47,@p48,@p49)  
DELETE FROM Parts WHERE PartId IN (1456,1457,1458,1459,1460,1461,1462,1463,1464,1465,14570,14571,14572,14573,14574,14575,14576,14577,14578,14579,14580,14581,14582,14583,14584,14585,14586,14587,14588,14589,14590,14591,14592,14593,14594,14595,14596,14597,14598,14599,14600,14601,14602,14603,14604,14605,14606,14607,14608,14609)  

Answer 4

Or keep it simple:

var command = new SqlCommand
{
   Connection = connection,
   CommandType = CommandType.Text,
   CommandText = @"DELETE Recognitions WHERE RecognitionID IN (SELECT convert(int, value) FROM string_split(@list, ',') WHERE len(value)> 0)"
   Transaction = transaction as SqlTransaction
};
string list = "";
foreach (var idBatch in idBatches)
{
   list += idBatch.ToString() + ",";
}
command.Parameters.Add("@list", SqlDbType.NVarChar, -1).Value = list;
command.ExecuteNonQuery();

You need SQL 2016 or later for string_split.

Answer 5

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.