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(); 3: 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>();
6: 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.