NHibernate unintentional lazy property loading

Question

I introduced a mapping for a business object which has (among others) a property called "Name":

public class Foo : BusinessObjectBase
{
    ...
    public virtual string Name { get; set; }
}

For some reason, when I fetch "Foo" objects, NHibernate seems to apply lazy property loading (for simple properties, not associations):

The following code piece generates n+1 SQL statements, whereof the first only fetches the ids, and the remaining n fetch the Name for each record:

ISession session = ...IQuery query = session.CreateQuery(queryString);
ITransaction tx = session.BeginTransaction();

List<Foo> result = new List<Foo>();
foreach (Foo foo in query.Enumerable())
{
    result.Add(foo);
}

tx.Commit();
session.Close();

produces:

select foo0_.FOO_ID as col_0_0_ from V1_FOO foo0_
SELECT foo0_.FOO_ID as FOO1_2_0_, foo0_.NAME as NAME2_0_ FROM V1_FOO foo0_ 
    WHERE foo0_.FOO_ID=:p0;:p0 = 81
SELECT foo0_.FOO_ID as FOO1_2_0_, foo0_.NAME as NAME2_0_ FROM V1_FOO foo0_ 
    WHERE foo0_.FOO_ID=:p0;:p0 = 36470
SELECT foo0_.FOO_ID as FOO1_2_0_, foo0_.NAME as NAME2_0_ FROM V1_FOO foo0_ 
    WHERE foo0_.FOO_ID=:p0;:p0 = 36473

Similarly, the following code leads to a LazyLoadingException after session is closed:

ISession session = ...
ITransaction tx = session.BeginTransaction();
Foo result = session.Load<Foo>(id);
tx.Commit();
session.Close();

Console.WriteLine(result.Name);

Following this post, "lazy properties ... is rarely an important feature to enable ... (and) in Hibernate 3, is disabled by default."

So what am I doing wrong? I managed to work around the LazyLoadingException by doing a NHibernateUtil.Initialize(foo) but the even worse part are the n+1 sql statements which bring my application to its knees.

This is how the mapping looks like:

<class name="Foo" table="V1_FOO">
    ...
    <property name="Name" column="NAME"/>
</class>

BTW: The abstract "BusinessObjectBase" base class encapsulates the ID property which serves as the internal identifier.

Answer 1

I don't think that this is due to lazy property loading. It's rather because of the use of Enumerable and Load.

Take a look at the reference documentation about Enumerable:

... The iterator will load objects on demand, using the identifiers returned by an initial SQL query (n+1 selects total).

Either use batch fetching to reduce the number of queries (in the mapping of the class)

<class name="Foo" table="V1_FOO" batch-size="20">

... or use List instead of Enumerable:

IQuery query = session.CreateQuery(queryString);
List<Foo> result query.List<Foo>();

Note: Enumerable only makes sense if you don't expect that you need the whole result, or in special cases where you don't want to have them all in memory at the same time (then you need Evict to remove them). For the most cases, List is what you need.

---

In the case of Load, only a proxy is created (no query is performed). On the first access to it, it is loaded. (This is very powerful for instance to use this proxy as filter arguments in queries or to link it to another entity without the need of loading its contents.) If you need its contents, use Get instead

using (ISession session = ...)
using (ITransaction tx = session.BeginTransaction())
{
    Foo result = session.Get<Foo>(id);

    tx.Commit();
}
// could still fail in case of lazy loaded references
Console.WriteLine(result.Name);

... or even better, use the entity only while the session is open.

using (ISession session = ...)
using (ITransaction tx = session.BeginTransaction())
{
    Foo result = session.Load<Foo>(id);
    // should always work fine
    Console.WriteLine(result.Name);

    tx.Commit();
}