我如何使用Nhibernate来检索数据；其中IN()”；有成千上万的价值?(sql中的参数太多)

本文关键字：sql 太多 参数 成千上万 Nhibernate 数据 检索 其中 IN 何使用 | 更新日期: 2023-09-27 17:58:42

问题：Nhibernate将"WHERE in（）"sql中的每个值解析为参数，而MS sql server不支持足够的参数（超过2000）。

我使用Nhibernate和Linq从SQL服务器检索数据，并且我需要基于已知的ID加载许多实体。

我的代码看起来像这样：

int[] knownIds = GetIDsFromFile();
var loadedEntities = _Repository.GetAll()
                                .Where(x => knownIds.Contains(x.ID))
                                .ToList();

它给出了这样的sql：

SELECT id, name FROM MyTable 
WHERE id IN (1 /* @p0 */,2 /* @p1 */,3 /* @p2 */,4 /* @p3 */, 5 /* @p4 */)

如果knownIds中的值太多，那么由于NHibernate使用了许多参数，此代码将引发异常。

我认为最好的解决方案是，如果我能让NHibernate在整个"WHERE IN（）"中只使用一个参数，但我不知道如何做到这一点：

SELECT id, name FROM MyTable WHERE id IN (1, 2, 3, 4, 5 /* @p0 */)

我很乐意听到任何关于如何解决这个问题的想法——无论是通过扩展LINQ提供程序还是通过其他方式。一种解决方案是简单地查询x次（knownIds.Count/1000），但我更想要一个适用于我的所有实体的通用解决方案。

我曾尝试通过搜索谷歌和Stackoverflow来扩展LINQ提供商，但我找不到解决方案，而且我对HQL或树生成器都没有任何经验。以下是我去过的几个网站：

  • 将LINQ扩展到Nhibernate提供程序，并结合动态LINQ问题
  • NHibernate LINQ提供程序扩展
  • 源代码：NHibernate/Linq/Functions/QueryableGenerator.cs

更新：我知道in子句中有这么多值不是一个好的做法，但我不知道有什么更好的解决方案可以解决我想做的事情。
考虑一家公司，所有客户每月为该公司的服务支付一次费用。该公司自己不处理付款，但有另一家公司来收取这笔钱。公司每月有一次收到一份文件，其中包含这些付款的状态：是否已付款。该文件只包含特定付款的ID，而不包含客户的ID。一家每月有3000名客户的公司，每月将进行3000次LogPayments，其中需要更新状态。一年后将有大约36.000个LogPayments，所以仅仅加载它们似乎也不是一个好的解决方案。

我的解决方案：感谢所有有用的答案。最后，我选择了使用答案的组合。对于这个特定的案例，我做了第四次建议的事情，因为这会大大提高性能。然而，我已经实现了Stefan Steinegger建议的通用方法，因为我喜欢我可以做到这一点，如果这是我真正想要的。此外，我不希望我的程序出现异常而崩溃，所以将来我会使用这个ContainsAlot方法作为保护。

我如何使用Nhibernate来检索数据；其中IN()”；有成千上万的价值?(sql中的参数太多)

请参阅这个类似的问题：NHibernate Restrictions

我通常会设置几个查询，例如，所有查询都会得到1000个条目。只需将您的id数组拆分为几个部分。

类似这样的东西：

// 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);

http://ayende.com/blog/2583/nhibernates-xml-in有一个可能的解决方案，将参数作为XML传递（不幸的是，页面中的大多数链接都已断开。）

WHERE in不应成为规范，只应在特定且有限的情况下使用。如果你发现自己经常使用它，这可能表明你的数据模型有问题。在您的情况下，我可能会做的是在延迟加载中从数据库中获取所有实体，然后在迭代我所拥有的ID时，将它们从实体集合中提取出来。通过这种方式，性能命中分布在许多查询中，并且不会达到WHERE IN阈值。

需要注意的是，如果ID将代表大多数实体，而不是一个子集（也就是说，你知道你最终会得到它们的全部或大部分），那么不要懒惰加载。

根据您的更新进行编辑

如果你说的是一年后的36000张唱片，但你只处理最近一段时间的负载，那么就急于加载你关心的最近的唱片。我会做一些类似的事情：创建一个标准来加载过去的记录。。。月然后我会有我可能需要的所有记录，通过代码将它们与文件中的ID进行匹配，然后宾果邦戈。

表的大小肯定会随着时间的推移而增长，所以总是撤回所有内容是没有意义的，但如果你有一种说法"我只关心这些记录"，那么SQL可以为你做这个约束。

我以前看到的代码中唯一一个ID扩展到数千的地方，就是ID列表刚刚作为一个单独的查询从数据库中加载的地方。相反，它应该创建为DetachedCriteria，然后使用Subqueries.PropertyNotIn或PropertyIn条件查询（而不是LINQ）使用。

另一种看待这类事情的方法是-2100个参数感觉像是一个任意的限制。我确信SQL Server可以被修改以接受更多的参数（但我确信Connect请求几乎会立即关闭），或者您可以使用变通方法（例如发送XML或预填充表）来传递这么多参数。但是，如果你达到了这个极限，你难道不应该退后一步，考虑一下你所做的事情中还有其他的问题吗？

您不能使IN列表只有一个参数（例如数组），因为SQL不支持这一点。据我所知，在in列表中有1000多个元素的唯一方法是在其中放置一个子查询
话虽如此，一种解决方法是将已知的ID放入一个临时表中，并将NHibernate语句更改为使用该表，以便在SQL语句中产生子查询。

我肯定会建议使用这种数据的临时表。

通过查询这个临时表，您将能够检查参数是否正确。而且你可能有外键约束，所以你在坏ID之前就被烤坏了。您可以在数据库中保存它们的历史记录。

我在Oracle中遇到了同样的问题，在in条件中也不允许超过1000个元素。错误为："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准则来动态生成多个IN条件，以便稍后与OR条件连接。

相关文章：