When does NHibernate execute my query?

Question

I am trying to write a generic repository for my NHibernate data access. The Get<T>() method should be able to take an optional predicate, that should be included in the query - that is, NHibernate should generate the WHERE clause in the SQL.

    public virtual IList<T> Get(Func<T, bool> predicate = null)
    {
        // Open NHibernate Session
        using (var session = NHibernateHelper.OpenSession())
            return (predicate != null
                       ? session.Query<T>().Where(predicate)
                       : session.Query<T>()).ToList();

    }

When I pass in a predicate, and observe the SQL statement NH generates, I see no where clause.

When does NHibernate execute the query? Right when calling .Query<T>()? If so, how can I achieve this?

Jehof · Accepted Answer · 2013-03-22T09:28:28.630

The query should be executed by the call ToList().

The case why the WHERE clause is not included in your sql statement is that you need to pass an Expression<Func<T,bool>> to your method.

public virtual IList<T> Get(Expression<Func<T, bool>> predicate = null)
    {
        // Open NHibernate Session
        using (var session = NHibernateHelper.OpenSession())
            return (predicate != null
                       ? session.Query<T>().Where(predicate)
                       : session.Query<T>()).ToList();

    }

The extension method Where(Func<T,bool>>) is defined on Enumerable, so that the the query loads all data and then applies the WHERE-filter in memory.

The extension method Where(Expression<Func<T,bool>>) is defined on Queryable, so that the query provider (NHibernate) can build a sql statement including your WHERE condition that gets executed on the data source.

score 1 · Answer 2 · answered Mar 22 '13 at 09:21

Since @Jehof gave you correct explanation I just want to add separate note - you should not return IList<T> from you repository method as then any other linq operation will be executed in memory and not in the database. Assume following calls

var data = repository.Get<Company>(c=>c.Name.StartsWith("MyCompany"));
... some other operations / method calls etc.
var companySubset = data.Where(...);

so now if you have IList<T> Get<T>() you decrease performance but with IQueryable<T> Get<T> you would still have the second Where() appended to the database query.

Of course not all linq operations are supported by IQueryable like (join , last ) and this is the only place to call ToList() extension to evaluate expression.

That is the point, but thanks for your note :) - I dont want my business logic to trigger DB calls when I dont explicitly ask for it. — Jeff, Mar 22 '13 at 09:41

When does NHibernate execute my query?

2 Answers2