Правильный метод удаления более 2100 строк (по идентификатору) с помощью Dapper

Один из вариантов — создать временную таблицу на сервере, а затем использовать средство массовой загрузки для одновременной загрузки всех идентификаторов в эту таблицу. Затем используйте предложение join, EXISTS или IN, чтобы удалить только те записи, которые вы загрузили во временную таблицу.

Массовая загрузка — это хорошо оптимизированный путь в SQL Server, и он должен быть очень быстрым.

Например:

1. Выполнить оператор CREATE TABLE #RowsToDelete(ID INT PRIMARY KEY)
2. Используйте массовую загрузку, чтобы вставить ключи в #RowsToDelete
3. Выполнить DELETE FROM myTable where Id IN (SELECT ID FROM #RowsToDelete)
4. Выполните DROP TABLE #RowsToDelte (таблица также будет автоматически удалена, если вы закроете сеанс)

Пример кода (предполагая Dapper):

conn.Open();

var columnName = "ID";

conn.Execute(string.Format("CREATE TABLE #{0}s({0} INT PRIMARY KEY)", columnName));

using (var bulkCopy = new SqlBulkCopy(conn))
{
    bulkCopy.BatchSize = ids.Count;
    bulkCopy.DestinationTableName = string.Format("#{0}s", columnName);

    var table = new DataTable();                    
    table.Columns.Add(columnName, typeof (int));
    bulkCopy.ColumnMappings.Add(columnName, columnName);

    foreach (var id in ids)
    {
        table.Rows.Add(id);
    }

    bulkCopy.WriteToServer(table);
}

//or do other things with your table instead of deleting here
conn.Execute(string.Format(@"DELETE FROM myTable where Id IN 
                                   (SELECT {0} FROM #{0}s", columnName));

conn.Execute(string.Format("DROP TABLE #{0}s", columnName));

Чтобы заставить этот код работать, я перешел на темную сторону.

Так как Даппер превращает мой список в параметры. И SQL Server не может обрабатывать множество параметров. (Мне никогда раньше не требовались даже двузначные параметры). Мне пришлось пойти с динамическим SQL.

Итак, вот мое решение:

string listOfIdsJoined = "("+String.Join(",", listOfIds.ToArray())+")";
connection.Execute("delete from myTable where Id in " + listOfIdsJoined);

Прежде чем все возьмутся за факелы и вилы, позвольте мне объяснить.

• Этот код работает на сервере, единственным вводом которого является поток данных из системы мейнфреймов.
• Список, который я динамически создаю, представляет собой список longs/bigints.
• Longs/bigints взяты из столбца Identity.

Я знаю, что построение динамического SQL — это плохо, но в данном случае я просто не понимаю, как это ведет к угрозе безопасности.

Dapper запрашивает список объектов, имеющих параметр в качестве свойства, поэтому в приведенном выше случае будет работать список объектов, имеющих идентификатор в качестве свойства.

connection.Execute("delete from myTable where Id in (@Id)", listOfIds.AsEnumerable().Select(i=> new { Id = i }).ToList());

Это сработает.