为什么SQL查询中没有WHERE子句？

I have a strange problem. I noticed that the code defined below:

我有一个奇怪的问题。我注意到下面定义的代码:

var query= unitOfWork.Session.CreateCriteria(typeof (SomeEntity)).Future<SomeEntity>().AsQueryable();
var queryWithWhere= query.Where(x => x.SomeProperty.ToLower().Contains("Xxx".ToLower()));
var result= queryWithWhere.ToList();

gives proper result but the SQL query I watched in NHibernateProfiler doesn't contain where clause, it's just

给出了正确的结果,但我在NHibernateProfiler中看到的SQL查询不包含where子句,它只是

SELECT ... 
FROM SomeEntity

and it seems that this WHERE from my code is is used after the SQL query runs, just like Linq-to-Objects instead of Linq-to-SQL.

似乎我的代码中的这个WHERE是在SQL查询运行之后使用的,就像Linq-to-Objects而不是Linq-to-SQL一样。

And of course the SQL query is started after the third line is proceeded.

当然,SQL查询是在第三行继续之后启动的。

4 个解决方案

#1

If you want to use Linq, and for NHibernate to understand it, then use the CreateQuery<T> extension (part of NHibernate, in the NHibernate.Linq namespace) to directly create an IQueryable<T>, rather than using CreateCriteria<T> - which, as you have noticed, only lets you write the parts of your query that NHibernate will process as restrictions.

如果你想使用Linq,并让NHibernate理解它,那么使用CreateQuery 扩展(NHibernate的一部分,在NHibernate.Linq命名空间中)直接创建一个IQueryable ,而不是使用CreateCriteria - 正如您所注意到的那样,只允许您编写NHibernate将作为限制处理的查询部分。

#2

If you want the WHERE inside the first query I think you need to add a Restriction:

如果你想在第一个查询中使用WHERE,我认为你需要添加一个限制:

 unitOfWork.Session.CreateCriteria<SomeEntity>()
    .Add(Restrictions.Eq("SomeProperty", "Some Value"))

Cheers

EDIT

Or more suitable for you

或者更适合你

 unitOfWork.Session.CreateCriteria<SomeEntity>()
    .Add(Restrictions.InsensitiveLike("SomeProperty", "Some Value"))

#3

NHibernate is materializing the results set due to the AsQueryable() which causes the collection to be loaded and cast as IQueryable. See this question for similar behavior in Entity Framework.

由于AsQueryable()导致集合被加载并转换为IQueryable,因此NHibernate实现了结果集。有关Entity Framework中的类似行为,请参阅此问题。

I would re-write your query to use NHibernate's LINQ provider:

我会重新编写您的查询以使用NHibernate的LINQ提供程序:

var result = session.Query<SomeEntity>()
                    .Where(x => x.SomeProperty.ToLower().Contains("Xxx".ToLower)));

#4

I would go in QueryOver<T> in your place and add NHibernate.Criterion in your usings. Code would look like this:

我会在你的地方使用QueryOver 并在你的使用中添加NHibernate.Criterion。代码看起来像这样:

    using NHibernate.Criterion;
    //...
    var result = session.QueryOver<SomeEntity>()
        .Where(e => e.SomeProperty
            .IsLike("xyz", MatchMode.Anywhere)).List().ToList();

It will generate query as you want.

它会根据需要生成查询。

#1

#2