流利的NHibernate不会在WHERE子句中创建IN部分 [英] Fluent NHibernate does not create IN part of WHERE clause

问题描述

我有Fluent NHibernate Linq查询，其中我根据运行时数组检查值.一个基本的例子是:

I have Fluent NHibernate Linq queries where I check values based on run time arrays. A basic example would be something like:

var array = [1,2,3,4,5,6];
using (var session = SessionProvider.SessionFactory.OpenSession())
{
  return session.Query<MyObject>().Where(x => array.Contains(x.CompareVal)).ToList();
}

我希望生成的SQL语句看起来像这样:

I would expect the generated SQL statement to look something like this:

SELECT CompareVal, Column1, Column2
FROM MyObject
WHERE CompareVal IN (1,2,3,4,5,6)

但是，我发现的是，生成的SQL语句只是发出WHERE子句(通过在Profiler中进行观察来证明)并选择整个表，然后在获取所有数据后似乎在内存中运行过滤器回来.

However, what I'm finding instead is that the generated SQL statement simply emits the WHERE clause (proven by watching in Profiler) and selects the entire table, and then seems to run the filter in memory once it gets all the data back.

需要注意的事情-我有一个通用存储库类，所有这些调用都通过该类进行了传递. Query方法如下:

Something to note - I have a Generic Repository class that all of these calls are funneled through. The Query method is as follows:

public IList<T> Query(Func<T, bool> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

显然，在有大量数据的表中，这种情况(缺少where子句)是不可接受的.如何强制NHibernate使用WHERE子句正确生成查询并仍然为存储库保留通用模式?

Obviously this (lack of a where clause) is not acceptable in a table with a large amount of data. What can I do to force NHibernate to generate the query correctly with the WHERE clause and still keep a generic pattern for repositories?

推荐答案

使用任意:

 return session.Query<MyObject>().Where(x => array.Any(y => y == x.CompareVal)).ToList();

您的存储库模式(使用纯Func)会自动将您的查询具体化为要列出的内容，如果您希望推迟执行某些操作，请使用IQueryable，而不仅仅是使用Func

Your repository pattern (using plain Func) automatically materializes your query to list, if you want something to be deferredly executed, use IQueryable, don't use Func only

需要注意的事情-我有一个通用存储库类，所有 这些电话都可以通过. Query方法如下:

Something to note - I have a Generic Repository class that all of these calls are funneled through. The Query method is as follows:

public IList<T> Query(Func<T, bool> criteria)
{
  using (var session = SessionProvider.SessionFactory.OpenSession())
  {
    return session.Query<T>().Where(criteria).ToList();
  }
}

您的存储库只是模仿NHibernate开箱即用的内容

Your repository just mimic what is already provided out of the box by NHibernate

这篇关于流利的NHibernate不会在WHERE子句中创建IN部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！