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.

2.1.2.4000; hmm thats < 3. Guess lazy properties aren't even supported yet in this version? Anyway as Stefan pointed out the problem is not due to lazy property loading. — chiccodoro
– chiccodoro, Commented Jun 2, 2010 at 10:52
updated my answer with code samples and further suggestions. — Stefan Steinegger
– Stefan Steinegger, Commented Jun 2, 2010 at 11:48

Stefan Steinegger · Accepted Answer · 2010-06-02 11:48:02Z

6

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();
}

edited Jun 2, 2010 at 11:48

answered Jun 2, 2010 at 9:47

Stefan Steinegger

64.8k17 gold badges132 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

chiccodoro Over a year ago

Very accurate and helpful answer, thank you. Following your linked reference documentation, the solution to the first problem would be: IList<BO> result = query.List<BO>(), or if I need a List, List<BO> result = query.List<BO>().ToList<BO>(); - If that's right, you might want to include this code in your answer for the reference of future readers?

chiccodoro Over a year ago

I have now implemented the .List<>(), and it performs way better than it did before! (Of course, since communication load with database is O(1) instead of O(n) now)

Collectives™ on Stack Overflow

NHibernate unintentional lazy property loading

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related