Lesser-Known NHibernate Features: Filtering an Unloaded Collection

Suppose you have an entity with a collection of other entities (one to many, many to many); this collection is normally represented by some property implementing IEnumerable<T>, or one some more specific interface.

If we want to query this collection, we normally have to load all of its items. For indexed collections, it is possible to use extra-lazy laziness, which means NHibernate will only load one item at a time, instead of all at once, but this might even be worse, because it would result in multiple SELECT statements.

NHibernate offers a nice mechanism to filter and order a collection without actually loading all of its items; it’s the CreateFilter method of the ISession, and we use it like this:

   1: //get an order

   2: var order = session.Query<Order>().First();


   4: //all order details of this order where quantity is greater than one

   5: var detailsWithMoreThanOneItem = session.CreateFilter(order.Details, "WHERE Quantity > 1").List<OrderDetail>();


   7: //all order details of this order sorted by their quantity in descending order

   8: var detailsSortedByQuantityInDescendingOrder = session.CreateFilter(o.Details, "ORDER BY Quantity DESC").List<OrderDetail>();

I even once wrote a helper method for querying collections with LINQ without actually loading them; its usage is:

   1: //all order details of this order where quantity is greater than one

   2: var detailsWithMoreThanOneItem = order.Details.Query().Where(d => d.Quantity > 1).ToList();

The Query extension method will check to see if the collection has already been loaded, in which case, it resorts to LINQ to Objects, otherwise, it generates a proper LINQ query. You can find the source code here.

Published by

Ricardo Peres

Tech Lead at RedLight Software.

Leave a Reply

Your email address will not be published. Required fields are marked *