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.

                             

6 Comments

  • wow that is really nice. We have had problems in the past with this due to large aggregates. Will check out your code!

  • How about if you want to filter by nested property? For example: Details has another collection "Quantities" and you want Details.Quantities.Quantity > 1? How would that look in filter?

  • Mr: in recent versions of NH you can use the extension method AsQueryable and perform LINQ queries over uninitialized collections.

  • Thank you for your reply RicardoPeres. But I need generic solution. I don't know which collection user wants to filter so I need to create filter on the fly. I'm assuming this is not possible via LINQ and I need HQL language when creating filter. So how would I achieve this via HQL(if possible)?

    For example:

    var filteredCollection = session.CreateFilter(collection, "WHERE " + parsedSqlClause).List<T>();

    collection represents Quantities (ICollection<Quantity>) of Details object.

    parsedSqlClause represents sql query which can be different every time (based on user parameters). For example let's say Quantity has property ICollection<QuantityDetail> QuantityDetails. How would sql look if user want's to filter by this "subcollection"?

    So far we tried:
    - WHERE QuantityDetails.Length > 100
    - tried to create alias "qd" which represents Quantity.QunatityDetials and then: WHERE qd.Length > 100


  • testing comment

  • Hi, Mr!
    It may work with some complex SQL query. If you can inspect the generated queries *without* this and then try to come up with this query.

Add a Comment

As it will appear on the website

Not displayed

Your website