Как использовать более 2100 значений в разделе IN с помощью Dapper?

c# dapper sql-server

Вопрос

У меня есть список, содержащий идентификаторы, которые я хочу вставить в временную таблицу с помощью Dapper, чтобы избежать ограничения по параметрам SQL в предложении «IN».

Так что в настоящее время мой код выглядит так:

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            WHERE
            at.animalId in @animalIds", new { animalIds }).ToList();
    }
}

Проблема, которую мне нужно решить, заключается в том, что, когда в списке animalIds содержится более 2100 идентификаторов, я получаю SQL-ошибку «Входящий запрос имеет слишком много параметров. Сервер поддерживает максимум 2100 параметров».

Итак, теперь я хотел бы создать временную таблицу, заполненную символами animalIds, переданными в метод. Затем я могу присоединиться к таблице животных на временном столе и избежать огромного предложения «IN».

Я пробовал различные комбинации синтаксиса, но не получил нигде. Вот где я сейчас:

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
    using (var db = new SqlConnection(this.connectionString))
    {
        db.Execute(@"SELECT INTO #tempAnmialIds @animalIds");

        return db.Query<int>(
            @"SELECT a.animalID        
            FROM
            dbo.animalTypes [at]
            INNER JOIN animals [a] on a.animalTypeId = at.animalTypeId
            INNER JOIN edibleAnimals e on e.animalID = a.animalID
            INNER JOIN #tempAnmialIds tmp on tmp.animalID = a.animalID).ToList();
    }
}

Я не могу заставить SELECT INTO работать со списком идентификаторов. Я иду об этом неправильно, может быть, есть лучший способ избежать ограничения «IN».

У меня есть решение для резервного копирования, в котором я могу разделить входящий список animalIDs на блоки из 1000, но я прочитал, что большое предложение «IN» страдает от удара по производительности, а соединение с временной таблицей будет более эффективным, и это также означает, что я не нужно дополнительный код «расщепления» для загрузки идентификаторов в блоки 1000.

Принятый ответ

Хорошо, вот версия, которую вы хотите. Я добавляю это как отдельный ответ, так как мой первый ответ с использованием SP / TVP использует другую концепцию.

public IList<int> LoadAnimalTypeIdsFromAnimalIds(IList<int> animalIds)
{
  using (var db = new SqlConnection(this.connectionString))
  {
    // This Open() call is vital! If you don't open the connection, Dapper will
    // open/close it automagically, which means that you'll loose the created
    // temp table directly after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");
    while (animalIds.Any())
    {
      // Build the statements to insert the Ids. For this, we need to split animalIDs
      // into chunks of 1000, as this flavour of INSERT INTO is limited to 1000 values
      // at a time.
      var ids2Insert = animalIds.Take(1000);
      animalIds = animalIds.Skip(1000).ToList();

      StringBuilder stmt = new StringBuilder("INSERT INTO #tempAnimalIds VALUES (");
      stmt.Append(string.Join("),(", ids2Insert));
      stmt.Append(");");

      db.Execute(stmt.ToString());
    }

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

Тестировать:

var ids = LoadAnimalTypeIdsFromAnimalIds(Enumerable.Range(1, 2500).ToList());

Вам просто нужно внести изменения в свой оператор select к тому, что он изначально был. Поскольку у меня нет всех ваших таблиц в моей среде, я просто выбрал из созданной таблицы temp, чтобы доказать, что он работает так, как должен.

Ловушки, см. Комментарии:

  • Откройте соединение в начале, в противном случае таблица temp исчезнет после того, как dapper автоматически закрывает соединение сразу после создания таблицы.
  • Этот особый вкус INSERT INTO ограничен 1000 значениями за раз, поэтому переданные идентификаторы необходимо разделить на куски соответственно.
  • Не пропускайте повторяющиеся ключи, так как первичный ключ в таблице temp не позволит этого.

редактировать

Кажется, Dapper поддерживает операцию на основе набора, которая также сделает эту работу:

public IList<int> LoadAnimalTypeIdsFromAnimalIdsV2(IList<int> animalIds)
{
  // This creates an IEnumerable of an anonymous type containing an Id property. This seems
  // to be necessary to be able to grab the Id by it's name via Dapper.
  var namedIDs = animalIds.Select(i => new {Id = i});
  using (var db = new SqlConnection(this.connectionString))
  {
    // This is vital! If you don't open the connection, Dapper will open/close it
    // automagically, which means that you'll loose the created temp table directly
    // after the statement completes.
    db.Open();

    // This temp table is created having a primary key. So make sure you don't pass
    // any duplicate IDs
    db.Execute("CREATE TABLE #tempAnimalIds(animalId int not null primary key);");

    // Using one of Dapper's convenient features, the INSERT becomes:
    db.Execute("INSERT INTO #tempAnimalIds VALUES(@Id);", namedIDs);

    return db.Query<int>(@"SELECT animalID FROM #tempAnimalIds").ToList();
  }
}

Я не знаю, насколько хорошо это будет выполняться по сравнению с предыдущей версией (то есть 2500 одиночных вставок вместо трех вставок с 1000, 1000, 500 значений каждый). Но документ предполагает, что он работает лучше, если использовать его вместе с асинхронными, MARS и конвейерами.


Популярные ответы

В вашем примере, что я не вижу, так это то, как ваш список animalIds фактически передается в запрос, который нужно вставить в таблицу #tempAnimalIDs .

Существует способ сделать это, не используя временную таблицу, используя хранимую процедуру с параметром значения таблицы.

SQL:

CREATE TYPE [dbo].[udtKeys] AS TABLE([i] [int] NOT NULL)
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[myProc](@data as dbo.udtKeys readonly)AS
BEGIN
    select i from @data;
END
GO

Это создаст пользовательский тип таблицы, называемый udtKeys который содержит только один столбец int с именем i и хранимую процедуру, которая ожидает параметр этого типа. Против ничего не делает, кроме как выбрать идентификаторы, которые вы передали, но вы можете, конечно, присоединиться к другим таблицам. Для получения подсказки относительно синтаксиса см. Здесь .

C #:

var dataTable = new DataTable();
dataTable.Columns.Add("i", typeof(int));
foreach (var animalId in animalIds)
    dataTable.Rows.Add(animalId);
using(SqlConnection conn = new SqlConnection("connectionString goes here"))
{
    var r=conn.Query("myProc", new {data=dataTable},commandType: CommandType.StoredProcedure);
    // r contains your results
}

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

Если вам действительно нужно передать более 2100 значений, вы можете захотеть проиндексировать свой тип таблицы для повышения производительности. Фактически вы можете дать ему первичный ключ, если вы не передадите дубликаты ключей, например:

CREATE TYPE [dbo].[udtKeys] AS TABLE(
    [i] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [i] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

Вам также может потребоваться присвоить разрешения запуска для этого типа пользователю базы данных, с которым вы выполняете это, например:

GRANT EXEC ON TYPE::[dbo].[udtKeys] TO [User]
GO

См. Также здесь и здесь .



Лицензировано согласно: CC-BY-SA with attribution
Не связан с Stack Overflow
Лицензировано согласно: CC-BY-SA with attribution
Не связан с Stack Overflow