NHibernate Pitfalls: Querying a Collection Count

This is part of a series of posts about NHibernate Pitfalls. See the entire collection here.

Beware when querying a one-to-many collection (bag, set, map, list, etc) with the LINQ standard operators Any() or Count() (or even with the collection’s own Count property: if the collection is mapped as lazy = true, it will be loaded entirely from the DB (if it is not already loaded). That is because the Any() and Count() methods are from LINQ to Objects, not LINQ to NHibernate:

   1: Int32 ordersByProduct = product.OrderDetails.Count();    //causes loading of all order details

There are two possible solutions:

  • Map the collection with lazy = extra and use the collection's Count property; 
   1: mapper.Class<Product>(ca =>
   2: {
   3:     //...
   4:     ca.Set(x => x.OrderDetails, x =>
   5:     {
   6:         x.Key(c => c.Column("`PRODUCTID`"));
   7:         x.Inverse(true);
   8:         x.Lazy(CollectionLazy.Extra);
   9:         //...
  10:     }, c => c.OneToMany());
  11: }
  • Perform an independent query (not on the collection) to find if the association has any elements: 
   1: var existOrderForProduct = session.Query<OrderDetail>().Where(x => x.Product.Id == 100).Any();

                             

4 Comments

  • This is not true. Members is a collection in group object. Watch this:

    select cast(count(*) as INT) as col_0_0_
    from members groupmembe0_
    inner join entity groupdto1_
    on groupmembe0_.entity_id = groupdto1_.entity_id
    left outer join users userdto2_
    on groupmembe0_.user_id = userdto2_.user_id
    left outer join user_options userdto2_1_
    on userdto2_.user_id = userdto2_1_.user_id
    where groupdto1_.entity_id = 'df5c9bf7-664c-42f3-b868-cd80dabaead6' /* @p0 */

  • @mynkow:
    What is that?
    What I mean is:
    int count = order.Details.Count();
    This causes the whole Details collection to be loaded, and it is a known problem.

  • I usually do a subquery for count with "Formula", well the cons of cause the sub-query is always execute along the main query.

  • Hi Ricardo,

    the sql I get is from:

    int count = user.Members.Count();

    The specific example is with many-to-many but it doesn't matter.

Comments have been disabled for this content.