NHIBERNATE - EAGER FETCH AND PAGINATION IN ONE QUERY

Let’s consider these mappings:

<class name="Item" table="Item">
	<id name="Id" column="Id" type="int">
		<generator class="native" />
	</id>
	<property name="Name" column="Name" type="string" />
	<property name="Sort" column="Sort" type="int" />
	<bag name="Tags" inverse="true" lazy="true">
		<key column="ItemId" />
		<one-to-many class="Tag"/>
	</bag>
</class>

<class name="Tag" table="Tag">
	<id name="Id" column="Id" type="int">
		<generator class="native" />
	</id>
	<property name="Name" column="Name" type="string" not-null="true" />
	<many-to-one name="Item" column="ItemId" class="Item" not-null="true" />
</class>

Trying to paginate a query that uses eager fetching to retrieve the data in the ‘traditional’ way, that is using something similar to this piece of code:

ICriteria myCriteria = session.CreateCriteria<Item>("itm");
myCriteria.AddOrder(new Order("Sort", true))
	.SetFirstResult((pageIndex - 1)*pageSize)
	.SetMaxResults(pageSize)
	.SetResultTransformer(Transformers.DistinctRootEntity)
	.SetFetchMode("Tags", FetchMode.Eager);

Will simply FAIL, because eager fetching retrieves more data rows from the database due to the joins it performs, thus the ‘SetFirstResult()’ call will simply cut out some of your data.

One way to resolve the problem is to use a DetachedCriteria to actually get a projection of all the Ids of the items you want to paginate (applying any filtering and sorting you desire at this step) and later on use the projection to perform the real query that retrieves the data using eager fetching:

private static IList<Item> GetPagedData(int pageIndex, int pageSize)
{
	IList<Item> result;
	using (ISession session = NHelper.OpenSession())
	using (ITransaction tx = session.BeginTransaction())
	{
		// get the list of IDs corresponding to the page od data we wanna get
		DetachedCriteria detached = DetachedCriteria.For<Item>("itm2");
		detached.AddOrder(new Order("Sort", true))
			.SetFirstResult((pageIndex - 1)*pageSize)
			.SetMaxResults(pageSize)
			.SetProjection(Projections.Property("Id"));
		// get the real data
		ICriteria myCriteria = session.CreateCriteria<Item>("itm");

		myCriteria
			.Add(Subqueries.PropertyIn("Id", detached))
			.SetResultTransformer(Transformers.DistinctRootEntity)
			.SetFetchMode("Tags", FetchMode.Eager);
			
		result = myCriteria.List<Item>();

		tx.Commit();
	}
	return result;
}

If you look at the logs generated by NHibernate you will see that this code will result in a single query sent to your database engine, having called this function asking for page index 2 with a page size of 2 I obtain the following query:

SELECT this_.Id as Id1_1_, this_.Name as Name1_1_, this_.Sort as Sort1_1_, tags2_.ItemId as ItemId3_, tags2_.Id as Id3_, tags2_.Id as Id0_0_, tags2_.Name as Name0_0_, tags2_.ItemId as ItemId0_0_ FROM dbo.Item this_ left outer join dbo.Tag tags2_ on this_.Id=tags2_.ItemId WHERE this_.Id in (SELECT TOP 2 y0_ FROM (SELECT this_0_.Id as y0_, ROW_NUMBER() OVER(ORDER BY this_0_.Sort) as __hibernate_sort_row FROM dbo.Item this_0_) as query WHERE query.__hibernate_sort_row > 2 ORDER BY query.__hibernate_sort_row)

This technique helped me to reduce the number of queries made by the application a lot.

PrimordialCode

NHibernate - Eager Fetch and Pagination in one query

Related Content

Alessandro Giorgetti