Как я могу использовать Nhibernate для извлечения данных, когда "WHERE IN()" имеет тысячи значений? (слишком много параметров в sql)

Ответ 1

Смотрите этот похожий вопрос: NHibernate Restrictions.In с сотнями значений

Я обычно настраиваю несколько запросов, которые получают, например, 1000 записей. Просто разделите массив идентификаторов на несколько частей.

Что-то вроде этого:

// only flush the session once. I have a using syntax to disable
// autoflush within a limited scope (without direct access to the
// session from the business logic)
session.Flush();
session.FlushMode = FlushMode.Never;

for (int i = 0; i < knownIds; i += 1000)
{
  var page = knownIds.Skip(i).Take(1000).ToArray();
  loadedEntities.AddRange(
    Repository.GetAll()
      .Where(x => page.Contains(x.ID)));
}

session.FlushMode = FlushMode.Auto;

Общая реализация с использованием критериев (только фильтрация одного свойства, которое является общим случаем):

public IList<T> GetMany<TEntity, TProp>(
  Expression<Func<TEntity, TProp>> property,
  IEnumerable<TProp> values)
{
    string propertyName = ((System.Linq.Expressions.MemberExpression)property.Body).Member.Name;

    List<T> loadedEntities = new List<T>();

    // only flush the session once. 
    session.Flush();
    var previousFlushMode = session.FlushMode;
    session.FlushMode = FlushMode.Never;

    for (int i = 0; i < knownIds; i += 1000)
    {
      var page = knownIds.Skip(i).Take(1000).ToArray();

      loadedEntities.AddRange(session
        .CreateCriteria(typeof(T))
        .Add(Restriction.PropertyIn(propertyName, page)
        .List<TEntity>();
    }

    session.FlushMode = previousFlushMode;
    return loadedEntities;
}

Используется следующим образом:

int[] ids = new [] {1, 2, 3, 4, 5 ....};
var entities = GetMany((MyEntity x) => x.Id, ids);

string[] names = new [] {"A", "B", "C", "D" ... };
var users = GetMany((User x) => x.Name, names);

Ответ 2

http://ayende.com/blog/2583/nhibernates-xml-in имеет возможное решение, передавая параметры как XML (к сожалению, большинство ссылок на странице нарушены..)

Ответ 3

ГДЕ IN не должно быть нормой и должно использоваться только в конкретных и ограниченных случаях. Если вы много используете его, это, вероятно, указывает на проблему с вашей моделью данных. То, что я, вероятно, сделаю в вашем случае, это получить ВСЕ объекты из базы данных в ленивой загрузке, а затем, когда я повторяю идентификаторы, которые у меня есть, вытащите их из коллекции сущностей. Таким образом, поражение производительности распределяется по многим запросам, и вы не попадаете в порог WHERE IN.

Просто отметим, что если идентификаторы будут представлять большинство сущностей, а не небольшое подмножество (это вы знаете, что вы все равно получите их все или большинство), тогда не lazy-load.

Изменить на основе вашего обновления

Если вы говорите о 36 000 записей через 1 год, НО вы имеете дело только с нагрузками в последнее время, то с нетерпением загружайте последние записи, о которых вы заботитесь. Я бы сделал что-то вроде: создайте критерии для загрузки записей за прошлый... месяц? Тогда у меня будут все записи, которые мне могут понадобиться, сопоставьте их с идентификаторами в файле с помощью кода и bingo-bango-bongo.

Таблица со временем будет расти со временем, поэтому нет смысла всегда оттягивать все, но если у вас есть способ сказать "я только забочусь об этих записях", тогда SQL может сделать это ограничение для вас.

Ответ 4

Единственное место, где я видел такой код, до тех пор, пока идентификаторы расширяются до тысяч, заключались в том, что список идентификаторов только что был загружен из базы данных в виде отдельного запроса. Вместо этого он создается как DetachedCriteria, а затем потребляется с использованием запроса критериев Subqueries.PropertyNotIn или PropertyIn (а не LINQ).

Еще один способ взглянуть на это - 2100 параметров чувствует себя как произвольный предел. Я уверен, что SQL Server может быть изменен, чтобы принимать больше параметров (но я уверен, что запрос Connect будет закрыт почти сразу), или вы можете использовать обходные методы (такие как отправка XML или предварительная заполнение таблицы), чтобы передать это многие параметры. Но если вы нажмете этот предел, разве вы не должны отступать и считать, что в чем-то еще что-то сломалось?

Ответ 5

Вы не можете сделать список IN только одним параметром (например, массивом), потому что это не поддерживается SQL. Единственный способ, которым я знаю, иметь более 1000 элементов в списке IN, - это разместить там дополнительный запрос.
Сказав это, одним из способов было бы превратить известные идентификаторы во временную таблицу и изменить оператор NHibernate для использования этой таблицы, чтобы она привела к подзапросу в инструкции SQL.

Ответ 6

Я бы определенно рекомендовал использовать временную таблицу для такого рода данных.

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

Ответ 7

Я столкнулся с той же проблемой в Oracle, которая не допускает более 1000 элементов в состоянии IN. Ошибка: "ORA-01795: максимальное количество выражений в списке равно 1000". Вот мое решение:

    //partition an IEnumerable into fixed size IEnumerables
    public static IEnumerable<IEnumerable<T>> Partition<T>(this IEnumerable<T> source, int partitionSize)
    {
        return source
            .Select((value, index) => new { Index = index, Value = value })
            .GroupBy(i => i.Index / partitionSize)
            .Select(i => i.Select(i2 => i2.Value));
    }

    public IEnumerable<T> Get(List<long> listOfIDs)
    {
        var partitionedList = listOfIDs.Partition(1000).ToList();
        List<ICriterion> criterions = new List<ICriterion>();
        foreach (var ids in partitionedList)
        {
            criterions.Add(Restrictions.In("Id", ids.ToArray()));
        }
        var criterion = criterions.Aggregate(Restrictions.Or);
        var criteria = session.CreateCriteria<T>().Add(criterion);
        return criteria.Future<T>();
    }

Первая часть - это метод расширения для IEnumerable, чтобы разбить большой список на списки фиксированного размера. Вторая часть использует NHibernate Criterions для динамического генерации нескольких условий IN, которые впоследствии будут объединены с условиями OR.